Transpose and repeat formula or script

[sneaky911]

I have the following data and need a formula or script that will transpose at each change in location. I have several thousand cells in column A to search through. My data is not consistently every 5th cell. (Locations= 001, 002, 003 etc.)

Before:

M-G-001-A

M-G-001-B

M-G-001-C

M-G-001-D

M-G-002-A

M-G-002-B

M-G-002-C

M-G-002-D

M-G-003-A

M-G-003-B

M-G-003-C

M-G-003-D After: M-G-001-A M-G-001-B M-G-001-C M-G-001-D M-G-002-A M-G-002-B M-G-002-C M-G-002-D M-G-003-A M-G-003-B M-G-003-C M-G-003-D <colgroup><col><col span="3"></colgroup><tbody> </tbody> <colgroup><col><col span="3"></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[Momentman]

If you have exactly 4 entries before a change, then

Book1
	A	B	C	D	E	F	G
1
2		M-G-001-A		M-G-001-A	M-G-001-B	M-G-001-C	M-G-001-D
3		M-G-001-B		M-G-002-A	M-G-002-B	M-G-002-C	M-G-002-D
4		M-G-001-C		M-G-003-A	M-G-003-B	M-G-003-C	M-G-003-D
5		M-G-001-D
6		M-G-002-A
7		M-G-002-B
8		M-G-002-C
9		M-G-002-D
10		M-G-003-A
11		M-G-003-B
12		M-G-003-C
13		M-G-003-D
Sheet4

Cell Formulas
Range	Formula
D2	=IFERROR(INDEX($B$2:$B$13,4*ROWS($1:1)-3+COLUMNS($A:A)-1),"")

 

[sneaky911]

This worked perfectly! Appreciate your help.

 

[sneaky911]

If you have exactly 4 entries before a change, then

	A	B	C	D	E	F	G
1
2		M-G-001-A		M-G-001-A	M-G-001-B	M-G-001-C	M-G-001-D
3		M-G-001-B		M-G-002-A	M-G-002-B	M-G-002-C	M-G-002-D
4		M-G-001-C		M-G-003-A	M-G-003-B	M-G-003-C	M-G-003-D
5		M-G-001-D
6		M-G-002-A
7		M-G-002-B
8		M-G-002-C
9		M-G-002-D
10		M-G-003-A
11		M-G-003-B
12		M-G-003-C
13		M-G-003-D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =IFERROR(INDEX($B$2:$B$13,4*ROWS($1:1)-3+COLUMNS($A:A)-1),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I apologize but how do I alter this for exactly 3 entries before a change?

 

[sneaky911]

I apologize but how do I alter this for exactly 3 entries before a change?​

 

[Momentman]

Book1
	B	C	D	E	F
1	M-G-001-A		M-G-001-A	M-G-001-B	M-G-001-C
2	M-G-001-B		M-G-002-A	M-G-002-B	M-G-002-C
3	M-G-001-C		M-G-003-A	M-G-003-B	M-G-003-C
4	M-G-002-A
5	M-G-002-B
6	M-G-002-C
7	M-G-003-A
8	M-G-003-B
9	M-G-003-C
Sheet3

Cell Formulas
Range	Formula
D1	=IFERROR(INDEX($B$1:$B$9,3*ROWS($1:1)-3+COLUMNS($A:A)),"")
D4	=IFERROR(INDEX($B$1:$B$9,3*ROWS($1:4)-3+COLUMNS($A:A)),"")
E4	=IFERROR(INDEX($B$1:$B$9,3*ROWS($1:4)-3+COLUMNS($A:B)),"")
F4	=IFERROR(INDEX($B$1:$B$9,3*ROWS($1:4)-3+COLUMNS($A:C)),"")

 

[sneaky911]

Thank you once again!