I have duplicate rows which represent customers who have bought different items. The item purchased are in one column. I would like to move them to separate columns so that I can import to a different program. So my data looks like something this:
John book
John ball
John bat
Tom book
Tom bat
Kate ball
I would like the data on items purchased moved to separate columns, something like this:
John book ball bat
Tom book - bat
Kate - ball -
Where each of book, ball, and bat has it's own column.
1 Answer
This formula, filled down from A8, will list the customer names, removing duplicates:
=IFERROR(INDEX($A$1:$A$6,MATCH(0,COUNTIF(A$7:A7,$A$1:$A$6),0)),"")
This is an array formula, so it must be entered with CTRLShiftEnter, rather than just Enter.
This formula, filled right and down from B8, will list the purchases by each customer:
=IF(SUMPRODUCT(($A$1:$A$6=$A8)*($B$1:$B$6=INDEX($B$1:$B$3,COLUMN(A1))))>0, INDEX($B$1:$B$3,COLUMN(A1)),"")
Note that the last formula relies on the fact that B1:B3 is an ordered list of the purchases. This is probably coincidental, and a similar list could be located anywhere. If the list were horizontal, instead of vertical, the formula would be simpler. Say the list was in E1:G1, then the formula would be:
=IF(SUMPRODUCT(($A$1:$A$6=$A8)*($B$1:$B$6=E$1))>0,E$1,"")
And an array formula similar to the first one, filled right from E1, could list the purchases there:
=IFERROR(INDEX($B$1:$B$6,,MATCH(0,COUNTIF($D1:D1,$B$1:$B$6),0)),"")
