Excel: Expand Table by Repeating Certain Rows, Duplicating Certain Cells

Question

Alright, this is very complicated, but here is the general idea.

I currently have a table with unique values in the first column, related value(s) in the second column, and a third column with a number indicating how many values are in the second column. The values in the second column are combined together with a vertical pipe ( | ).

This is limited by my unfortunate lack of knowledge of possible colors of different fruit, but this is basically how my table looks. Any given row could have a single value in column B, or multiple (no limit), separated by that vertical pipe. My goal is to have a spill formula in a single cell that processes this table and renders it in a two-column table like this:

I've used lots of different combinations of REPT, TEXTJOIN, TEXTSPLIT, HSTACK, and VSTACK formulas, but I haven't been able to figure this out. If this needs to be two separate spill formulas in the two columns that's fine, but my goal was initially just one spill column so as to make sure all the fruits and colors matched up directly.

Hope that makes sense, thank you in advance!

Answer 1

Hi @Josh-8092,

To avoid using the OFFSET function which is an volatile function I think it is better for you to use the following formula.

In this formula the third column (Number) is not used as such is not necessary.

=VSTACK(A1:B1, TEXTSPLIT(TEXTJOIN(";",, TOCOL(A2:A5 & "," & TEXTSPLIT(TEXTJOIN(";",, B2:B5), "|", ";"), 3)), ",", ";"))

Or you can use Power Query. Below is the M code. Name the table as Data.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Colour", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Color.1", "Color.2", "Color.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Fruit", "Number"}, "Attribute", "Colour"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Number", "Attribute"})
in
    #"Removed Columns"

Hope this helps.

IlirU

Answer 2

Hi,

In cell E1, enter this formula

=REDUCE({"Fruit","Colour"},A2:A5,LAMBDA(s,c,VSTACK(s,IF({1,0},c,TEXTSPLIT(OFFSET(c,,1),,"|")))))

Hope this helps.

Answer 3

Hi,

You may solve this very simply with Power Query. Just split the second column by rows with the | delimiter.

Answer 4

Hi @Josh-8092,

According to your concern, may I confirm whether the number in Number column is equal to the amount of different color in the Color column?

If that's the case, you can try this formula which just need to use the Fruit and Color columns to return the outcome as you expected:

=LET(f,A2:A5,c,B2:B5,DROP(REDUCE("",SEQUENCE(ROWS(f)),LAMBDA(acc,i,VSTACK(acc,HSTACK(IF(SEQUENCE(ROWS(TEXTSPLIT(INDEX(c,i),,"|"))),INDEX(f,i)),TEXTSPLIT(INDEX(c,i),,"|"))))),1))

Hope my sharing may be helpful with your concern. If you have any questions or need further assistance, please feel free to share them in the comments on this post so I can continue to support you.    

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread