logic - Excel Problem, combining columns in a specific way

Question

I have a list of Materials composed of Sub components listed in 2 separate columns.

• One column is composed of Materials A, B and C.
• Another column shows the sub components of each of the ingredients.
• A is made up of sub ingredients 1-6. B is made up of 7-13. And C is made up of 14-21(The numbers of ingredients/sub-ingredients does not matter as this can vary).

Importantly the two columns which contain the relevant data are not adjacent to one another (See attached image).

Is it possible to have a new column such that the data is automatically combines the relevant columns to read: A,1,2,3,4,5,6,B,7,8,9,10,11,12,13,C,14,15,16,17,18,19,20,21, whilst omitting the columns in between.

In the attached image I have shown the desired output to better explain visually.

Progress so far has been achieved by help from @Gary's Student

• By having the columns A B adjacent to each other the desired output can be calculated with the following formula (with the column titles removed): =FILTERXML("("&SUBSTITUTE(TEXTJOIN(",",TRUE,A:A:B:B),",",")

  (")&")","//b")

• The problem is that the standardised Excel spreadsheet that I am working on does not have these columns next to one another but instead the relevant data is in columns A and E respectively.

• Attempts to omit Columns B:D have been trailed using: =FILTERXML("("&SUBSTITUTE(TEXTJOIN(",",TRUE,A:A,E:E) ,",",")

  (")&")","//b") and although this does omit irrelevant information, unfortunately this also changes the desired output to be A,B,C,1,2,3...

The attached image shows the desired output that I am trying to achieve from the material column and the sub component column whilst omitting the additional info.

Any further thoughts would be appreciated Thank you.

question from:https://stackoverflow.com/questions/66050377/excel-problem-combining-columns-in-a-specific-way

Answer 1

With Excel 365 and data in columns A and B, in C1 enter:

=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,A1:B16),",","</b><b>")&"</b></a>","//b")

EDIT#1:

If we want to include data from columns A and E, but exclude columns B, C, and D, then replace:

SUBSTITUTE(TEXTJOIN(",",TRUE,A1:B16)

with something like:

TEXTJOIN(",",TRUE,A:A,E:E)    

EDIT#2:

Here is the correct equation:

=LET(x,INDEX(A$2:E$22,ROUNDUP(SEQUENCE(42)/2,0),IF(MOD(SEQUENCE(42),2)=0,5,1)),FILTER(x,x<>""))