Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match based on a lookup and then flag
I have 2 sheets. 1 sheet has 3 columns of data, the other has 3
also. 1 of the columns in each sheet contains the same information. I need to do a lookup from sheet1 to sheet 2, based on the column in common and then update the row in sheet 1 with the data from sheet 2. It sounds like an easy vlookup, however, in each sheet there are multiple occurences of the data in the column in common, but I need to put the various pieces of data from sheet 2 into the rows in sheet 1. For example: sheet 1 Sheet2 Col1 Col2 Col3 Col1 Col2 Col3 A01 23456 123-1 292-1 ABC P000123 A02 23456 123-1 176-1 DEF P000124 A03 23456 123-1 292-1 GHI P000145 A04 23456 123-1 123-1 JKL P000654 B01 23456 176-1 123-1 MNO P000754 B02 12345 292-1 123-1 PQR P000275 B03 12345 292-1 292-1 STU P000198 B04 12345 292-1 123-1 VWX P000175 I need to be able to match sheet1.col3 to sheet2.col1 and have a unique value from sheet2.col3 be used and not repeated. The eventual output that I need is Col1 and col3 from sheet 1 and col 3 from sheet 2 in a single sheet. sheet 3 - Final result Col1 Col2 Col3 A01 123-1 P000175 A02 123-1 P000654 A03 123-1 P000754 A04 123-1 P000275 B01 176-1 P000124 B02 292-1 P000145 B03 292-1 P000198 B04 292-1 P000123 Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match based on a lookup and then flag
Try...
Sub CombineData() 'Get data from sheets Dim vD1, vD2, sAddr As String vD1 = Sheets("Sheet1").UsedRange vD2 = Sheets("Sheet2").UsedRange sAddr = Sheets("Sheet1").UsedRange.Address 'Config data for output Dim j As Long, k As Long For j = LBound(vD1) To UBound(vD1) vD1(j, 2) = vD1(j, 3): vD1(j, 3) = "" Next 'j For j = LBound(vD2) To UBound(vD2) For k = LBound(vD1) To UBound(vD1) If vD1(k, 2) = vD2(j, 1) _ And vD1(k, 3) = "" _ Then vD1(k, 3) = vD2(j, 3): Exit For Next 'k Next 'j 'Dump the data into Sheet3 Sheets("Sheet3").Range(sAddr) = vD1 End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match based on a lookup and then flag
SLICK! Thanks!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match based on a lookup and then flag
It works like a charm. However, ini trying to learn more from this, I
have some questions. The line of code in the For next loop What exactly is this doing? vD1(j, 2) = vD1(j, 3): vD1(j, 3) = "" And in the double loop through both sheet1 and sheet2, I guess I am not following that either. The (k,2) and (j,1) refer to dimensions - same as columns in this case on the 2 sheets?? For j = LBound(vD2) To UBound(vD2) For k = LBound(vD1) To UBound(vD1) If vD1(k, 2) = vD2(j, 1) _ And vD1(k, 3) = "" _ Then vD1(k, 3) = vD2(j, 3): Exit For Next 'k Next 'j Sub CombineData() * 'Get data from sheets * Dim vD1, vD2, sAddr As String * vD1 = Sheets("Sheet1").UsedRange * vD2 = Sheets("Sheet2").UsedRange * sAddr = Sheets("Sheet1").UsedRange.Address * 'Config data for output * Dim j As Long, k As Long * For j = LBound(vD1) To UBound(vD1) * * vD1(j, 2) = vD1(j, 3): vD1(j, 3) = "" * Next 'j * For j = LBound(vD2) To UBound(vD2) * * For k = LBound(vD1) To UBound(vD1) * * * If vD1(k, 2) = vD2(j, 1) _ * * * * * And vD1(k, 3) = "" _ * * * * * Then vD1(k, 3) = vD2(j, 3): Exit For * * Next 'k * Next 'j * 'Dump the data into Sheet3 * Sheets("Sheet3").Range(sAddr) = vD1 End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Creating a Flag based on cell value | Excel Programming | |||
Rows to be moved from one flag to another flag | Excel Programming |