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)

enter image description here

And to produce the values in A15 and A16, you can check out unique().