Forum Discussion
Take a series of rows and break each row into multiple rows
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 e...
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!
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))
)
)
)
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.
NotSoFastEddieEvery scenario presented has been slightly different than the last. In this case, use the REDUCE function to iterate through the static lang array and return a different set of results for "fr_CA"...
djclements Not sure if my earlier reply ever got saved and sent as I don't see it in my history.
The data entry sheet PI_Package is the basis for linkProductFeatures_Base which contains PCODE and BUNDLE IDENTIFIER rows and attaches the language based off the array lang. PI_PACKAGE has both english and french columns for assembly specifications. it turns out, that I needed to add dimension type (ft,in,lb, cm/mm/..) for both english and french. Background ->In PI_Package, I used a custom format #0.00 " in" (/ft/lb). This works great on the data entry portion. When the cells are copied to linkProductFeatures_Base, the formatting is lost which is why I added lines in the LET statement to convert to text. This stops the issue and carries the formatting on to linkproductfeatures (ends up being exported to a CSV).
Back to the ask, I thought I would use the same approach that you provided for code with the idea that if the language on the row is fr_CA, then use the french columns, otherwise use the english columns. You can see in the output of linkProductFeatrues_Base that it is iterating through the language, but the result is not desirable. Sheet 3 provided me some insight. I am not sure how to correct this. I know the magic is in the TOCOL(IF(a,b))),TOCOL(IF(b,lang).
=LET(
table, PI_Package!$A$1:$HZ$1600,
head,TAKE(table,1),
data,FILTER(DROP(table,6),
(PI_Package!$C$7:$C$1600="PCODE")*(PI_Package!$C$7:$C$1600="PTY")+(PI_Package!$C$7:$C$1600="BOTH")+((PI_Package!$A$7:$A$1600="BUNDLE IDENTIFIER")*(PI_Package!$C$7:$C$1600="PTY")+(PI_Package!$C$7:$C$1600="BOTH")),"NO RECORDS"),
lang,{"en_CA";"en";"fr_CA"},
specs_heading0,TAKE(DROP(head,,176),,1),specs_heading1,TAKE(DROP(head,,26),,14),specs_heading2,TAKE(DROP(head,,44),,1),
specs_heading3,TAKE(DROP(head,,49),,5),specs_heading4,TAKE(DROP(head,,183),,8),specs_headingfr,TAKE(DROP(head,,191),,15),
specs_data0,TAKE(DROP(data,,176),,1),specs_data1,TAKE(DROP(data,,26),,14),specs_data2,TAKE(DROP(data,,44),,1),
specs_metricdata,TAKE(DROP(data,,49),,5),specs_impdata,TAKE(DROP(data,,183),,8),
specs_datafr,TAKE(DROP(data,,191),,15),specs_impdatafr,TAKE(DROP(data,,211),,8),specs_metricdatafr,TAKE(DROP(data,,206),,5),
a,SEQUENCE(ROWS(lang)), b,SEQUENCE(,ROWS(data)),
code,IF(CHOOSECOLS(data,1)="PCODE",CHOOSECOLS(data,4),CHOOSECOLS(data,5)),
assht_cm,IF(CHOOSECOLS(specs_metricdata,1)<>"",TEXT(CHOOSECOLS(specs_metricdata,1),"#0.00")&" cm"),
assln_cm,IF(CHOOSECOLS(specs_metricdata,2)<>"",TEXT(CHOOSECOLS(specs_metricdata,2),"#0.00")&" cm"),
asswd_cm,IF(CHOOSECOLS(specs_metricdata,3)<>"",TEXT(CHOOSECOLS(specs_metricdata,3),"#0.00")&" cm"),
asswt_kg,IF(CHOOSECOLS(specs_metricdata,4)<>"",TEXT(CHOOSECOLS(specs_metricdata,4),"#0.00")&" kg"),
assdi_cm,IF(CHOOSECOLS(specs_metricdata,5)<>"",TEXT(CHOOSECOLS(specs_metricdata,5),"#0.00")&" cm"),
assht_ft,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,1),CHOOSECOLS(specs_impdata,1)),
assht_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,2),CHOOSECOLS(specs_impdata,2)),
assln_ft,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,3),CHOOSECOLS(specs_impdata,3)),
assln_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,4),CHOOSECOLS(specs_impdata,4)),
asswd_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,5),CHOOSECOLS(specs_impdata,5)),
asswd_mm,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,6),CHOOSECOLS(specs_impdata,6)),
asswt_lb,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,7),CHOOSECOLS(specs_impdata,7)),
assdi_in,IF(lang="fr_CA",CHOOSECOLS(specs_impdatafr,8),CHOOSECOLS(specs_impdata,8)),
VSTACK(
HSTACK("#code",specs_heading0,specs_heading1,specs_heading2,specs_heading3,specs_heading4,specs_headingfr,"Language"),
HSTACK(CHOOSEROWS(HSTACK(code,specs_data0,specs_data1,specs_data2,assht_cm,assln_cm,asswd_cm,asswt_kg,assdi_cm,assht_ft,assht_in,assln_ft, assln_in,asswd_in,asswd_mm,asswt_lb,assdi_in,specs_datafr),
TOCOL(IF(a,b))),TOCOL(IF(b,lang))
)
)
)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...
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
)
)
