Hi,
I have data in the following format (about 40 thousand rows, let's say this is tab Source):
I am interested in only a specific subset of subjects, and their IDs are listed in column I; rest of data should be hidden or deleted. I would normally just filter out what I need, but I have 3 limiting factors:
1. The list of subjects I am interested in ("reference list"), is almost 300 entries long.
2. Numbers in the reference list are non-contiguous and can fall anywhere between 1 and 7000
3. I cannot exclude possibility that the reference list turns out to be dynamic, e.g. individual IDs within that list may change with time, so I would need a new output whenever there's an update to the reference list.
The reference list is noted on a separate tab (let's say, tab List, cells A2:A300)
How can I quickly post-process source data to filter out, or isolate, only rows which belong to this specific list of subject IDs? I would then do pivot table for my further needs, being sure that I only look into a specific subset. Ideally I need a formula (along the lines of "check cell I2 against unique patient ID list on tab List, and if there's a match, copy row 2 in full onto tab Output; otherwise skip and check next row; stop after all rows are analyzed and copied over as appropriate"). Macro might also work though preference is a formula.
Hope I was able to explain my struggle...
I have data in the following format (about 40 thousand rows, let's say this is tab Source):
I am interested in only a specific subset of subjects, and their IDs are listed in column I; rest of data should be hidden or deleted. I would normally just filter out what I need, but I have 3 limiting factors:
1. The list of subjects I am interested in ("reference list"), is almost 300 entries long.
2. Numbers in the reference list are non-contiguous and can fall anywhere between 1 and 7000
3. I cannot exclude possibility that the reference list turns out to be dynamic, e.g. individual IDs within that list may change with time, so I would need a new output whenever there's an update to the reference list.
The reference list is noted on a separate tab (let's say, tab List, cells A2:A300)
How can I quickly post-process source data to filter out, or isolate, only rows which belong to this specific list of subject IDs? I would then do pivot table for my further needs, being sure that I only look into a specific subset. Ideally I need a formula (along the lines of "check cell I2 against unique patient ID list on tab List, and if there's a match, copy row 2 in full onto tab Output; otherwise skip and check next row; stop after all rows are analyzed and copied over as appropriate"). Macro might also work though preference is a formula.
Hope I was able to explain my struggle...