Get a list of entries - similar to an SQL queries

[WindRider]

Hi,
there might be some infos about this on the forum already but I don't exactly know what to search about.

Here is what I need to do...

I have a table following this format:

ID Lot ID date
1 beer / ladies dd/mm/yyyy
2 beer dd/mm/yyyy
3 ladies dd/mm/yyyy
4 desert island dd/mm/yyyy
5 beer / ladies / desert island dd/mm/yyyy

The number of entries in the sheet varies everyday.

I would need a way to retrieve the list of all ID that contains a specific Lot ID.

The first idea I had was to write a macro that would define the last entrie in the sheet, then with this create a loop to test every Lot ID cell to determine if it contains the specific string I want to filter. If so, retreive the ID number with an offset, store it in an array and keep on....

Is there another way? Is this possible without a macro? Is there something more like an SQL query within excel that would give me the same result?

Thanks in advance for your time on this!
Regards

[JBeaucaire]

Please Login or Register  to view this content.

The number of entries in the sheet varies everyday.

I would need a way to retrieve the list of all ID that contains a specific Lot ID.

You can turn on the Data > Filter > AutoFilter for this dataset and then apply a custom filter to column B for contains: beer, that would show you.

[WindRider]

Thanks for your quick answer!

Yeah, that would obviously be a quick way for a one shot deal but...
I forgot to mention that I am working on a automised reporting system so I definitely need a formula or code solution.

I have another set of 2 sheets that uses sumproduct to generate stats and graphs.
I would like to have my stats automatically update according to the selected LotID.

Lets say the table described above is the source...
I thought that having a data sheet filled up with vLookup(in the source) and simply managing the first column of the data sheet by copying the selected ID list of the selected lot(that is the part I am trying to conceive now, the rest is in place)... that way, when recalculating excel, my graphs and stats will update and give me stats for the selected Lot only.




mmmm thinking about it, filtering by code might be a better way then scrolling through every cell though...

[WindRider]

Isn't there another solution with some formulas using INDEX, MATCH, etc?

Oups... I realize this is not in the right forum section perhaps....