Selective Transpose

I

isasa74

New Member
Joined
Aug 6, 2014
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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

1Blueparentafter the Formula -->1Blueparent4child16child2
4child1
6child2
1Redparent1Redparent5child1etc.
5child1
 
Sort by date Sort by votes
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Click to expand...
thanks Peter for looking into this you too.

I actually had put on hold your message thinking of going back to it later, I frankly thought it was a bot generated post, sorry about that.

I have Excel 365 (32bit).
I initially asked for a formula true, however the final list turned out to be a very long and I fear that thousands of Arrayed Formulas could make the sheet unstable or best case slow (already with the code by Special-K99 is inclined to crash after having ran it), also:


1) my laptop is not the brightest bulb on the tree
2) Excel is only in its 32bit version

that said I tried your formula and all I get is the Parent in A1 copied in B2 and Childs ignored.
Please also find below the XL2BB (only data).

Both Formula and Code will do for me if running smooth on my system, with the code I felt we were very close to the solution.


Book1 REA.xlsx
A
1MXteriXl No
21XX4.772.77
35FD4.771.71
45XX7.771.72.2
56XX7.771.77
66XX7.777.77.2
75XP4.771.77
85XX4.772.77.2
96FX7.777.77
105FC7.771.71
111XX4.772.71
125FD4.771.71
135TTC.777.71
145XX7.771.72.2
156XX7.771.72
166XX7.777.77.2
175XP4.771.77
185XX4.772.71.2
196FX7.777.77
205XX4.772.72.2
216FX7.777.77
225FC7.771.71
235FC7.771.71
241XX4.772.72
255FD4.771.71
265XX7.771.72.2
276XX7.771.71
286XX7.777.77.2
296XX7.771.74
305XP4.771.77
315XX4.772.72.2
326FX7.777.77
335FC7.771.71
341XX4.772.73
355FD4.771.71
365XX7.771.72.2
376XX7.771.73
386XX7.771.72
396XX7.777.77.2
405XP4.771.77
415XX4.772.73.3
426FX7.777.77
435FC7.771.71
441XX4.772.74
455FD4.771.71
465XX7.771.72.2
476XX7.771.72
486XX7.777.77.2
495XP4.771.77
505XX4.772.74.3
516FX7.777.77
525FC7.771.71
531XX4.713.77
545FD4.771.71
555XX7.771.72.2
566XX7.771.73
576XX7.777.77.2
585XP4.771.77
595XX4.713.77.3
606FX7.777.77
615FC7.771.71
621XX4.719.77
635FD4.771.71
645XX4.719.77.3
655XX4.719.72
665XX4.719.73
675XX7.719.71.2
685D77.181
695D77.182
706XX7.719.77.2
715S77.779
726FX7.777.77
736TTX.771.23
741XX4.719.72
755FD4.771.71
765XX4.719.77.2
775XX4.719.72
785XX4.719.73
795XX7.719.71.2
805D77.181
815D77.182
826XX7.719.77.2
835S77.779
846FX7.777.77
856TTX.771.23
861XX4.729.77
875FD4.771.71
885TTC.777.71
895XX7.771.72.2
906XX7.771.72
916XX7.777.77.2
925XP4.771.77
935XX4.729.77.2
946FX7.777.77
955FC7.771.71
961XX4.737.77
975FD4.771.71
985XX7.771.72.2
996XX7.771.74
1006XX7.771.73
1016XX7.771.72
1026XX7.777.77.2
1035XP4.771.77
1045XX4.737.77.3
1056FX7.777.77
1065FC7.771.71
1071XX4.754.77
1085FD4.771.71
1095XX7.771.72.2
1106XX7.771.77
1116XX7.777.77.2
1125XP4.771.77
1135XX4.754.77.2
1146FX7.777.77
1155FC7.771.71
1161XX4.754.71
1175FD4.771.71
1185XX7.771.72.2
1196XX7.771.77
1206XX7.777.77.2
1215XP4.771.77
1225XX4.754.71.2
1236FX7.777.77
1245FC7.771.71
1251XX4.754.73
1265FD4.771.71
1275XX7.771.72.2
1286XX7.771.71
1296XX7.777.77.2
1305XP4.771.77
1315XX4.754.73.2
1326FX7.777.77
1335FC7.771.71
1341XX4.754.74
1355FD4.771.71
1365XX7.771.72.2
1376XX7.771.77
1386XX7.777.77.2
1396TTB.771.36
1405XP4.771.77
1415XX4.754.74.3
1426FX7.777.77
1435FC7.771.71
1441XX4.754.75
1455FD4.771.71
1465XX7.771.72.2
1476XX7.771.72
1486XX7.777.77.2
1495XP4.771.77
1505XX4.754.75.3
1516FX7.777.77
1525FC7.771.71
1531XX4.176.77
1545FD4.771.71
1555XX7.771.72
1565D77.181
1575D77.182
1586ZX7.176.74
1596XX7.771.77
1605XX4.771.77
1615XX4.176.77.2
1626FX7.777.77
1635FC7.771.71
1641XX5.772.77
1655FD5.771.71
1665XX7.771.72.2
1676XX7.771.77
1686XX7.777.77.2
1695XP5.771.77
1705XX5.772.77.2
1716FX7.777.77
1725FC7.771.71
1731XX5.772.77R
1745FD5.771.71
1755XX7.771.72.2
1766XX7.771.77
1776XX7.777.77.2
1785XP5.771.77
1795XX5.772.77R
1806FX7.777.77
1815FC7.771.71
1826XX7.777.77.2
1835XP5.771.77
1845XX5.772.77R
1856FX7.777.77
1865FC7.771.71
1871XX5.772.71
1885FD5.771.71
1895TTC.777.71
1905XX7.771.72.2
1916XX7.771.72
1926XX7.777.77.2
1935XP5.771.77
1945XX5.772.71.2
1956FX7.777.77
1965FC7.771.71
1971XX5.772.72
1985FD5.771.71
1995XX7.771.72.2
2006XX7.771.71
2016XX7.771.74
2026XX7.777.77.2
2035XP5.771.77
2045XX5.772.72.2
2056FX7.777.77
2065FC7.771.71
2071XX5.772.73
2085FD5.771.71
2095XX7.771.72.2
2106XX7.771.73
2116XX7.771.72
2126XX7.777.77.2
2135XP5.771.77
2145XX5.772.73.3
2156FX7.777.77
2165FC7.771.71
Sheet2
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
update

