A tricky one (probably not for you guys)

[dixon1983]

Hi,

This could require a very long code but I only have a basic knowledge of VBA.

I have a worksheet called "Day". In this sheet in Column A I have log numbers. This log number is used as the Vlookup reference to r other fields in the row.

The sheet that the data is taken from is called 'Database'. This sheet contains thousands of records and is sorted by log number. There can be numerous records with the same log number as each log number represents a different part of a consignment.

I would like a macro uses the log numbers in the 'Day' sheet to look up log numbers in the database and return every row of data with that log number in Column A in a new sheet.

E.G Log 45678 has 5 different rows in the Database sheet.
The macro would copy these 5 rows of data from the Databse sheet into a new sheet. It would then repeat this for every different log number.

Hope someone can help. Hope it makes sense....

 

[rabarnes]

Hi Dixon

I'm fairly new to this but this is exactly the kind of thing I have been working on recently and my formula seems to work ok. If anyone knows of any way of simplifying it though, please let me know!

You don't need a macro, just a series of formulae. Try this:

=INDEX(Database!$A$4:$Z$5000,MATCH("*"&'Day'!$B$1&"*",Database!$A$4:$A$5000,0)+COUNTIF(Database!$A$4:$A$5000,$B$1)-COUNTIF(Database!$A$4:$A$5000,$B$1),2)

Where B1 is the log number you are looking for.

Then to get the next result for that log number, simply add +1, +2, +3, to the second "countif" formula i.e:

=INDEX(Database!$A$4:$Z$5000,MATCH("*"&'Day'!$B$1&"*",Database!$A$4:$A$5000,0)+COUNTIF(Database!$A$4:$A$5000,$B$1)-COUNTIF(Database!$A$4:$A$5000,$B$1)+1,2)

The formula is made up as follows:

Match is the row number you are looking for
Countif is the number of times your log number appears in the data
The value at the end of the formula should reflect the column number i.e. a=1, b=2, c=3 etc.

***For this formula to work, your data has to be sorted in ascending order (by log number)***

Like I said before, I am new to this and the formula I have come up with is a result of a load of similar formulae which I have found on this site. There is probably a much simpler way of doing it, but I know that this works for me so I'm happy with it!

Hope this helps.

Rowan