CODEWITHSUNDEEP
google-sheets
Matt Rogers
Matt Rogers

Reputation: 156

Get whole row by two unique fields in a Google Sheet

I have tried to achieve this for the last few hours with no luck so I am appealing for help.

I have a google form which saves to a google spreadsheet and the responses are recording in a structure as per below.

 ---------------------------------------------------------
|  WEEK  |  CATEGORY  |  COL1   |   COL2   |  TIMESTAMP   |
|--------|------------|---------|----------|--------------|
|  1     |  CAT1      |  VALUE1 |  VALUE1  |  YYYY-MM-DD  |
|  1     |  CAT1      |  VALUE2 |  VALUE2  |  YYYY-MM-DD  |
|  1     |  CAT2      |  VALUE1 |  VALUE1  |  YYYY-MM-DD  |
|  2     |  CAT3      |  VALUE1 |  VALUE1  |  YYYY-MM-DD  |
|  2     |  CAT3      |  VALUE2 |  VALUE2  |  YYYY-MM-DD  |
 ---------------------------------------------------------

I need a formula which I can use in google sheets to take the most recent entry where WEEK and CATEGORY together are unique values.

Eg using the above table, the formula would result in something that looks like this.

 ---------------------------------------------------------
|  WEEK  |  CATEGORY  |  COL1   |   COL2   |  TIMESTAMP   |
|--------|------------|---------|----------|--------------|
|  1     |  CAT1      |  VALUE2 |  VALUE2  |  YYYY-MM-DD  |
|  1     |  CAT2      |  VALUE1 |  VALUE1  |  YYYY-MM-DD  |
|  2     |  CAT3      |  VALUE2 |  VALUE2  |  YYYY-MM-DD  |
 ---------------------------------------------------------

Any help would be greatly appreciated!

Thanks,

Upvotes: 1

Views: 32

Answers (1)

ttarchala
ttarchala

Reputation: 4567

There isn't a super clean solution for what is basically a dynamic self-join in SQL parlance, but this might be manageable:

  1. Get a list of maximum timestamps per category and week enter image description here

  2. Filter the original table by this derived list using a concatenated key enter image description here

Any attempts to improve this answer appreciated (in particular the string concatenation as key is not very elegant).

Upvotes: 1

Related Questions