Macro for INDEX and MATCH

[Remphan]

Here is example file. Help pls with VBA code against formula. Because file may include more than 250K rows for sheet1 and the same for sheet2. So, formula works very slowly.

Also, very often the case that I need to compare the filtered bands and stretch the formula in the filtered range. Stretching the formula in the filtered band is very difficult, if he has a lot of rows. Therefore, if the table that we are looking for and from which the copy has filters - and it also needs to look only in the filtered range.

Often it is necessary to compare the way different ranges, would be very happy if the macro will be able to specify the range of the pop-up window. I would appreciate it if someone decides to help.

Cross

 

[Remphan]

Help, please ray:

 

[RickXL]

Hi,

I'd like to but I cannot see your file. Perhaps if you posted the formula I could take a look.

 

[Remphan]

Hi,

I'd like to but I cannot see your file. Perhaps if you posted the formula I could take a look.

>>File (example)<<

1st sheet and table1 - may have a filter on columns. This is the table for which I needs to pull values from the second table.
2nd sheet - Originally located in another book, but for convenience I put it as a sheet in the first book with table.

Total, there are a couple of sheets in Workbook. For the first table, we need to pull out values from second table. The tables are different (only in example them are the same), but 1 an 1 table have completely or partially shared column in which a comparison is possible.

At present, a few problems:
1. Formula for a long time working on large data (250k+ rows)
2. The table of the first sheet may be set filter. And needs to compare visible values on the filter with the second sheet (which may also be a filter). It is necessary to compare both the filtered and unfiltered ranges only visible values);
3. 3. In large data uncomfortable stretching formula in the filtered range.

Sometimes I need only values after formula (copy/paste as unformatted values) and then remove sheet2, but sometimes it is required and automatic recalculation. That is sometimes the second table on the second sheet and leave to automatically recalculate.

 

[Remphan]

H3:H69 formula and J3:M69 *

 

[RickXL]

Sorry, but I do not have access to http://www.excelforum.com only MrExcel.com | Excel Resources | Excel Solutions.

I am not understanding the whole problem yet but I do have a VBA macro that will perform VLOOKUPs by using VBA Arrays and Dictionaries. It will look at key field in one column in a worksheet then copy on the corresponding columns from another worksheet. So it is like a VLOOKUP but it copies the data once so no recalculation is needed.

Sub VLookUp3()
    Dim arrT    As Variant      ' Table for Look-Up
    Dim arrI    As Variant      ' Input Column
    Dim arrO    As Variant      ' Output Array
    Dim ws1     As Worksheet    ' Data
    Dim ws2     As Worksheet    ' Look-Up Table
    Dim i       As Long
    Dim j       As Long
    Dim dic     As Dictionary
    Dim Key     As String
    
    Set dic = New Dictionary
   
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set dic = CreateObject("Scripting.Dictionary")
        
    With ws2        ' Read in the Look Up Table
        arrT = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For i = 1 To UBound(arrT, 1): dic(arrT(i, 1)) = i: Next
    End With
    
    With ws1        ' Read in the Input Column
        arrI = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    ' Perform the Look Up
    ReDim arrO(1 To UBound(arrI, 1), 1 To UBound(arrT, 2) - 1)
    For i = 1 To UBound(arrO, 1)
        For j = 1 To UBound(arrO, 2)
            If dic.Exists(arrI(i, 1)) Then
                arrO(i, j) = arrT(dic(arrI(i, 1)), j + 1)
            Else
                arrO(i, j) = CVErr(xlErrNA)
            End If
        Next
    Next

    ' Write the results
    ws1.Range("D2").Resize(UBound(arrO, 1), UBound(arrO, 2)) = arrO

End Sub

The macro uses column A in Sheet1 as the value to be used for the look-up.
It matches that with column A in Sheet2 and copies in columns B and C from Sheet2 into column D of Sheet1.

 

[RickXL]

I think this is a better version of the code. The test data is at the end.

Sub VLookUp()
    Dim arrT    As Variant      ' Table for Look-Up
    Dim arrI    As Variant      ' Input Column
    Dim arrO    As Variant      ' Output Array
    Dim ws1     As Worksheet    ' Data
    Dim ws2     As Worksheet    ' Look-Up Table
    Dim i       As Long
    Dim j       As Long
    Dim dic     As Object
    Dim Key     As String
   
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set dic = CreateObject("Scripting.Dictionary")
        
    With ws2        ' Read in the Look Up Table
        arrT = .Range("A2:D" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For i = 1 To UBound(arrT, 1): dic(arrT(i, 1)) = i: Next
    End With
    
    With ws1        ' Read in the Input Column
        arrI = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    ' Perform the Look Up
    ReDim arrO(1 To UBound(arrI, 1), 1 To UBound(arrT, 2) - 1)
    For i = 1 To UBound(arrO, 1)
        For j = 1 To UBound(arrO, 2)
            If dic.Exists(arrI(i, 1)) Then
                arrO(i, j) = arrT(dic(arrI(i, 1)), j + 1)
            Else
                arrO(i, j) = CVErr(xlErrNA)
            End If
        Next
    Next

    ' Write the results
    ws1.Range("D2").Resize(UBound(arrO, 1), UBound(arrT, 2) - 1) = arrO

End Sub

Excel 2013
	A	B	C	D	E	F
1	N	Letter	Name	Lkup#1	Lkup#2	Lkup#3
2	3	C	Charlie	C	Charlie	xx3
3	13	M	Mike	M	Mike	xx13
4	24	X	X-ray	X	X-ray	xx24
5	1	A	Alpha	A	Alpha	xx1
6	10	J	Juliet	J	Juliet	xx10
7	2	B	Bravo	B	Bravo	xx2
8	10	J	Juliet	J	Juliet	xx10
9	2	B	Bravo	B	Bravo	xx2
10	555	#N/A	#N/A	#N/A	#N/A	#N/A
11	26	Z	Zulu	Z	Zulu	xx26
12	23	W	Whiskey	W	Whiskey	xx23
13	23	W	Whiskey	W	Whiskey	xx23
14	8	H	Hotel	H	Hotel	xx8
15	25	Y	Yankee	Y	Yankee	xx25
16	444	#N/A	#N/A	#N/A	#N/A	#N/A
17	9	I	India	I	India	xx9
18	25	Y	Yankee	Y	Yankee	xx25
19	17	Q	Quebec	Q	Quebec	xx17
20	26	Z	Zulu	Z	Zulu	xx26
Sheet1

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,Sheet2!$A$1:$C$27,2,FALSE)
C2	=VLOOKUP($A2,Sheet2!$A$1:$C$27,3,FALSE)

