Forum Discussion
Splitting a table taking one portion and putting it under the other via LET - file format changed
I am hoping someone can help. I am running out of time and well out of skill.
The attached spreadsheet is a subset of a much larger one. Essentially, the LET statement is very cool (DJClement) and I have modified it to meet the distributed pattern of columns I need to gather.
The current LET formula produces a rows of en_CA, en and fr_CA where the information is duplicated for each of the locale types in as one wide table. I need to get the French columns underneath the equivalent English ones.
The list takes from a source worksheet and generates the output in a new worksheet.
I have tried a few different ways, but have only had limited success.
This is a pic of the current output and the desired state below).
PeterBartholomew1 - thanks for taking time to look at. I am good - finally - so many people happy now - but me more than any of them. I need to go back to my solution architect role.....
- I can not open your attached file because it is a strict open xml.
peiyezhu Thanks for responding, I replaced the file in the original post and added it to this reply.
NotSoFastEddie Not sure if you saw my reply to your last inquiry found here https://techcommunity.microsoft.com/t5/excel/take-a-series-of-rows-and-break-each-row-into-multiple-rows/m-p/4147130#M229877 where I used the REDUCE function to iterate through the 3 languages, as opposed to the TOCOL / IF / SEQUENCE method, in order to return different columns for "fr_CA".
If you didn't like that method, please try the newly attached file below, where only the 2 English languages {"en_CA";"en"} are used to repeat the English columns, and the French columns are then stacked below it...
- Sorry DJ, I opened the file splitthedata_needhelp2.xlsx and it is the same as what I posted. I don't see your fix. Could you please check and re-send.
NotSoFastEddie Check it again and look closely. I just redownloaded both files (your original and my version) and they are not the same. I made the changes to the formula directly in cell A2, so the "current state" results match the "desired state" that you hard-keyed below. Scroll to the right and you'll see the results only go to column U in my file, whereas the original file goes all the way to column AM. Compare the files side-by-side and the differences will be obvious.
You appear to be sorted but, just for the record, I appeared to be making some progress by searching the header row for English and French data fields.
= LET( enColIdx, XMATCH(desiredHdr, header), frenchIdx, XMATCH(desiredHdr & " FR", header), frColIdx, IFERROR(frenchIdx, enColIdx), rowIdx, SEQUENCE(ROWS(body)), stacked, VSTACK(INDEX(body,rowIdx,enColIdx), INDEX(body,rowIdx,frColIdx)), FILTER(stacked, TAKE(stacked,,1)<>"") )PeterBartholomew1 - thanks for taking time to look at. I am good - finally - so many people happy now - but me more than any of them. I need to go back to my solution architect role.....
