Apr 28 2021 12:40 AM - edited Apr 28 2021 12:44 AM
Hi All
Would like to seek your assistance in finding any easier method/ less tedious method in concatenating my data to obtain what I want? Currently, my approach is to concatenate manually, but there is too much data.
Thank you.
Apr 28 2021 01:45 AM
@Jin_Tang Perhaps Power Query (PQ) is what you need. Are you familiar with that?
Connect to your data. The merged cells in the condition column will be un-merged by PQ, by the way. Then do some clean-up steps and create the desired output. Note, however, that the output is grouped, based on the condition into one cell separating the elements by a line-feed. Wrap text applied to column A in the "output" sheet to see the impact of the line feeds.
Apr 28 2021 02:16 AM
Apr 28 2021 03:11 AM
@Jin_Tang Okay. With a few extra steps in PQ you can group it as desired. See attached.
Apr 28 2021 08:10 PM
Apr 28 2021 10:37 PM
@Jin_Tang If you go through the applied steps, one-by-one you should be able to follow and replicate it in your own file.
First, I add an index column, Then I add a Custom column that checks if there is a value in the "Condition" column. If so, then return the index value. If not then null. Now I have a column with the index number on each row that has a "Condition". Next step is to fill down this Custom column. This "labels" all rows that belong together with the the same index number. Thus, creating rows that can be grouped by 0,4,11,15 and 20. After cleaning-up a bit, the rows are grouped and the data gets concatenated into one cell using Text.Combine and a line-feed "#(lf)".
I took a copy of the first query up to the "Added Index" step. Now I can merge the Custom column in the first query with the Index column in the copied query. So, Custom 0 matches Index 0 which will return condition "a". Custom 4 matches Index 4 which will return condition "b" and so on.
If you can't get it to work, perhaps better if you upload the file containing your attempts to replicate this. That would make it easier to diagnose.
Apr 28 2021 11:59 PM