• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index Match Match using VBA

L

leonardtan

Member
Dear Excel Experts,

i have a set of data where the rows and columns is not fixed
i am currently using index match match to find the data for net covers, food(1), Bev Alcoholic(2), Bev NonAlcoholic(3) and so on..

The pain comes when i need to extract new set of data and have to repeat the match and index formula again for 30 to 31 days
i have been trying to use VBA and offset to automate the repeat process, but am unable to do so

is there an easier way for the ease of updating so that the data can be just pasted and the formula will automate the respective columns in the data tab?
66680

66681
 

Attachments

  • abc.xlsx
    99.8 KB · Views: 8
"The pain comes when i need to extract new set of data and have to repeat the match and index formula again for 30 to 31 days"
It means that data in new sheets is at the same place but you need to change the sheet's name in the formula , is it ?
Please, give more details about the issue.
 
It means that data in new sheets is at the same place but you need to change the sheet's name in the formula , is it ?
Yes but the rows are different and i cant use this method


The new data will be required to be pasted into the sheets named "1", "2","3" to 31 depending on the number of days in a month if it's march then it will be 31 days.

Scenario
1. The data is extracted from a system and will be converted to .csv format, will the paste on the template depending on the day of the month
2. Will need to extract the data from the individual worksheet tabs according to the day of the month
3. For instance - Referring to worksheet "1" will need the net covers for all the outlets from column AX13:AX51 to match the covers with the outlet from column A13:A51, But when the data is pasted to worksheet "2" the rows changes to column A13:A47 to match with the covers AX13:AX47. Formula need to be re-keyed again
4. Will need to match outlets net revenue data, where the rows are different, but starting column is static. for worksheet "1" will be index from C118:
5. Will also require to match service charge under column V row 172 onwards for worksheet "1"

I wish to replace the worksheet name with 2, 3, 4 and so on but this is not possible as the rows of the data changes

Re-attached data for better understanding, please help with VBA if possible, otherwise a dynamic formula will be good. Data pasted changes in rows where index and match formula needs to be re-keyed on a daily basis
 

Attachments

  • abc.xlsx
    108.4 KB · Views: 3
Please confirm the understanding
In sheet "Data" there is formulas making reference to some sheets named : 1 to 31
Formulas are in columns:
Net Covers, Food (1),Bev Alcoholic (2),Bev NonAlcoholic (3),Tobacco (7),Miscellaneous (8),Service Charge

The name of the sheet is coming from the day number in cell F4

The formula is doing reference to the sheet concerned (1 to 31) using the value in header row ( 6) and in first column (A)
These references are used to search where is the data in the sheet concerned (1 to 31)
These references are used sometimes for row reference, sometimes for column reference.
 
Please confirm the understanding
In sheet "Data" there is formulas making reference to some sheets named : 1 to 31
Formulas are in columns:
Net Covers, Food (1),Bev Alcoholic (2),Bev NonAlcoholic (3),Tobacco (7),Miscellaneous (8),Service Charge

reply: yes

The name of the sheet is coming from the day number in cell F4

reply: no it is manual entry but refers to the data from day of month

The formula is doing reference to the sheet concerned (1 to 31) using the value in header row ( 6) and in first column (A)
These references are used to search where is the data in the sheet concerned (1 to 31)

reply: yes

These references are used sometimes for row reference, sometimes for column reference.

reply: yes, if it is column will be column, and if row it will be row it will not change
 
Today in sheet "Data" there is values from 2 sheets : 1 & 2
How many sheets do you want to display : 2 like today or the full set from 1 to 31 ?
The split between sheet's display is "Total" in column "A" ?
 
See here what could be done
In sheet "Data" cell F2 enter the sheet name to use
Is only done one sheet calculation
Test and comment

Code: 
Option Explicit
Option Base 1

Sub PrepaFormulas()
' PrepaFormulas Macro
Const Hd1 As String = "Net Covers"
Const Hd2 As String = "Food (1)"
Dim WsN As String
Dim WkWs As Worksheet
Dim Form1 As String, Form2  As String, Form3  As String
Dim FC  As Integer, LC  As Integer, FR As Integer, LR  As Integer
Dim WkAdd As String, WkRg As Range
Dim RgMx   As Variant
Dim F, T
Dim AAA, BBB, CCC
    T = Array("#", "µ1", "µ2", "µ3")
    ReDim RgMx(1 To UBound(T, 1), 1 To 1)
    Dim I  As Integer
    For I = 1 To 4
        RgMx(I, 1) = T(I)
    Next I
    ReDim Preserve RgMx(1 To UBound(RgMx, 1), 1 To 2)
    
