1

I need to combine the data of three different columns. Thus, I have this:

-------------------------------------------------------
| ID | ID Data | Markdown 1 | Markdown 2 | Markdown 3 |
-------------------------------------------------------
| 1  |   G     |     X2     |    X3      |    X4      | 
-------------------------------------------------------
| 2  |   B     |     X6     |    X7      |    X8      | 
-------------------------------------------------------
| 5  |   Z     |     X3     |    X4      |    X5      | 
-------------------------------------------------------
| etc ... 100 rows

In the end, I want one column saying "Markdowns", thus that combines the different columns. However, I want to keep the row data, meaning I need to see for which ID's which markdowns were put:

----------------------------
| ID | ID Data | Markdowns | 
----------------------------
| 1  | G       |    X2     | 
----------------------------
| 1  | G       |    X3     | 
----------------------------
| 1  | G       |    X4     | 
----------------------------
| 2  | B       |    X6     |   
----------------------------
| 2  | B       |    X7     | 
----------------------------
| 2  | B       |    X8     | 
----------------------------
| 5  | Z       |    X3     | 
----------------------------
| 5  | Z       |    X4     | 
----------------------------
| 5  | Z       |    X5     | 
----------------------------
| etc ... 

I need this to create pivot's and graphs to show how many and which markdowns for which ID were put. Now I need to create three different pivots and combine the data manually.

I already checked this formula but with that one, it seems I have to add the formula for each row manually.

Can anyone help me with this?

0

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(IF(LEN(A1:A), 
 "♠"&A1:A&"♦"&B1:B&"♦"&C1:C&"♠"&A1:A&"♦"&B1:B&"♦"&D1:D&"♠"&A1:A&"♦"&B1:B&"♦"&E1:E, )
 ,,999^99), "♠")), "♦")))

0

  • Awesome, thank you so much!!!! – Vincent May 14 at 7:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.