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

Any ideas?

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

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.

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

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.

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.

Originally Posted by watersev

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

Originally Posted by rain4u

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.

Originally Posted by rain4u

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.