I am having an issue to find the magic ArrayFormula
Here is my request. Suppose I have a Sheet1 like this with RequestIDs and MDM_Keys getting the LOGS of MDM Request. This table is filled with forms and new rows are added automatically
| RequestID | MDM_Key | MDM_Attr01 | MDM_Attr02 |
|---|---|---|---|
| R001 | A | A | EUR |
| R001 | B | B | USD |
| R001 | C | C | JPY |
| R002 | A | A Name1 | EUR |
| R002 | B | B Name1 | USD |
| R003 | C | C Name1 | BRL |
| R004 | A | A Name2 | EUR |
I would like to create a Sheet2 to get the last status of my MDM, meaning
| NbOfRequest | LastRequestID | MDM_Key | MDM_Attr01 | MDM_Attr02 |
|---|---|---|---|---|
| 3 | R004 | A | A Name2 | EUR |
| 2 | R002 | B | B Name1 | USD |
| 2 | R003 | C | C Name1 | BRL |
Probably I need to create an additional column in Sheet1 to concatenate RequestID and MDM_Key
Anyway, I am not able to find the ArrayFormula to get the LastRequestID. Thank you for your advices
I have created a public Sheet https://docs.google.com/spreadsheets/d/1JDAP3DAw1ngb0_BRF75I-xWhZ0lyLdBfYnVAsUqSp9c/edit?usp=sharing
You can do this with a reverse vlookup.
Try:
=query({"NbOfRequest","LastRequestID",B7:D7;ArrayFormula({countif(B8:B,unique(B8:B)),VLookup(unique(B8:B),sort({B8:B,A8:A,C8:D},row(A8:D),0),{2,1,3,4},0)})},"where Col2<>'#N/A'",1)