Take a series of rows and break each row into multiple rows

[NotSoFastEddie]

Once again, I look to the Excel Gurus to assist.  I have been stewing about this most of the day.  I have rows of data and I need to take each row and some of its cells and produce a single row for each of the key cells.  The heading is actually a key in a lookup table for XLOOKUP. There are a number of static fields as well.  This sample shows the original output from a FILTER formula in the first 4 rows.  Below that is the desired state. 

[#2]

@NotSoFastEddie To unpivot the data in range A1:H4, try the following:

=LET(
    tbl, A1:H4,
    data, DROP(tbl, 1, 1),
    code, DROP(TAKE(tbl,, 1), 1),
    attr, DROP(TAKE(tbl, 1),, 1),
    HSTACK(
        TOCOL(IF(SEQUENCE(, COLUMNS(data)), code)),
        TOCOL(IF(SEQUENCE(ROWS(data)), attr)),
        TOCOL(data)
    )
)

I'm not entirely sure what you need returned for the other 4 columns (value, Unit, valuePosition, language), but if they all need to be filled with "n/a" and "EN_CA (static)", the EXPAND function could work. See attached...

[#3]

djclements

Thank you very much for your response. It works well, except, the classificationAttributeCode is a lookup in the table to the right using the column heading (e.g. Accessories List) and returning PS_ACCESSORY. What I did is substitute the last entry in your formula for a simple string "test msg") and the your formula continued to work and placed "test msg" into the cell below Classification Attribute. I then substituted an XLOOKUP function for that last entry in your formula, but ran into a some weird behaviour.

=LET( tbl, A1:H4, data, DROP(tbl, 1, 1), code, DROP(TAKE(tbl,, 1), 1), attr, DROP(TAKE(tbl, 1),, 1),
HSTACK(TOCOL(IF(SEQUENCE(, COLUMNS(data)), code)), TOCOL(IF(SEQUENCE(ROWS(data)), attr)),XLOOKUP(attr,Table1[Attribute name],Table1[[Classification Attribute ]]) )).

It kind of worked, but it also looks like it did the XLOOKUP for every heading in the row. It looks like attr will in fact have "Accessories List" as the value for the first row. 

As an aside, is there a way to debug or at least view what values are in data, code and attr?

[#4]

@djclements

I tried using your EXPAND formula putting the XLOOKUP there. I had some luck, but it only looked up for row b8.  If I use B8#, I get a #REF error

=EXPAND(XLOOKUP(B8,Table1[Attribute name],Table1[[Classification Attribute ]]), ROWS(A8#), COLUMNS(A8#), "n/a")

[#5]

@NotSoFastEddie Perhaps I misunderstood the question. I thought you wanted the data from each row in range A2:H4 returned on multiple rows, but it seems that you simply want the data from the lookup table repeated for each PCODE. If so, try the following instead:

=LET(
    range1, A2:A4,
    range2, Table1[[Attribute name]:[Classification Attribute ]],
    a, SEQUENCE(ROWS(range1)),
    b, SEQUENCE(, ROWS(range2)),
    results, HSTACK(TOCOL(IF(b, range1)), CHOOSEROWS(range2, TOCOL(IF(a, b)))),
    EXPAND(EXPAND(results,, 6, "n/a"),, 7, "EN_CA (static)")
)

[#6]

@djclements 

DJ, I apologize for dragging this on. I had some issues on my side with the instructions I received. In the newly attached spreadsheet, I have addressed those and provided explanations. There is a second worksheet, called DesiredResults that hopefully explains more fully what I am looking for.

I have tried to play around with the formula, but I am struggling to understand, exactly what it is doing.  I know this is a lot to ask, but would it be possible to get a Teams Meeting to review the actual formula, for my understanding, so I can be more independent.  If not, any help is appreciated.

[#7 · Accepted solution]

@NotSoFastEddie Thank you for providing the desired results and additional explanation. Try the newly attached file... it's basically just a combination of the techniques used in the first two versions, with an additional XLOOKUP.

The IF / SEQUENCE method is used to repeat data the required number of times, TOCOL sends each array to a single column, and HSTACK joins each of the columns together horizontally to generate a single output array. To get a better understanding of how these methods work, it's best to start by experimenting with basic examples. For more information, please see: https://techcommunity.microsoft.com/t5/excel/copy-array-n-times-with-excel-formula... 

If further explanation is needed, please feel free to send me a private message (just click on my profile name or icon, then click the Message button). Cheers!

[#8]

Thanks again for your continued support. It looks beautiful and I can't thank you enough. I did make lots of headway understanding the formula to the point where I got to the unit column. I like this solution even more .

[#9]

@djclements 

Just following up.  The formula transferred well into the "real" spreadsheet.  I made a decision which is not ideal as I was having an issue with trying to include one cell value based on a condition and if it was false, include another cell value.  I used the CHOOSECOLS function.  I received some weird results and wondered if you had any thoughts.  I am going to look at that link you provided as basically, due to the issue above, I created six tables, for PCODE with lang = en_ca, one with BUNDLE IDENTIFIER with lang = en_ca and duplicated for lang = en and for lang = fr.  Not very efficient, but if I can solve the getting the PCODE or the BUNDLE IDENTIFIER in the same column, different rows, then I can reduce the number of table.  Thoughts  

[#10]

@NotSoFastEddie Your use of CHOOSECOLS was incorrect, nesting the IF statement within the col_num1 argument. Use the IF function on its own to merge columns 2 and 3, then stack the results together with the remaining columns using HSTACK. In the attached file, I've broken it down into steps so you can easily see the process of how one might go about building a single cell dynamic array formula...

[#11]

@djclements 

I have been working on this over the weekend.  My full spreadsheet has some 200 columns and I am allowing for 1600 rows.  I have gotten everything in order.  If you recall, you helped me with the next step which was performing lookups in another table.  The language was en_CA was hardcoded.  Now that I have the language in place, I want to replace "en_CA" with the actual language in the source table.  I have tried a few things, but I have to say, I am confused with what the last TOCOL is doing on the backend of the formula.
=LET(
tbl,Current!$A1:$AV1600,
data, DROP( tbl, 1, 2),
keys, DROP(TAKE(tbl,, 2), 1),
attr, DROP(TAKE(tbl, 1),, 2),
code, XLOOKUP(attr, Table_Specifications[Attribute name], Table_Specifications[[Classification Attribute ]], "not found"),
type, XLOOKUP(attr, Table_Specifications[Attribute name], Table_Specifications[Feature Type], "not found"),
lang, TAKE(DROP(tbl,1),,1), <-- I added this
a, SEQUENCE(ROWS(data)),
b, SEQUENCE(, COLUMNS(data)),
EXPAND(EXPAND(HSTACK(
CHOOSEROWS(keys, TOCOL(IF(b, a))),
TOCOL(IF(a, code)),
TOCOL(data)&","&TOCOL(IF(a, type))),, 6, ""),, 7,"en_CA")
)

The feeder table looks like:

=LET(
table, PI_Package!$A$1:$GR$1600,
head,TAKE(table,1),
data,FILTER(DROP(table,6),((PI_Package!$A7:$A1600="PCODE")+(PI_Package!$A7:$A1600="BUNDLE IDENTIFIER")),""),
lang,{"en_ca";"en";"fr"},
specs_heading1,TAKE(DROP(head,,26),,14),
specs_heading2,TAKE(DROP(head,,45),,9),
specs_heading3,TAKE(DROP(head,,177),,8),
specs_headingfr,TAKE(DROP(head,,185),,15),
specs_data1,TAKE(DROP(data,,26),,14),
specs_data2,TAKE(DROP(data,,45),,9),
specs_data3,TAKE(DROP(data,,177),,8),
specs_datafr,TAKE(DROP(data,,185),,15),
a,SEQUENCE(ROWS(lang)),
b,SEQUENCE(,ROWS(data)),
code,IF(CHOOSECOLS(data,1)="PCODE",CHOOSECOLS(data,4),CHOOSECOLS(data,5)),
VSTACK(
HSTACK("#code",specs_heading1,specs_heading2,specs_heading3,specs_headingfr,"Language"),
HSTACK(
CHOOSEROWS(HSTACK(code,specs_data1,specs_data2,specs_data3,specs_datafr),TOCOL(IF(a,b))),
TOCOL(IF(b,lang))
)
)
)

[#12]

@NotSoFastEddie The variables you've highlighted belong to the two EXPAND functions:

EXPAND(EXPAND(HSTACK(...),, 6, ""),, 7, "en_CA")

The HSTACK function is retuning an array 4 columns wide. The inner EXPAND function (highlighted in blue above) expands the array to 6 columns, filling the 2 new columns with "". The outer EXPAND function adds a 7th column filled with "en_CA".

Remove the outer EXPAND function and add whatever step is necessary to pull in the language from your source table (XLOOKUP or otherwise... I wouldn't know for sure without seeing a sample spreadsheet). I hope that helps.

[#13]

@djclements again any help is so appreciated.  In the attached, I removed the outer EXPAND and tried to add in the column with the lang field that I defined. I get a VALUE error. Tried a few different ways that I could think of, but no luck.

Although the lang variable seems to work outside of the LET, I must be trying to reference it incorrectly.

I have been playing with the formula and in the last line ,,7,Current!AV2 provides a response which happens to be en_ca.  I have tried various ways to get an array for the language, but each time I do that I get a #VALUE, which tells me I am out of bounds and it seems to want just a constant or a single cells content.

=LET(
          tbl,Current!$A1:$AV$1600,
          data, DROP( tbl, 1, 2),
           keys, DROP(TAKE(tbl,, 2), 1),
           attr, DROP(TAKE(tbl, 1),, 2),
          code, XLOOKUP(attr, Table_Specifications[Attribute name],       Table_Specifications[[Classification Attribute ]], "not found"),
           type, XLOOKUP(attr, Table_Specifications[Attribute name], Table_Specifications[Feature Type], "not found"),
           lang,TAKE(DROP(tbl,1),,1),
a, SEQUENCE(ROWS(data)),
b, SEQUENCE(, COLUMNS(data)),
EXPAND(
EXPAND(
HSTACK(
CHOOSEROWS(keys, TOCOL(IF(b,a))),
TOCOL(IF(a,code)),
TOCOL(data)&","&TOCOL(IF(a,type))
),,6,""
),,7,Current!AV2
)
)

[#14]

@NotSoFastEddie DROP the Language column from the data & attr variables and add it to the keys variable. Then use CHOOSECOLS on the final results to rearrange the columns as desired. See attached...

[#15]

@djclements THANKS SO MUCH!  I was certainly struggling.  I appreciate your continued interest and response.