Merge column values in one row based on common key

**ashokkumar2412** · 03-28-2014, 09:00 AM

We need a vba script to get the desired output for data in the given input format

Key -----Val1----- Val2----- Val3
Key1-----a1
Key1------------- b1
Key1------------------------c1
Key2-----a2
Key2------------- b2
Key2------------------------c2
Key3-----a3
Key3------------- b3
Key3------------------------c3

Desired Ouput:
---------------
Key Val1 Val2 Val3
Key1 a1 b1 c1
Key2 a2 b2 c2
Key3 a3 b3 c3

The same needs to be applied for data around 50 sheets in the same excel. Also, the number of columns for Val1, Val2, etc goes beyond 20 in many of the sheets. Kindly post your suggestion to automate the same.

Thanks,

**Bernie Deitrick** · 03-28-2014, 10:30 AM

If you have only one value for a key in any given column, select the entire table (including headers) and run this macro:

Please Login or Register  to view this content.

**ashokkumar2412** · 03-31-2014, 04:34 AM

Thanks a lot for your response. We tried executing your macro for our data. Is working fine for one set of values (Key1). It would be of great help, if the program could be generalised for all the key values (Key1, Key2 ...etc). We have a huge data, more than 100 sheets of around (2000 KB)each. The execution time is very high. Kindly advise on the same.

**Bernie Deitrick** · 03-31-2014, 08:05 AM

It worked on your example data set - post a shortened data set on which it does not work the way you want. Is it just that you have multiple sheets, or is it not working correctly on the first sheet?

**ashokkumar2412** · 03-31-2014, 08:55 AM

Originally Posted by Bernie Deitrick

It worked on your example data set - post a shortened data set on which it does not work the way you want. Is it just that you have multiple sheets, or is it not working correctly on the first sheet?

Sorry for the confusion. Actually there is one more column in the key part, Column A(Array Index) i.e., (Array Index in ColA + Key in ColB) forms the unique key, data values start from column C onwards. We missed ColA details in our sample data. Could you please help us providing the updated script to match this scenario ?

Array Key -----Val1----- Val2----- Val3
1 Key1-----a1
1 Key1------------- b1
1 Key1------------------------c1
2 Key2-----a2
2 Key2------------- b2
2 Key2------------------------c2
1 Key3-----a3
1 Key3------------- b3
1 Key3------------------------c3

Desired Ouput:
---------------
Array Key Val1 Val2 Val3
1 Key1 a1 b1 c1
2 Key2 a2 b2 c2
1 Key3 a3 b3 c3

**Bernie Deitrick** · 03-31-2014, 09:52 AM

Try this version:

Please Login or Register  to view this content.

And to do it on every sheet:

Please Login or Register  to view this content.

Merge column values in one row based on common key

LinkBack

Thread Tools

Rate This Thread

Display

Merge column values in one row based on common key

Re: Merge column values in one row based on common key

Re: Merge column values in one row based on common key

Re: Merge column values in one row based on common key

Re: Merge column values in one row based on common key

Re: Merge column values in one row based on common key

Thread Information

Users Browsing this Thread

Similar Threads

Merge values from column in one cell, based on duplicates in another column

[SOLVED] Merge cells in a table based on number of common values

[SOLVED] Excel - Merge rows with common values and concatenate the differences in one column

Excel 2007 : merge 2 sheets that have one common column

Merge 2 sheets with common ID column

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions