So I have 3 different columns in a excel file, what I need to is convert rows into columns by reference_number
So I have columns like this,
| REF_NO | LCY_AMOUNT | TAG |
|---|---|---|
| 001 | 200 | NEGO |
| 001 | 300 | EXCH |
| 001 | 350 | POST |
| 001 | 400 | CONF |
| 002 | 300 | NEGO |
| 002 | 400 | EXCH |
| 002 | 450 | POST |
| 002 | 500 | CONF |
What I need is Tag rows into columns like this
| REF_NO | NEGO | EXCH | POST | CONF |
|---|---|---|---|---|
| 001 | 200 | 300 | 350 | 400 |
| 002 | 300 | 400 | 450 | 500 |
Solution 1:
So went with sumifs():
SUMIFS($B$3:$B$10,$A$3:$A$10,$A15,$C$3:$C$10,B$14)
And to produce the values in A15 and A16, you can check out unique().