matching data from table
Hi, This seems simple but I am not able to figure it out. I would appreciate
any help. Please see the example below. This is the data that I have: Col A Col B 29890 Price 20010102 48.625 20010103 47.875 20010104 47.875 20010105 48.6875 66157 Price 20010102 28.9375 20010103 30.8125 20010104 31.9375 20010105 30.6875 This is the array im trying to match to (It is on Sheet2): 29890 BP 47896 JPM 66157 USB 69032 MWD What I need is to copy BP beside 29890 and USB beside 66157 in the data without changing anything else in column B. This is a small example, my files contain about 40,000 rows of data that I need to match with their ID numbers. My data needs to look something like this. Col A Col B 29890 BP Price 20010102 48.625 20010103 47.875 20010104 47.875 20010105 48.6875 66157 USB Price 20010102 28.9375 20010103 30.8125 20010104 31.9375 20010105 30.6875 |
matching data from table
Hello,
Perhaps something like the following will work: Sub foo() On Error GoTo errHandle Range(Sheets(1).Cells(1, 1), _ Sheets(1).Cells(65536, 1).End(xlUp)).Offset(, 1). _ SpecialCells(xlBlanks).FormulaR1C1 = _ "=VLOOKUP(RC[-1],Sheet2!R1C1:R4C2,2,1)" 'Convert to Values With Range(Sheets(1).Cells(1, 2), _ Sheets(1).Cells(65536, 2).End(xlUp)) .Value = .Value End With errHandle: End Sub You might need to run it a few times if you have a large number of non-contiguous cells: http://support.microsoft.com/default...b;en-us;832293 And, your list on Sheet2 looked like it was sorted in ascending order, so the Vlookup() function I used passes True as the final argument. If it's not, pass False as the final argument. Regards, Nate Oliver |
matching data from table
You might need to run it a few times if you have a large number of
non-contiguous cells: http://support.microsoft.com/default...b;en-us;832293 Sorry, running it a few times isn't the answer, use the advice provided at the link: WORKAROUND To work around this behavior, you may want to create a looping structure in your VBA macro that handles less than the maximum 8,192 cells. I.e., pass the Vlookup() function to smaller ranges over several passes. Regards, Nate Oliver |
All times are GMT +1. The time now is 09:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com