Solved: VBA to do job of lookups

[Sir Babydum GBE]

Hi

As part of a shipping imports spreadsheet I have to create a lookup table.

There may be, say, a total of 500 boxes arriving on a container and there may be 1000 or so rows of info on a spreadsheet indicating the box number and its contents.

So lets say that That Column A contains the box number and column B the product code.

If there is only one product type in a box, that box will only appear once on the list. However if there are 30 product types in a box, there will be 30 rows of info - each showing the box number and product code. So far so good?

So what I'm looking to do is create a table on another sheet "Tabular". The macro would look at each row on the original sheet and transfer the box number to "Tabular". Then in column B on the same row it will put the 1st product it finds in box 1, then in column C it will put the second produc. D the third product, and so on.

Once it has found all products for Box 1 it will create a new line with the next box number on the list until it has exausted all boxes and the products they contain.

Can you help please?

Thx

BD

[Bob Phillips]

Like this

[Sir Babydum GBE]

Brilliant - I thought it was impossible with a formula!

Never let it be said that Bob doesn't know the "impossible" formula

[Bob Phillips]

Don't forget to update the formulae to accommodate the real range sizes.