I tried your formula without array and it worked like a charm for the first Parent occurrences however it started ,at some point, duplicating Childs and in facts creating long rows of duplicates, see table please

Book1 REA.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1151XX4.754.711XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.71
1165FD4.771.71 
1175XX7.771.72.2 
1186XX7.771.77 
1196XX7.777.77.2 
1205XP4.771.77 
1215XX4.754.71.2 
1226FX7.777.77 
1235FC7.771.71 
1241XX4.754.731XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.715FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.71.26FX7.777.775FC7.771.711XX4.754.73
1255FD4.771.71 
1265XX7.771.72.2 
1276XX7.771.71 
1286XX7.777.77.2 
1295XP4.771.77 
1305XX4.754.73.2 
1316FX7.777.77 
1325FC7.771.71 
1331XX4.754.741XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.715FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.71.26FX7.777.775FC7.771.711XX4.754.735FD4.771.715XX7.771.72.26XX7.771.716XX7.777.77.25XP4.771.775XX4.754.73.26FX7.777.775FC7.771.711XX4.754.74
1345FD4.771.71 
1355XX7.771.72.2 
1366XX7.771.77 
1376XX7.777.77.2 
1386TTB.771.36 
1395XP4.771.77 
1405XX4.754.74.3 
1416FX7.777.77 
1425FC7.771.71 
1431XX4.754.751XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.715FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.71.26FX7.777.775FC7.771.711XX4.754.735FD4.771.715XX7.771.72.26XX7.771.716XX7.777.77.25XP4.771.775XX4.754.73.26FX7.777.775FC7.771.711XX4.754.745FD4.771.71
1445FD4.771.71 
1455XX7.771.72.2 
1466XX7.771.72 
1476XX7.777.77.2 
1485XP4.771.77 
1495XX4.754.75.3 
1506FX7.777.77 
1515FC7.771.71 
1521XX4.176.771XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.715FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.71.26FX7.777.775FC7.771.711XX4.754.735FD4.771.715XX7.771.72.26XX7.771.716XX7.777.77.25XP4.771.775XX4.754.73.26FX7.777.775FC7.771.711XX4.754.745FD4.771.71
1535FD4.771.71 
1545XX7.771.72 
1555D77.181 
1565D77.182 
1576ZX7.176.74 
1586XX7.771.77 
1595XX4.771.77 
1605XX4.176.77.2 
1616FX7.777.77 
1625FC7.771.71 
1631XX5.772.771XX4.754.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.77.26FX7.777.775FC7.771.711XX4.754.715FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.754.71.26FX7.777.775FC7.771.711XX4.754.735FD4.771.715XX7.771.72.26XX7.771.716XX7.777.77.25XP4.771.775XX4.754.73.26FX7.777.775FC7.771.711XX4.754.745FD4.771.71
Sheet2
Cell Formulas
RangeFormula
B115:K115,B163:BG163,B152:AV152,B143:AM143,B133:AC133,B124:T124,B116:B123,B125:B132,B134:B142,B144:B151,B153:B162B115=IF(LEFT(A115,1)="1",TRANSPOSE(A115:INDEX(A$99:A115,IFNA(MATCH("1*",A$99:A116,0),COUNTA(A$99:A115)))),"")
Dynamic array formulas.
 
