Vlookup, multiple row results

[darvistor]

Hi all. I'm working on a spreadsheet for work and need to combine some data based on multiple rows for the same ID#. I've looked at a few examples here on the forum and from around the web and just can't seem to get the formulas right when the data repeats more then twice. I've got a pretty good background in Excel and have done some array formulas and VBA code in the past, but I'm having "Excel block" with this one I guess. Because of the users of the system, I need to be able to do this without using any VBA code. So on to the file:

I've attached a small excerpt from the file I'm working on. The data gets exported from another system in which the lot #s are entered into a Grid format. When the data gets converted to Excel though, it creates a new row for a given ID# for each Lot # entered. I need to create one master sheet that links all the information together. I've used the concatenate feature on the other parts of the file(not included) when an ID is never repeated more then twice. In the case of Lot #s, there are occasions where 20 different lot #s are entered for 1 unique ID # and as designed Vlookup returns only the first value and I don't know how many concatenates I will need to do. In this example, I did an advanced filter to pull out the unique ID#s and then put in the standard vlookup to get the values for the ones that I knew had only 1 value.

Any help would be appreciated. If there is any other inf I can provide, please let me know.

Thanks
Darv

[ron2k_1]

the only way I can think of doing this is by way of array.

Look at the attached if that suits you.

Oh, also look at your Unique ID's column I've placed an array formula there as well. It add to your columns automatically any NEW entry that you may had done on Column A. If the list is not extensive you can use that. Otherwise, i recommend that you drop it as arrays can take lots of memory.