+ Reply to Thread
Results 1 to 8 of 8

VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple columns.

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple columns.

    Hi
    I have spent now hours of trying to google this solution over the internet with no success. Currently I'm using macro code that writes formulas in the cells and generates results from the formulas themselves. Basic formula looks something like this:
    =INDEX(zflex!AA:AA,MATCH(KHL_YM26!A:A,zflex!Z:Z,0))

    Sheet KHL_YM26 being sheet A
    Sheet zflex being sheet B

    Up till now I been using macro codes like below
    Please Login or Register  to view this content.
    This code is for one column. Now my problem is that I need to pull over data from 11 columns from sheet B to sheet A. So by my method so far I need the macro has to perform the similar task 11 times. Performing the lookup 11 times might have been ok a month ago but I have learned so much lately that I know that this approach is ridiculous. With proper VBA code, setting variables etc I bet its possible to do one lookup or match between these two sheets, get those info from those 11 cells and place them in corresponding cells on sheet A.

    So the following is what I need.
    MATCH lookup value is from sheet KHL_YM26 column 1
    MATCH lookup array is from sheet zflex column 26


    Now it should take the info from sheet zflex from following columns:
    40 27 10 25 9 29 11 31 35 36 37

    And place them to sheet KHL_YM26 columns in respective order:
    20 29 30 31 34 35 36 37 38 39 40

    Or if its visually better to understand it
    zflex -----> KHL_YM26
    40 -----> 20
    27 -----> 29
    10 -----> 30
    25 -----> 31
    N/A -----> N/A
    N/A -----> N/A
    9 -----> 34
    29 -----> 35
    11 -----> 36
    31 -----> 37
    35 -----> 38
    36 -----> 39
    37 -----> 40


    It would be nice to know how to achieve this in an efficient way without writing them formulas in and performing unnecessary lookups. I need to do this with some more sheets it would be nice to learn how its properly done. Doing like 20 lookups in total from 3 sheets is not efficient at all.

    I have attached xls with real data. Both sheets have header rows.



    Any help would be extremely appreciated.
    Cheers
    Rain
    Attached Files Attached Files
    Last edited by rain4u; 06-21-2011 at 07:22 PM.

  2. #2
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    Any ideas?

  3. #3
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    hi, Rain, question: 40 column on zflex sheet is empty, why do we use it?

  4. #4
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    apart from that, it looks like this, one of the ways to make it:

    see attachment, run code "test"

    Preparation to run code "test":

    1. [Alt+F11] - open Visual Basic window
    2. Tools - References - find and tick "Microsoft Activex Data Objects 2.0 Library"
    3. Press "Ok"
    4. Close Visual basic window

    Notes:

    1. Sheet names are hardcoded so if names are different the code must be amended.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    Hi watersev
    Thank you for the code. It works brilliantly. Very short code. You should see mine with like bazillion lines.
    If you have some time I would like to understand and learn from the code so I can use it with some other sheets. Can we break the code to pieces. I would really like to learn from it.

    Full code
    Please Login or Register  to view this content.


    x = .Range(.[af2], .Cells(Rows.Count, "a").Offset(, 32))
    1) Why is this af2 column chosen? Its not the beginning or the end on either sheets, its kinda in middle of both and does not contain any specific information. I also noticed column af also happens to be column 32. Any connection between these two? What is the function for this line? How does it operate?

    "SELECT T1.F27,T1.F10,T1.F39,T1.F1,T1.F1,T1.F9,T1.F29,T1.F11,T1.F31,T1.F35,T1.F36,T1.F37 FROM [zflex$A2:AL65536]
    2) T1.Fxx - xx stands for the respective column from the source sheet (zflex), correct yes? And F1 will leave columns blank/untouched, correct?
    3) [zflex$A2:AL65536] - this is just a source area correct? If I would like it to extend further I would change the value of AL65536 to something else?

    T1 LEFT OUTER JOIN [KHL_YM26$A2:A65536] T2 ON T1.F1=T2.F1", cn, adOpenStatic, adLockOptimistic
    [ac2].CopyFromRecordset rs:

    4) I don't understand which line on the code is actually marrying up column Z on source sheet and column A from destination sheet? I don't have clue for the either one? My only guess is that it is to do with something in this line above. What and how would I have to change on the code if it would be different parameters? Like instead column A It would be column C that I would be matching up with column E on source sheet etc.

    [ac2].CopyFromRecordset rs:
    5) Is ac2 where it starts to count columns to be replaced i.e. T1, T2, T3. T1 = AC2, T2 = AD2, T3 = AE2 etc... Correct?


    If you could help me to learn from this code I would be very happy as I would like to repeat the same method with some other reports.


    Kind regards
    Rain

  6. #6
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    hi, Rain

    1. As columns 32,33 should not change after the code execution, we take their values to array and paste it's value after code execution. "A" column is used to count rows with data (it never has empty fields). In the end it's just selecting 33,34 columns data without first row (header)
    2. Yes
    3. Correct. Yes (within 65536 rows)
    4. LEFT OUTER JOIN states for joining left table with corresponding values of the right table (comes from SQL). Column C will be T1.F3, column E will be T2.F5.
    5. This line [ac2].CopyFromRecordset rs outputs selected data to the sheet starting from [ac2] as the left upper cell.

    This method has some limitations so its use is rather rare though sometimes its elegant and easy.

  7. #7
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    Hi watersev
    I'm still struggling to fully understand 4. Sorry to be bit pain. Can you help me bit more?
    Please Login or Register  to view this content.

    Quote Originally Posted by watersev View Post
    4. LEFT OUTER JOIN states for joining left table with corresponding values of the right table (comes from SQL). Column C will be T1.F3, column E will be T2.F5.
    If this is the case, then as per current code it states T1.F1=T2.F1. Does this mean its matching column A from source sheet (xflex) with column A on destination sheet (KHL_YM26)? It should be matching column Z (col 26) from source sheet (zflex) with column A from destination sheet (KHL_YM26). Should it read T1.F26=T2.F1 instead? I changed this on the code and it still looked to be working but I'm not 100% sure.


    FROM [zflex$A2:AL65536] T1 LEFT OUTER JOIN [KHL_YM26$A2:A65536]
    What role does KHL_YM26$A2:A65536 play in terms when matching the columns. If it would be column C on sheet KHL_ym26. then other than changing T1.F1=T2.F1 to T1.F1=T2.F3 would I also have to change KHL_YM26$A2:A65536 to KHL_YM26$C2:C65536. Or this has to remain the same?

    I would be very thankful if you can teach me bit more about it.
    Cheers
    Rain

  8. #8
    Forum Expert
    Join Date
    11-29-2010
    Location
    Ukraine
    MS-Off Ver
    Excel 2019
    Posts
    4,168

    Re: VBA: Index/Match. 1 lookup value, 1 lookup array. copies cells from multple colum

    Quote Originally Posted by rain4u View Post
    If this is the case, then as per current code it states T1.F1=T2.F1. Does this mean its matching column A from source sheet (xflex) with column A on destination sheet (KHL_YM26)? It should be matching column Z (col 26) from source sheet (zflex) with column A from destination sheet (KHL_YM26). Should it read T1.F26=T2.F1 instead?
    That's right. Experiment and practice - the best way to learn something.

    Quote Originally Posted by rain4u View Post
    FROM [zflex$A2:AL65536] T1 LEFT OUTER JOIN [KHL_YM26$A2:A65536]
    What role does KHL_YM26$A2:A65536 play in terms when matching the columns
    [KHL_YM26$A2:A65536] T2 defines range for table 2 - T2, column C is not in the range, you will get error. Table defining range should be changed to [KHL_YM26$C2:C65536] T2 and T1.F1 will be column C then.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1