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.
|