Concatenate columns

[PMSNAGU]

Hello all,

I would like to have a code which does a concatenate operation of 1st column with the rest columns which has data and highlight it in a different color.

Eg:

Lets say i have data in 5 columns. I would like to have 1st and 2nd column's data concatenated in 6th column, 1st and 3rd concatenation in 7th column, 1st and 4th column data in 8th column and so on till end. All these concatenated data should be highlighted in any color.

Note: No.of columns may differ each time. So based on it macro need to concat the existing columns.

Thanks for your help.

Regards

[alansidman]

The easiest way to do this is with Power Query also known as Get and Transform. Upload your Table to PQ, identify which columns to concatenate and click on the merge icon. Load and Close back to your workbook. Very quick and very easy. This is available to you in Excel 2016 on the Data Tab.

[AliGW]

I'm not sure how this will be achieved in Power Query based on the description given that involves cell highlighting. In fact, I am not really entirely sure what the data is meant to look like! I really think we need to see a workbook before advising any further.

[daboho]

maybe like this ,example in a1:c1 have any value and variant colorindex ,you can concatenate by different color in col a1:c1
using udf
=ConcaT(A1:C1)

Please Login or Register  to view this content.

[jindon]

Note: No.of columns may differ each time. So based on it macro need to concat the existing columns.

How?
Row by row?


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[PMSNAGU]

Hello All,

Thanks for the reply. As per request, I have attached the sample excel of my desired output.

Pls note that No.of columns may differ each time. So based on it macro need to concat the existing columns.

Thanks.

[PMSNAGU]

I'm not sure how this will be achieved in Power Query based on the description given that involves cell highlighting. In fact, I am not really entirely sure what the data is meant to look like! I really think we need to see a workbook before advising any further.

I have attached the workbook for your reference. Pls advice.

Note that, the column data changes every time. I have provided 3 columns in the sample, but i may get more than or lesser than 3 columns anytime.

[jindon]

Do you want it like this?

Please Login or Register  to view this content.

[alansidman]

Using Power Query, here is the M Code for the merger

Please Login or Register  to view this content.

[PMSNAGU]

Awesome! Thanks Jindon for your support - it worked.

[PMSNAGU]

Thanks alansidman