Auto transposing text?

[mikemeadeuk]

Hello, its quite hard to explain what I want to do. I have a list of ITEMs in column A with several duplicates and I have the VALUEs in columb B, I want to be able to traspose the VALUEs for each ITEM on one line.

I have attached an example file, I have needed to do this for years but always end up doing it manually as I havent found a way of doing this. I have thousands of rows so doing it manually takes ages!

Any help would me mega appreciated.

ThanksHOW_TO_COMBINE_DATA.xlsx

[AlKey]

Array formula in D1 and pull formula across and down

=IFERROR(INDEX($B$2:$B$12,MATCH(0,IF($A$2:$A$12=$C1,COUNTIF($C1:C1,$B$2:$B$12)),)),"")

***Array formula must be entered by using key combination of CTRL+SHIFT+ENTER and not just ENTER

Row\Col	A	B	C	D	E	F	G	H	I
1	ITEM	VALUE	COLOUR	red	blue	orange	green	black
2	colour	red	SIZE	small	large	medium	extra small	extra large	XXL
3	colour	blue
4	colour	orange
5	colour	green
6	colour	black
7	size	small
8	size	large
9	size	medium
10	size	extra small
11	size	extra large
12	size	XXL

[mikemeadeuk]

worked like a charm, I cant believe ive been doing manually all this time! thanks man!

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

[mikemeadeuk]

Only thing I would say is that its very slow with large datasets, not sure of there could be a quicker way to avoid searching through data already processed if you know what I mean?