Search in dynamic range

kdew

New Member
Joined
Apr 28, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
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
 

Attachments

  • example.png
    example.png
    37 KB · Views: 6
Last edited by a moderator:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the forum.

Have a look at this and see if it will work. Inputs are in B27 and down, and the corresponding person, the number of entries to find, and the date/outcome combinations spill over into the columns to the right. If this is close, then the range references will need to be modified to point to the correct files, worksheets, and ranges in your setup. This is configured to find up to three entries in any given "person" block. If more than three entries (i.e., positives + negatives) are present, then simply copy the last two columns (the Date and Test Outcome results table) and paste them immediately to the right to create new receiver cells for a 4th entry, and so on. This approach finds the data matching the criteria you've described, but it does not carry over the cell fill color...I believe VBA would be needed to do that efficiently.
Example Excel file.xlsx
ABCDEFGHIJ
1
2File 1File 2
3
4Sample identifierDateTest outcomePersonDateSample identifierTest outcome
51610/17/2019 3:30NEGATIVEA12/13/2019 7:4518POSITIVE
6200A10/14/2019 4:0010-
72110/18/2019 4:30POSITIVEA11/14/2019 7:107-
80A10/15/2019 3:5519-
913A11/15/2019 7:209NEGATIVE
1015B10/16/2019 4:058-
11300B10/17/2019 3:304NEGATIVE
12400B12/17/2019 7:5016-
132C10/18/2019 4:301POSITIVE
14500C10/19/2019 4:3021-
1511D11/18/2019 8:205POSITIVE
16600E10/19/2019 3:3513-
173E10/20/2019 3:3515-
186F11/20/2019 7:2512-
19700F12/20/2019 7:502-
2016F10/21/2019 3:2511-
21800F10/22/2019 3:003-
2219F11/22/2019 9:4017NEGATIVE
2310G10/23/2019 4:1514POSITIVE
241000H12/23/2019 7:456-
25
26Sample IDPersonCountDateTest OutcomeDateTest OutcomeDateTest Outcome
271C110/18/19 4:30POSITIVE    
282F111/22/19 9:40NEGATIVE    
293F111/22/19 9:40NEGATIVE    
304B110/17/19 3:30NEGATIVE    
315D111/18/19 8:20POSITIVE    
326H0no matchno match    
337A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE  
348B110/17/19 3:30NEGATIVE    
359A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE  
3610A212/13/19 7:45POSITIVE11/15/19 7:20NEGATIVE  
3711F111/22/19 9:40NEGATIVE    
3812F111/22/19 9:40NEGATIVE    
Blad1
Cell Formulas
RangeFormula
C27:C38C27=IFERROR(INDEX($F$5:$F$24,MATCH($B27,$H$5:$H$24,0)),"no match")
D27:D38D27=COUNTIFS($F$5:$F$24,C27,$I$5:$I$24,"<>-")
E27:E38E27=IF($D27=0,"no match",INDEX($G$5:$G$24,AGGREGATE(15,6,(ROW($G$5:$G$24)-ROW($G$4))/(($F$5:$F$24=$C27)*($I$5:$I$24<>"-")),INT((COLUMNS($E$26:E$26)-1)/2)+1)))
F27:F38F27=IF($D27=0,"no match",INDEX($I$5:$I$24,AGGREGATE(15,6,(ROW($G$5:$G$24)-ROW($G$4))/(($F$5:$F$24=$C27)*($I$5:$I$24<>"-")),INT((COLUMNS($E$26:F$26)-1)/2)+1)))
G27:G38,I27:I38G27=IFERROR(INDEX($G$5:$G$24,AGGREGATE(15,6,(ROW($G$5:$G$24)-ROW($G$4))/(($F$5:$F$24=$C27)*($I$5:$I$24<>"-")),INT((COLUMNS($E$26:G$26)-1)/2)+1)),"")
H27:H38,J27:J38H27=IFERROR(INDEX($I$5:$I$24,AGGREGATE(15,6,(ROW($G$5:$G$24)-ROW($G$4))/(($F$5:$F$24=$C27)*($I$5:$I$24<>"-")),INT((COLUMNS($E$26:H$26)-1)/2)+1)),"")
 

kdew

New Member
Joined
Apr 28, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi KRice. Many thanks for your reply. Yes, this effectively solves the problem.
Thanks a lot!
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
Great news! You're welcome...and I appreciate the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,217
Messages
5,640,943
Members
417,180
Latest member
nomans2325

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top