Upvote 0
If you go back to my original formula in B1 of post 10. Everywhere that I have row $100 (3 places in the formula), you need to change that to something at least as big as the last row of data before copying down.
See if that helps.

And yes, do not enter the formula as an array formula. Excel 365 treats it as an array formula by itself and spills across the rows as required.
 
Upvote 0
isasa74 said:
yes precisely, to be parent other that starting with 1* another condition is not ending in S or S* where the star after the S is a number.

I also have noticed 2 issues which will be clearer by looking at the screenshot

1) The Parent is copied in the next column too as it was a Child, consequentially I have it repeated twice, the original and a copy in the next column .
2) In case of long list of Childs (apparently when more than 10), Childs after the first 10 are copied one row above of the other Childs.

View attachment 44286
Click to expand...
A formula would be very complicated to resolve this.

Yes, the output on the child row is continuing from the column related to its parent above (row 1).
This is because the childrens first digit is NOT in numerical order. Look at the first digit reading down. 1,5,5,5,... 6, then back to 5.

More importantly this was NOT the data you provided.
I can only repeat what I said to someone on this forum earlier this week.

If you are providing example data you should provide data EXACTLY as it is, not something similar.
The number of times example data is provided, we waste time providing a solution, and the OP comes back with
"Actually the solution doesn't work because my data isn't like the example I provided".

I now don't have time to continue with this problem, however, had you provided the EXACT data at the beginning it would have been solved by now.
Good luck.
 
Upvote 0
Peter_SSs said:
=IF(LEFT(A1,1)="1",TRANSPOSE(A1:INDEX(A1:A$100,IFNA(MATCH("1*",A2:A$100,0),COUNTA(A1:A$100)))),"")
Click to expand...
it worked, thanks :)

