Selective Transpose

[isasa74]

Hi Guys,

I need help please.
I have a long list of values for that I need to transpose, however I need a formula that can dynamically select the number of lines to transpose.

Mine it's a list of part numbers, with the Parent part numbers starting by 1 and Childs part numbers randomly starting by any other number (4-5-6 etc.)
In the list for each Parent there is a variable number of Childs parts.

The Formula should look at the Parent and transposing all the Childs in the same row of the Parent.
I'm ok as excel user but I'm not up to this level, any help is welcome and a bit of code will do as well , however a solution by a formula is preferred.


Thanks in advance

1Blueparent	after the Formula -->	1Blueparent	4child1	6child2
4child1
6child2
1Redparent		1Redparent	5child1	etc.
5child1

 

[Marcelo Branco]

A possible solution using formulas (i used a smaller data sample since xl2bb didn't accept a bigger one)

10032021 Testes.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Parent/Child	Parent	Child1	Child2	Child3	Child4	Child5	Child6	Child7	Child8
2	1XX4.772.77	1XX4.772.77	5FD4.771.71	5XX7.771.72.2	5FC7.771.71	5FD4.771.71	6XX7.777.77.2	5FC7.771.71
3	5FD4.771.71
4	5XX7.771.72.2
5	5FC7.771.71
6	1XX4.772.71S
7	5FD4.771.71
8	6XX7.777.77.2
9	5FC7.771.71
10	1XX4.772.72	1XX4.772.72	5XX7.771.72.2	6XX7.771.71	5FD4.771.71	5XX7.771.72.2	6XX7.771.73
11	1XX4.772.7S0
12	5XX7.771.72.2
13	6XX7.771.71
14	1XX4.772.73S.5
15	5FD4.771.71
16	5XX7.771.72.2
17	6XX7.771.73
18	1XX4.772.74	1XX4.772.74	5FD4.771.71	5FC7.771.71
19	5FD4.771.71
20	5FC7.771.71
21	1XX4.713.77	1XX4.713.77	6FX7.777.77
22	1XX4.772.71S
23	6FX7.777.77
24		END
Plan6

Cell Formulas
Range	Formula
C2:J23	C2	=IF($B2="","",IFERROR(INDEX($A3:INDEX($A3:$A$998,MATCH(1,INDEX(--($B3:$B$998<>""),),0)-1),AGGREGATE(15,6,(ROW($A3:INDEX($A3:$A$998,MATCH(1,INDEX(--($B3:$B$998<>""),),0)-1))-ROW($A3)+1)/(ISERROR(SEARCH("S",$A3:INDEX($A3:$A$998,MATCH(1,INDEX(--($B3:$B$998<>""),),0)-1)))),COLUMNS($C2:C2))),""))
B2:B24	B2	=IF(A2="","END",IF(AND(LEFT(A2)="1",ISERROR(SEARCH("S",A2))),A2,""))

Hope this helps

M.

 

[Peter_SSs]

Another possible formula approach using a helper column, which could be hidden.
The exact detail of the helper column formula would depend on the answer to Fluff's question in post #20. At the moment I have only allowed for one digit (0-9) to follow "S." at the end of the column A value.

isasa74.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	1XX4.772.77	1	1XX4.772.77	5FD4.771.71	5XX7.771.72.2	6XX7.777.77.2
3	5FD4.771.71	1
4	5XX7.771.72.2	1
5	6XX7.777.77.2	1
6	1XX4.772.71SXX	2	1XX4.772.71SXX	5FD4.771.71	5TTC.777.71	5XX7.771.72.2	1XX4.772.72S.1	5FD4.771.71	6FX7.777.77	5FC7.771.71
7	5FD4.771.71	2
8	5TTC.777.71	2
9	5XX7.771.72.2	2
10	1XX4.772.72S.1	2
11	5FD4.771.71	2
12	6FX7.777.77	2
13	5FC7.771.71	2
14	1XX4.772.73	3	1XX4.772.73	5FD4.771.71	5FC7.771.71	1XX4.772.74S	5FD4.771.71	5XX7.771.72.2	5FC7.771.71
15	5FD4.771.71	3
16	5FC7.771.71	3
17	1XX4.772.74S	3
18	5FD4.771.71	3
19	5XX7.771.72.2	3
20	5FC7.771.71	3
21	1XX4.713.77S.X	4	1XX4.713.77S.X	6XX7.777.77.2	5XP4.771.77	5XX4.772.74.3
22	6XX7.777.77.2	4
23	5XP4.771.77	4
24	5XX4.772.74.3	4
Sheet7

Cell Formulas
Range	Formula
B2:B24	B2	=B1+AND(LEFT(A2,1)="1",RIGHT(A2,1)<>"S",RIGHT(A2,3)<>"S."&SEQUENCE(10,,0))
C2:F2,C21:F21,C14:I14,C6:J6,C3:C5,C7:C13,C15:C20,C22:C24	C2	=IF(B2=B1,"",TRANSPOSE(FILTER(A2:A$1971,B2:B$1971=B2)))
Dynamic array formulas.

 

[isasa74]

On second thoughts if you could have a value like 1XS4.772.74 that is a parent my previous idea won't work.

What is the maximum amount of numbers you could have after the S.?

I don't have a limit for the number following the S. I believe is safe to consider S.9 the highest possible.

 

[Fluff]

Does Peter's suggestion work for you?

 

[isasa74]

I don't have a limit for the number following the S. I believe is safe to consider S.9 the highest possible.

yes it does!.... sorry for the late reply but I was on holidays, thanks everybody

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.