Find last product sell date of agent

[kv.singh]

FORMULA TO FIND GREATEST FOR DATE FOR EACH UNIQUE AGENT AND CORRESPONDINDG PRODUCT NUMBER IN SHEET2


seach each agent number shown in sheet2 col.A in sheet2 and paste greatest date for each agent in col.B sheet2 with corresponding product number in col C sheet2


CAN ANYONE PROVIDE HELP.

[Glenn Kennedy]

B2, copied down:
=IF(A2="","",AGGREGATE(14,6,Sheet1!$D$2:$D$250/(Sheet1!$J$2:$J$250=A2),1))

C2, copied down:
=IF(A2="","",INDEX(Sheet1!$B$2:$B$250,MATCH(1,INDEX(((Sheet1!$J$2:$J$250=A2)*(Sheet1!$D$2:$D$250=B2)),0),0)))

[CARACALLA]

Sheet2


B2=IF(AND(Sheet2!$A2<>"",Sheet2!B$1<>""),LOOKUP(2,1/(Sheet1!$J$2:$J$222=Sheet2!$A2),Sheet1!B$2:B$222),"")

copy across and down

[AliGW]

Here's an alternative using the Table.Max() function in PowerQuery.

M Code:

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Please add this formula for column A to get unique list of agents.
In A2 then copied down.

=IFERROR(INDEX(Sheet1!$J$2:$J$222,AGGREGATE(15,6,ROW(Sheet1!$J$2:$J$222)/(ISERROR(MATCH(Sheet1!$J$2:$J$222,$A$1:$A1,0))),1)-ROW($J$1)),"")