Now I'm trying to create the other condition for the part number to be a parent which is must not end up with either "S" or "S.*" where the star stands for a number from 1 to 4.
(couldn't find any part ending with "S.5" or above so I think covering up till "S.4" would be enough.)

however I managed to nest conditions only for "S" and "S.1" and cannot find my way up to "S.4", see table below please.




Book1 REA.xlsx
ABCDEFGHIJK
11XX4.772.771XX4.772.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.772.77.26FX7.777.775FC7.771.71
25FD4.771.71 
35XX7.771.72.2 
46XX7.771.77 
56XX7.777.77.2 
65XP4.771.77 
75XX4.772.77.2 
86FX7.777.77 
95FC7.771.71 
101XX4.772.71S 
115FD4.771.71 
125TTC.777.71 
135XX7.771.72.2 
146XX7.771.72 
156XX7.777.77.2 
165XP4.771.77 
175XX4.772.71.2 
186FX7.777.77 
195FC7.771.71 
201XX4.772.72S.1 
215FD4.771.71 
225XX7.771.72.2 
236XX7.771.71 
246XX7.777.77.2 
256XX7.771.74 
265XP4.771.77 
275XX4.772.72.2 
286FX7.777.77 
295FC7.771.71 
301XX4.772.731XX4.772.735FD4.771.715XX7.771.72.26XX7.771.736XX7.771.726XX7.777.77.25XP4.771.775XX4.772.73.36FX7.777.775FC7.771.71
315FD4.771.71 
325XX7.771.72.2 
336XX7.771.73 
346XX7.771.72 
356XX7.777.77.2 
365XP4.771.77 
375XX4.772.73.3 
386FX7.777.77 
395FC7.771.71 
401XX4.772.741XX4.772.745FD4.771.715XX7.771.72.26XX7.771.726XX7.777.77.25XP4.771.775XX4.772.74.36FX7.777.775FC7.771.71
415FD4.771.71 
425XX7.771.72.2 
436XX7.771.72 
446XX7.777.77.2 
455XP4.771.77 
465XX4.772.74.3 
476FX7.777.77 
485FC7.771.71 
491XX4.713.771XX4.713.775FD4.771.715XX7.771.72.26XX7.771.736XX7.777.77.25XP4.771.775XX4.713.77.36FX7.777.775FC7.771.71
Sheet2
Cell Formulas
RangeFormula
B1:J1,B49:J49,B40:J40,B30:K30,B2:B29,B31:B39,B41:B48B1=IF(AND(LEFT(A1,1)="1",RIGHT(A1,1)<>"S",OR(RIGHT(A1,3)<>"S.1")),TRANSPOSE(A1:INDEX(A1:A$1999,IFNA(MATCH("1*",A2:A$1999,0),COUNTA(A1:A$1999)))),"")
Dynamic array formulas.
 
Upvote 0
How about
Excel Formula: 
=IF(AND(LEFT(A1,1)="1",RIGHT(A1,1)<>"S",MID(A1,LEN(A1)-2,1)<>"S"),TRANSPOSE(A1:INDEX(A1:A$1999,IFNA(MATCH("1*",A2:A$1999,0),COUNTA(A1:A$1999)))),"")
 
Upvote 0
isasa74 said:
Now I'm trying to create the other condition for the part number to be a parent which is must not end up with either "S" or "S.*" where the star stands for a number from 1 to 4.
(couldn't find any part ending with "S.5" or above so I think covering up till "S.4" would be enough.)
Click to expand...
If cell A10 in post #15 is not a parent (because it ends with S) then does that make it and/or cells A11:A19 also children of the parent in A1 and therefore should be included in the row 1 results, or does it just mean that rows 10:19 just get ignored and those values not included in any results?

Can you also confirm that if a "1" entry did turn up ending with, say, S.8 or S.0 that it should also be treated as a non-parent?
 
Last edited:
Upvote 0
Fluff said:
=IF(AND(LEFT(A1,1)="1",RIGHT(A1,1)<>"S",MID(A1,LEN(A1)-2,1)<>"S"),TRANSPOSE(A1:INDEX(A1:A$1999,IFNA(MATCH("1*",A2:A$1999,0),COUNTA(A1:A$1999)))),"")
Click to expand...
Hi Fluff, thanks for contributing the thread and your formula is surely well thought.
please read my reply to Peter :)


Peter_SSs said:
If cell A10 in post #15 is not a parent (because it ends with S) then does that make it and/or cells A11:A19 also children of the parent in A1 and therefore should be included in the row 1 results, or does it just mean that rows 10:19 just get ignored and those values not included in any results?

Can you also confirm that if a "1" entry did turn up ending with, say, S.8 or S.0 that it should also be treated as a non-parent?
Click to expand...
Hi Peter, sorry for the late reaction I was off line for a while.

"If cell A10 in post #15 is not a parent (because it ends with S) then does that make it and/or cells A11:A19 also children of the parent in A1 and therefore should be included in the row 1 results--> YES that is why my bit of formula and Fluff's aren't fixing it.

Can you also confirm that if a "1" entry did turn up ending with, say, S.8 or S.0 that it should also be treated as a non-parent?
YES, it is confirmed anything with S. or only S at the end is NOT to be considered a Parent.
 
Upvote 0
Ok, how about
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTU
11XX4.772.771XX4.772.775FD4.771.715XX7.771.72.26XX7.771.776XX7.777.77.25XP4.771.775XX4.772.77.26FX7.777.775FC7.771.711XX4.772.71S5FD4.771.715TTC.777.715XX7.771.72.26XX7.771.726XX7.777.77.25XP4.771.775XX4.772.71.26FX7.777.775FC7.771.71
25FD4.771.71 
35XX7.771.72.2 
46XX7.771.77 
56XX7.777.77.2 
65XP4.771.77 
75XX4.772.77.2 
86FX7.777.77 
95FC7.771.71 
101XX4.772.71S 
115FD4.771.71 
125TTC.777.71 
135XX7.771.72.2 
146XX7.771.72 
156XX7.777.77.2 
165XP4.771.77 
175XX4.772.71.2 
186FX7.777.77 
195FC7.771.71 
201XX4.772.721XX4.772.725FD4.771.715XX7.771.72.26XX7.771.716XX7.777.77.26XX7.771.745XP4.771.775XX4.772.72.26FX7.777.775FC7.771.791XX4.772.73S.55FD4.771.715XX7.771.72.26XX7.771.736XX7.771.726XX7.777.77.25XP4.771.775XX4.772.73.36FX7.777.775FC7.771.71
215FD4.771.71 
225XX7.771.72.2 
236XX7.771.71 
246XX7.777.77.2 
256XX7.771.74 
265XP4.771.77 
275XX4.772.72.2 
286FX7.777.77 
295FC7.771.79 
301XX4.772.73S.5 
315FD4.771.71 
325XX7.771.72.2 
336XX7.771.73 
346XX7.771.72 
356XX7.777.77.2 
365XP4.771.77 
375XX4.772.73.3 
386FX7.777.77 
395FC7.771.71 
401XX4.772.741XX4.772.745FD4.771.715XX7.771.72.26XX7.771.726XX7.777.77.25XP4.771.775XX4.772.74.36FX7.777.775FC7.771.71
415FD4.771.71 
425XX7.771.72.2 
436XX7.771.72 
446XX7.777.77.2 
455XP4.771.77 
465XX4.772.74.3 
476FX7.777.77 
485FC7.771.71 
491XX4.713.771XX4.713.77
Master
Cell Formulas
RangeFormula
B1:T1,B40:J40,B20:U20,B2:B19,B21:B39,B41:B49B1=IF(AND(LEFT(A1,1)="1",RIGHT(A1,1)<>"S",MID(A1,LEN(A1)-2,1)<>"S"),TRANSPOSE(A1:INDEX(A1:A$1999,IFERROR(AGGREGATE(15,6,(ROW(A2:A$1999)-ROW(A2)+1)/(LEFT(A2:A$1999,1)="1")/(NOT(ISNUMBER(SEARCH("s",A2:A$1999)))),1),COUNTA(A1:A$1999)))),"")
Dynamic array formulas.
 
Upvote 0
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.?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Multi-level Bill Of Materials explosion
3 4 5
Replies
40
Views
4K
Andy Howcroft
A
J
  • Question
Hair and eye colour chooser formula help
Replies
2
Views
130
jwoww
J
J
  • Question
Populate column with the parent part number using an Excel formula.
2
Replies
16
Views
775
Alex Blakenburg
A
S
  • Question
Organization Chart Formula
Replies
3
Views
131
ShogunStealth
S
M
  • Question
Sorting part numbers based on ascending order and also putting "a" part numbers at top
Replies
4
Views
106
myall_blues
myall_blues

Forum statistics

Threads
1,215,523
Messages
6,125,319
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top