Dim RgAdd1 As String, RgAdd2 As String, RgAdd3 As String
    Form1 = "=IFERROR(INDEX('#'!µ1,MATCH(F7,'#'!µ2,0),MATCH($K$6,'#'!µ3,0)),0)"
    Form2 = "=IFERROR(INDEX('#'!µ1,MATCH(M$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
    Form3 = "=IFERROR(INDEX('#'!µ1,MATCH(Q$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
    
    WsN = Range("F2")
    If (Not (Evaluate("isref('" & WsN & "'!A1)"))) Then MsgBox (" Sheet : " & WsN & vbCrLf & " DO NOT exist "): Exit Sub
    Set WkWs = Sheets(WsN)
    With WkWs
'--------------------------
        Set F = .Cells.Find(Hd1, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 1
            FR = F.Row
            LC = .Cells(FR, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            RgMx(2, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, LC)).Address
            RgMx(3, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, FC)).Address
            RgMx(4, 2) = Range(.Cells(FR, FC), .Cells(FR, LC)).Address

            For I = 1 To UBound(RgMx, 1)
                Form1 = Replace(Form1, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "K"), Cells(Rows.Count, "K").End(3))
            WkRg.Formula = Form1
        End If
'--------------------------
        Set F = .Cells.Find(Hd2, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 3
            FR = F.Row
            LC = .Cells(FR + 1, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            
            Set WkRg = Range(.Cells(FR, FC), .Cells(LR, LC))
            WkAdd = WkRg.Address
            RgMx(2, 2) = Range(.Cells(FR, FC), .Cells(LR, LC)).Address
            
            
            Set WkRg = Range(.Cells(FR, FC), .Cells(LR, FC))
            WkAdd = WkRg.Address
            RgMx(3, 2) = Range(.Cells(FR, FC), .Cells(LR, FC)).Address
            
            Set WkRg = Range(.Cells(FR - 1, FC), .Cells(FR - 1, LC))
            WkAdd = WkRg.Address
            RgMx(4, 2) = Range(.Cells(FR - 1, FC), .Cells(FR - 1, LC)).Address
            
            For I = 1 To UBound(RgMx, 1)
                Form2 = Replace(Form2, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "M"), Cells(Rows.Count, "M").End(3)).Resize(, 3)
            WkRg.Formula = Form2
            For I = 1 To UBound(RgMx, 1)
                Form3 = Replace(Form3, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "Q"), Cells(Rows.Count, "Q").End(3)).Resize(, 3)
            WkRg.Formula = Form3
        End If
    
    End With
    
    MsgBox ("Job Done")
'
End Sub
 

Attachments

  • abc5.xlsm
    141.2 KB · Views: 10
Today in sheet "Data" there is values from 2 sheets : 1 & 2

Reply: There will be value for 1 to 31 days
How many sheets do you want to display : 2 like today or the full set from 1 to 31 ?
The split between sheet's display is "Total" in column "A" ?
 
PCI said:
See here what could be done
In sheet "Data" cell F2 enter the sheet name to use
Is only done one sheet calculation
Test and comment

Code: 
Option Explicit
Option Base 1

Sub PrepaFormulas()
' PrepaFormulas Macro
Const Hd1 As String = "Net Covers"
Const Hd2 As String = "Food (1)"
Dim WsN As String
Dim WkWs As Worksheet
Dim Form1 As String, Form2  As String, Form3  As String
Dim FC  As Integer, LC  As Integer, FR As Integer, LR  As Integer
Dim WkAdd As String, WkRg As Range
Dim RgMx   As Variant
Dim F, T
Dim AAA, BBB, CCC
    T = Array("#", "µ1", "µ2", "µ3")
    ReDim RgMx(1 To UBound(T, 1), 1 To 1)
    Dim I  As Integer
    For I = 1 To 4
        RgMx(I, 1) = T(I)
    Next I
    ReDim Preserve RgMx(1 To UBound(RgMx, 1), 1 To 2)
  
