PDA

View Full Version : Solved: VBA to do job of lookups



Sir Babydum GBE
04-20-2012, 02:22 AM
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
04-20-2012, 03:53 AM
Like this

Sir Babydum GBE
04-20-2012, 05:28 AM
Brilliant - I thought it was impossible with a formula!

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

Bob Phillips
04-20-2012, 05:39 AM
Don't forget to update the formulae to accommodate the real range sizes.