Excel 2013
	A	B	C	D
1	Number	Letter	phonetic letter	xx
2	1	A	Alpha	xx1
3	2	B	Bravo	xx2
4	3	C	Charlie	xx3
5	4	D	Delta	xx4
6	5	E	Echo	xx5
7	6	F	Foxtrot	xx6
8	7	G	Golf	xx7
9	8	H	Hotel	xx8
10	9	I	India	xx9
11	10	J	Juliet	xx10
12	11	K	Kilo	xx11
13	12	L	Lima	xx12
14	13	M	Mike	xx13
15	14	N	November	xx14
16	15	O	Oscar	xx15
17	16	P	Papa	xx16
18	17	Q	Quebec	xx17
19	18	R	Romeo	xx18
20	19	S	Sierra	xx19
21	20	T	Tango	xx20
22	21	U	Uniform	xx21
23	22	V	Victor	xx22
24	23	W	Whiskey	xx23
25	24	X	X-ray	xx24
26	25	Y	Yankee	xx25
27	26	Z	Zulu	xx26
Sheet2

 

[Remphan]

I think this is a better version of the code. The test data is at the end.

This is almost exactly what I need (macro). Only a small request, if there is little opportunity to expand its functionality.

In my case:

for single column C

C2=IFERROR(INDEX(Sheet2!$B$2:$B$27,MATCH(A2,Sheet2!$A$2:$A$27,0)),"-")

and for a range (can stretch formula till F and next columns)

D2=IFERROR(INDEX(Sheet2!B$2:B$27,MATCH(A2,Sheet2!$A2:$A$27,0)),"-")

Several problems. Table 1 on the sheet may have a filter on the sheet and the table 2 may also have a filter. It is necessary to compare only the visible (filtered) values and paste only visible columns (what I need to pull up from sheet2 to sheet1). If the filter is not - then compare all visible.

The second trouble is that often need to compare many different tables, then there will need to be changed every time the macro code. It would be cool if macro could show the request that compare with what (universal for any tables) And how we paste (like C2 or D2 example formula way).

It is a pity that it's difficult yet for me to write such things, can only complicated formulas, macros little harder. So If you're a little extend the functionality of the macro, it will be very cool).

 

[Remphan]

I think this is a better version of the code. The test data is at the end.

I would be very grateful if you spend a little of your time for the universalization of the macro.

 

[RickXL]

I am hesitating because as a programmer I can see that it is quite general already. Another problem is that what you may be asking for will be several times the effort for me and another is that I still don't know exactly how you intend to use it. Use of "filters" should not affect the look-up. They are applied afterwards?

For example:

C2=IFERROR(INDEX(Sheet2!$B$2:$B$27,MATCH(A2,Sheet2!$A$2:$A$27,0)),"-")

becomes:

Sub VLookUp()
    Dim arrT    As Variant      ' Table for Look-Up
    Dim arrI    As Variant      ' Input Column
    Dim arrO    As Variant      ' Output Array
    Dim ws1     As Worksheet    ' Data
    Dim ws2     As Worksheet    ' Look-Up Table
    Dim i       As Long
    Dim j       As Long
    Dim dic     As Object
    Dim Key     As String
   
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    Set dic = CreateObject("Scripting.Dictionary")
        
    With ws2        ' Read in the Look Up Table
        arrT = .Range("A2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For i = 1 To UBound(arrT, 1): dic(arrT(i, 1)) = i: Next
    End With
    
    With ws1        ' Read in the Input Column
        arrI = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With
    
    ' Perform the Look Up
    ReDim arrO(1 To UBound(arrI, 1), 1 To UBound(arrT, 2) - 1)
    For i = 1 To UBound(arrO, 1)
        For j = 1 To UBound(arrO, 2)
            If dic.Exists(arrI(i, 1)) Then
                arrO(i, j) = arrT(dic(arrI(i, 1)), j + 1)
            Else
                arrO(i, j) = "-"
            End If
        Next
    Next

    ' Write the results
    ws1.Range("C2").Resize(UBound(arrO, 1), UBound(arrO, 2)) = arrO

End Sub

I could make the red parts variables which you could define altogether at the start of the code;
Or I could make the macro callable from another macro that would supply the same data;
Or It could read the data from a worksheet.

If you need to have several different copies and you want to know which one is which then you can add comments.