Dim RgAdd1 As String, RgAdd2 As String, RgAdd3 As String
    Form1 = "=IFERROR(INDEX('#'!µ1,MATCH(F7,'#'!µ2,0),MATCH($K$6,'#'!µ3,0)),0)"
    Form2 = "=IFERROR(INDEX('#'!µ1,MATCH(M$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
    Form3 = "=IFERROR(INDEX('#'!µ1,MATCH(Q$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
  
    WsN = Range("F2")
    If (Not (Evaluate("isref('" & WsN & "'!A1)"))) Then MsgBox (" Sheet : " & WsN & vbCrLf & " DO NOT exist "): Exit Sub
    Set WkWs = Sheets(WsN)
    With WkWs
'--------------------------
        Set F = .Cells.Find(Hd1, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 1
            FR = F.Row
            LC = .Cells(FR, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            RgMx(2, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, LC)).Address
            RgMx(3, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, FC)).Address
            RgMx(4, 2) = Range(.Cells(FR, FC), .Cells(FR, LC)).Address

            For I = 1 To UBound(RgMx, 1)
                Form1 = Replace(Form1, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "K"), Cells(Rows.Count, "K").End(3))
            WkRg.Formula = Form1
        End If
'--------------------------
        Set F = .Cells.Find(Hd2, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 3
            FR = F.Row
            LC = .Cells(FR + 1, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
          
            Set WkRg = Range(.Cells(FR, FC), .Cells(LR, LC))
            WkAdd = WkRg.Address
            RgMx(2, 2) = Range(.Cells(FR, FC), .Cells(LR, LC)).Address
          
          
            Set WkRg = Range(.Cells(FR, FC), .Cells(LR, FC))
            WkAdd = WkRg.Address
            RgMx(3, 2) = Range(.Cells(FR, FC), .Cells(LR, FC)).Address
          
            Set WkRg = Range(.Cells(FR - 1, FC), .Cells(FR - 1, LC))
            WkAdd = WkRg.Address
            RgMx(4, 2) = Range(.Cells(FR - 1, FC), .Cells(FR - 1, LC)).Address
          
            For I = 1 To UBound(RgMx, 1)
                Form2 = Replace(Form2, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "M"), Cells(Rows.Count, "M").End(3)).Resize(, 3)
            WkRg.Formula = Form2
            For I = 1 To UBound(RgMx, 1)
                Form3 = Replace(Form3, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "Q"), Cells(Rows.Count, "Q").End(3)).Resize(, 3)
            WkRg.Formula = Form3
        End If
  
    End With
  
    MsgBox ("Job Done")
'
End Sub
Click to expand...

Hi PCI,

The macro changes the header rows, value unable to match. Did i do anything wrong
66814
 
"The macro changes the header rows, value unable to match. Did i do anything wrong"
Do not delete anything in sheet "Data"
 
ok. then the data is fine
need help with the service charge

rest of the sheets from 1 to 31 when new data is being pasted how will the macro treat them to the data tab
 
"need help with the service charge"
Yes there was a bug: There is 4 set of formulas to work on

"rest of the sheets from 1 to 31 when new data is being pasted how will the macro treat them to the data tab"
The template is, in sheet "Data" from row 6 to 48, so if you want more than 1 day we need to duplicate this template and update it with the right day.
How do yoiu select the day to use:
Is it a selection somewhere : Cell F2
Is it the full set ( 1 to 31 )

Code: 
}
Option Explicit
Option Base 1

Sub PrepaFormulas()
' PrepaFormulas Macro
Const Hd1 As String = "Net Covers"
Const Hd2 As String = "Food (1)"
Const Hd3 As String = "Service Charge"
Dim WsN As String
Dim WkWs As Worksheet
Dim Form1 As String, Form2  As String, Form3  As String, Form4  As String
Dim FC  As Integer, LC  As Integer, FR As Integer, LR  As Integer
Dim WkAdd As String, WkRg As Range
Dim RgMx   As Variant
Dim F, T
Dim AAA, BBB, CCC
    T = Array("#", "µ1", "µ2", "µ3")
    ReDim RgMx(1 To UBound(T, 1), 1 To 1)
    Dim I  As Integer
    For I = 1 To 4
        RgMx(I, 1) = T(I)
    Next I
    ReDim Preserve RgMx(1 To UBound(RgMx, 1), 1 To 2)
    
