Hi all, I have the following problem to solve using Excel.
I have a list of sample numbers in one Excel file ('file 1' in attached file).
I have to couple each sample identifier from file 1 (cells in gray) to information from another sample of the same person ('file 2'). The information I need should be imported in columns C and D (which has been partially filled in for clarity).
The information from file 2 that I need concerns 1) the test outcome (column I), and 2) the test Date (column G), that belongs to the same person (column F) as the sample identifier belongs to. However, the information I need is NEVER on the same row as the gray sample identifiers, but must be extracted from columns G & I on the row where either 'Positive' or 'Negative' is in column H.
For instance, for sample 2 (and 11 and 3) I need 'Negative' from position I22 and the corresponding date in G22. For Sample 16, I need 'Negative' from I11 and date from G11.
I know how to retrieve cell content from a given relative position using INDEX and MATCH functions:
=INDEX('K:\[file1.xlsx]Tab1'!$A$1:$B$10000;MATCH(A1;'K:\file2.xlsx]Tab2'!$A$1:$A$10000;0);2)
However, the difficulty here is that I need to lookup for every sample identifier, the test outcome and date from the row where test outcome (column I) is NOT "-" for the SAME person, given in column F.
An extra difficulty would be that for person A there are multiple rows where there is not "-" in column I, and I'd need in different columns both of the test outcomes (I5 and I9) and both the dates (G5 and G9).
Any help about how to approach this is much appreciated.
I have also posted this thread on Excelforum: How to search within a dynamic range
I have a list of sample numbers in one Excel file ('file 1' in attached file).
I have to couple each sample identifier from file 1 (cells in gray) to information from another sample of the same person ('file 2'). The information I need should be imported in columns C and D (which has been partially filled in for clarity).
The information from file 2 that I need concerns 1) the test outcome (column I), and 2) the test Date (column G), that belongs to the same person (column F) as the sample identifier belongs to. However, the information I need is NEVER on the same row as the gray sample identifiers, but must be extracted from columns G & I on the row where either 'Positive' or 'Negative' is in column H.
For instance, for sample 2 (and 11 and 3) I need 'Negative' from position I22 and the corresponding date in G22. For Sample 16, I need 'Negative' from I11 and date from G11.
I know how to retrieve cell content from a given relative position using INDEX and MATCH functions:
=INDEX('K:\[file1.xlsx]Tab1'!$A$1:$B$10000;MATCH(A1;'K:\file2.xlsx]Tab2'!$A$1:$A$10000;0);2)
However, the difficulty here is that I need to lookup for every sample identifier, the test outcome and date from the row where test outcome (column I) is NOT "-" for the SAME person, given in column F.
An extra difficulty would be that for person A there are multiple rows where there is not "-" in column I, and I'd need in different columns both of the test outcomes (I5 and I9) and both the dates (G5 and G9).
Any help about how to approach this is much appreciated.
I have also posted this thread on Excelforum: How to search within a dynamic range
Attachments
Last edited by a moderator: