View Single Post
 
Old 04-20-2017, 05:07 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

For example, for R22_Batch, start by getting latest date...

So in column E2 try array formula:

=MAX(IF('R22_BATCH'!$A$2:$A$14=A2,'R22_BATCH'!$E$2 :$E$14))

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

You may have to format as date... use custom format and enter type as: m/d/yyyy;-0;;@

This will get rid of the 0 dates values.

Then you can get the matching data...

in B2, another array formula:

=IFERROR(INDEX('R22_BATCH'!$B$2:$B$14,MATCH(1,('R2 2_BATCH'!$A$2:$A$14=A2)*('R22_BATCH'!$E$2:$E$14=E2 ),0)),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

Similarly you can get the info for the other columns...

Then repeat the steps for the other sheets.

Hope this helps.
Reply With Quote