Dim RgAdd1 As String, RgAdd2 As String, RgAdd3 As String
    Form1 = "=IFERROR(INDEX('#'!µ1,MATCH($F7,'#'!µ2,0),MATCH($K$6,'#'!µ3,0)),0)"
    Form2 = "=IFERROR(INDEX('#'!µ1,MATCH(M$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
    Form3 = "=IFERROR(INDEX('#'!µ1,MATCH(Q$6,'#'!µ2,0),MATCH($F7,'#'!µ3,0)),0)"
    Form4 = "=IFERROR(INDEX('#'!µ1,MATCH($F7,'#'!µ2,0),MATCH($S$6,'#'!µ3,0)),0)"
    WsN = Range("F2")
    If (Not (Evaluate("isref('" & WsN & "'!A1)"))) Then MsgBox (" Sheet : " & WsN & vbCrLf & " DO NOT exist "): Exit Sub
    Set WkWs = Sheets(WsN)
    With WkWs
'--------------------------
        Set F = .Cells.Find(Hd1, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 1
            FR = F.Row
            LC = .Cells(FR, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            RgMx(2, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, LC)).Address
            RgMx(3, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, FC)).Address
            RgMx(4, 2) = Range(.Cells(FR, FC), .Cells(FR, LC)).Address

            For I = 1 To UBound(RgMx, 1)
                Form1 = Replace(Form1, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "K"), Cells(Rows.Count, "K").End(3))
            WkRg.Formula = Form1
        End If
'--------------------------
        Set F = .Cells.Find(Hd2, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 3
            FR = F.Row
            LC = .Cells(FR + 1, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            RgMx(2, 2) = Range(.Cells(FR, FC), .Cells(LR, LC)).Address
            RgMx(3, 2) = Range(.Cells(FR, FC), .Cells(LR, FC)).Address
            RgMx(4, 2) = Range(.Cells(FR - 1, FC), .Cells(FR - 1, LC)).Address
            
            For I = 1 To UBound(RgMx, 1)
                Form2 = Replace(Form2, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "M"), Cells(Rows.Count, "M").End(3)).Resize(, 3)
            WkRg.Formula = Form2
            For I = 1 To UBound(RgMx, 1)
                Form3 = Replace(Form3, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "Q"), Cells(Rows.Count, "Q").End(3)).Resize(, 2)
            WkRg.Formula = Form3
        End If
'--------------------------
'=IFERROR(INDEX('1'!$A$173:$AQ$212,MATCH($F7,'1'!$A$173:$A$212,0),MATCH($S$6,'1'!$A$172:$AQ$172,0)),0)
        Set F = .Cells.Find(Hd3, LookIn:=xlValues, Lookat:=xlWhole)
        If Not (F Is Nothing) Then
            FC = 1
            FR = F.Row
            LC = .Cells(FR + 1, Columns.Count).End(xlToLeft).Column
            LR = .Cells(FR + 1, FC).End(xlDown).Row
            RgMx(1, 2) = WsN
            RgMx(2, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, LC)).Address
            RgMx(3, 2) = Range(.Cells(FR + 1, FC), .Cells(LR, FC)).Address
            RgMx(4, 2) = Range(.Cells(FR, FC), .Cells(FR, LC)).Address
            
            For I = 1 To UBound(RgMx, 1)
                Form4 = Replace(Form4, RgMx(I, 1), RgMx(I, 2))
            Next I
            Set WkRg = Range(Cells(7, "S"), Cells(Rows.Count, "S").End(3))
            WkRg.Formula = Form4
        End If
    End With
    
    MsgBox ("Job Done")
'
End Sub
}
 

Attachments

  • abc6.xlsm
    142.3 KB · Views: 12
"need help with the service charge"
Yes there was a bug: There is 4 set of formulas to work on

"rest of the sheets from 1 to 31 when new data is being pasted how will the macro treat them to the data tab"
The template is, in sheet "Data" from row 6 to 48, so if you want more than 1 day we need to duplicate this template and update it with the right day.
How do yoiu select the day to use:
Is it a selection somewhere : Cell F2
Reply: Yes i use F2 to change from 1 to 2, maybe i make a drop down list to select until 31
66830
Is it the full set ( 1 to 31 ) Dont understand what this means
 
"would like to check if this can be possible using formula as well instead of VBA"
Today, still exist formulas in sheet "Data", the macro is updating only ranges.
Not sure it is possible to do it with formulas as the values are quite everywhere in sheets 1,2,...
 
You must log in or register to reply here.
Back
Top