Need help with CONCATENATE multiple columns in table

[Jin_Tang]

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.

[#2]

@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.

[#3]

Hi Riny

Thanks for the help.
However for the example of having 2 different inputs for the same conditions, this doesn't seem to work.

Using the example i listed
x1 is the name of the person, x2 is the address, x3 is the telephone, x4 is the company, etc while condition a is the capital
and i have y1, y2, y3, which are detail of another group while they are still group under the same condition a.
then PQ doesnt really solve this problem, since they will group all of them up due to the condition A

Would there be any other ways, i.e. via VBA to concatenate the number of column based on the merged cell?

[#4]

@Jin_Tang Okay. With a few extra steps in PQ you can group it as desired. See attached.

[#5]

Hi Riny

I have been trying and I have an issue when merging the queries. How were you able to merge when the number of columns of "Custom" does not match the number of columns of "Index"?

This was the formula that you used
= Table.NestedJoin(#"Grouped Rows", {"Custom"}, #"Table1-copy", {"Index"}, "Table1-copy", JoinKind.LeftOuter)

I tried mimicking it on my data and the table display always shows null

[#6]

@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.

[#7]

Hi Riny

Thanks for your help! I understood where i went wrong.