CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulavlookuparray-formulas
ZondaC12
ZondaC12

Reputation: 13

Reorder and regroup multiple columns by date

I'm a bit stumped on this one, would love some help on this :)

Here is my data :

+--- Col A --+- Col B --+-- Col C ---+--- Col D --+- Col E --+-- Col F ---+
|                FRANCE              |               ITALY                |
|     DATE   | Installs | Uninstalls |    DATE    | Installs | Uninstalls |
+---+----------------+----------------------------------------------------+
| 14/09/2020 |        1 |          2 | 14/09/2020 |        1 |          2 |
| 15/09/2020 |        3 |          1 | 17/09/2020 |        4 |          1 |
| 16/09/2020 |        1 |          2 | 22/09/2020 |        1 |          2 |
| 22/09/2020 |        3 |          1 | 26/09/2020 |        4 |          1 |
+------------+----------+------------+------------------------------------+

I am looking to group the results by date like this (one date = one line) :

+--- Col A --+- Col B --+-- Col C ---+--- Col D --+- Col E --+-- Col F ---+
|                FRANCE              |               ITALY                |
|     DATE   | Installs | Uninstalls |    DATE    | Installs | Uninstalls |
+---+----------------+----------------------------------------------------+
| 14/09/2020 |        1 |          2 | 14/09/2020 |        1 |          2 |
| 15/09/2020 |        3 |          1 |            |        0 |          0 |
| 16/09/2020 |        1 |          2 |            |        0 |          0 |
|            |        0 |          0 | 17/09/2020 |        0 |          0 |
| 22/09/2020 |        3 |          1 | 22/09/2020 |        1 |          2 |
|            |        0 |          0 | 26/09/2020 |        4 |          1 |
+------------+----------+------------+------------------------------------+

I am familiar with query, but having difficulties finding the right formula :(

Thank you in advance !!

Best

Upvotes: 1

Views: 83

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

In your comment you mention

I have more countries in the real data set, all on the same format (DATE | INSTALLS | UNINSTALLS)

You can use a single formula

={ArrayFormula(IFERROR(VLOOKUP(UNIQUE(SORT({A3:A;D3:D})),A3:C,{1,2,3},FALSE))), 
  ArrayFormula(IFERROR(VLOOKUP(UNIQUE(SORT({A3:A;D3:D})),D3:F,{1,2,3},FALSE)))}

You can add the next country by changing to the next set G3:I and adding the arrayformula part using the logic:

={ArrayFormula(...),
  ArrayFormula(...),
  ArrayFormula(...), 
  ArrayFormula(...),
  ArrayFormula(...)}

Upvotes: 1

Kpym
Kpym

Reputation: 4033

You can recover all the dates by:

=UNIQUE(SORT({A1:A4;D1:D4}))

Then you can extract the corresponding installs for France by:

=ArrayFormula(IFERROR(VLOOKUP(UNIQUE(SORT({$A$1:$A$4;$D$1:$D$4}));$A$1:$C$4;2;FALSE)))

And by replacing 2 with 3 you can get the uninstalls. For Italy you replace $A$1:$C$4 by $D$1:$F4$.

enter image description here

Upvotes: 0

Related Questions