Formula to transpose data

[WynH2017]

Sorry, first post so forgive me if I get anything wrong.

I am a fairly experienced excel user and happy to use any formula for my requirements I'm using 64 bit Excel 2016.

My issue is trying to transpose large amounts of data using a formula (vlookup/hlookup/index match are in my mind but not enough to make it work)

What I have:
A B C D
123 xxx ccc hhh
456 yyy ddd iii
456 zzz eee jjj
456 aaa fff kkk
789 bbb ggg lll

What I want:
A B1 B2 B3 C1 C2 C3 D1 D2 D3
123 xxx ccc hhh
456 yyy zzz aaa ddd eee fff iii jjj kkk
789 bbb ggg lll

But I have 5000 rows of data and B1-B3 etc would go as far as 50 extra columns

Any help would be much appreciated.

 

[Jborg]

Hi Pete_SSs,

Thanks for pointing it out. However the formula works across columns as you might have seen already. I could not see the end result clearly.

Yes nice to be here. Always learning from all of you Excel Experts

 

[WynH2017]

Thanks all, I shall give these solutions a go and see what works.
@Peter_SSs there are 5000 rows of data and 30 columns. Column A is the one in which I have duplicates and Column AB is the one I'd like transposed into further columns depending on how many duplicate entries there are in column A.

 

[WynH2017]

Thanks all. Managed to make it work with a variation on JBorg's formula

 

[Peter_SSs]

Thanks all. Managed to make it work with a variation on JBorg's formula

Could you post the final formula used?

 

[Jborg]

Hi pete,

i think he adjusted the column part:

=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(E7:$F7)/3+1),MOD(COLUMNS($E8:E8)-1,3)+1),"")

 

[Peter_SSs]

Hi pete,

i think he adjusted the column part:

=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(E7:$F7)/3+1),MOD(COLUMNS($E8:E8)-1,3)+1),"")

John

Do you have the sample data in A1:D5? If not, where?

Do you still have the 123, 456 and 789 in A12:A4? If not where?

What cell did you start this new formula in?

 

[Jborg]

Hi Pete,

A12 to A14 123 456 789

Then Put the formula in C12 and drag over and down.

=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(C12:$D12)/3+1),MOD(COLUMNS($C12:C12)-1,3)+1),"")

 

[Peter_SSs]

Hi Pete,

A12 to A14 123 456 789

Then Put the formula in C12 and drag over and down.

=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(C12:$D12)/3+1),MOD(COLUMNS($C12:C12)-1,3)+1),"")

Thanks. That produces what you see in rows 12:14 below. The expected result shown by the OP in post 1 is repeated in rows 17:19. There are only 5 cells that match the exact value and location requested by the OP. Hence my asking what formula the OP used to produce the result they wanted. Perhaps they changed the required output, but I was just trying to understand.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	123	xxx	ccc	hhh
2	456	yyy	ddd	iii
3	456	zzz	eee	jjj
4	456	aaa	fff	kkk
5	789	bbb	ggg	lll
6
11
12	123		xxx	ccc	hhh
13	456		yyy	ddd	iii	zzz	eee	jjj	aaa	fff	kkk
14	789		bbb	ggg	lll
15
16
17	123	xxx			ccc			hhh
18	456	yyy	zzz	aaa	ddd	eee	fff	iii	jjj	kkk
19	789	bbb			ggg			lll
20
Transpose

Cell Formulas
Range	Formula
C12	{=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(C12:$D12)/3+1),MOD(COLUMNS($C12:C12)-1,3)+1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jborg]

Hi Pete,

The output shown in Cells A12 to A14 is what the formula produces. They may have changed the desired output??

 

[Peter_SSs]

The output shown in Cells A12 to A14 is what the formula produces.

The formula starts in C12 you said. How can it produce anything in A12:A14? I thought that you entered those manually ("First I listed an original of your numbers starting in cell A12")?

They may have changed the desired output??

No, post 1 has never been edited. As I mentioned in post 9, if you go back to post 1 and click on "Reply With Quote" and look at the OP's post, you should see the expected layout that I set out in rows 17:19 above. Here is a link to a snapshot of that "Reply With Quote" https://www.dropbox.com/s/nivvn8atd35uxrl/WynH2017%20Quote.JPG?dl=0