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

**Rawland_Hustle** · 01-15-2014, 08:47 AM

Hi!

I know, the title is very hard to undestand. What I'd like to do is turn this:

A	B
London	Blue
London	Green
New York	Yellow
New York	White
New York	Blue
Milano	Blue

Into this:

A	B
London	Blue, Green
New York	Yellow, White, Blue
Milano	Blue

Or this:

A	B	C	D
London	Blue	Green
New York	Yellow	White	Blue
Milano	Blue

As you can see, values in column A should only occure once. For all rows that each value occured, the values in column B should somehow be merged in the same row. Just look at the tables if my explanation doesn't make sense

Can I do this with a formula or do I need a VBA script? Obviously there are lots of more rows and columns in the real worksheet.

Thanks!

**:) Sixthsense :)** · 01-15-2014, 09:07 AM

Assume that your data is from A1:B6

In D2 cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX($A$1:$A$6,MATCH(0,COUNTIF($D$1:$D1,$A$1:$A$6),0)),"")

Drag it down...

In E2 cell - Array Formula - Requires CTRL+SHIFT+ENTER

=IFERROR(INDEX($B$1:$B$6,SMALL(IF($A$1:$A$6=$D2,ROW($A$1:$A$6)-MIN(ROW($A$1:$A$6))+1),COLUMN(A:A))),"")

Drag it down and right....

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

**Rawland_Hustle** · 01-15-2014, 10:15 AM

Thanks for the quick reply!

I should of course have mentioned this in my initial post: in my worksheet, all the columns have duplicates but the formula should only be used for column C and G. It actually looks like this (I've left the other columns blank here but they do have values):

A	B	C	D	E	F	G
		London				Blue
		London				Green
		New York				Yellow
		New York				White
		New York				Blue
		Milano				Blue

I'm going to apply this formula on many worksheets so I'd like to understand which values in the formula that I should change, depending on how many rows the particular worksheet has. The columns are always the same, the data starts in A2 and ends in G-something

**Rawland_Hustle** · 01-16-2014, 05:52 AM

To clarify: There are values in the other columns which are the same for every duplicate in column C. Those values should be should be kept on one row.

Is this possible?

**:) Sixthsense :)** · 01-16-2014, 06:32 AM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**Rawland_Hustle** · 01-16-2014, 10:10 AM

I have attached a sample workbook. The result in sheet AFTER (1) is the one I'd prefer but AFTER (2) works too

Se sheet EXPLAINED for explanation of what the formula actually should do. Bare in mind that the actual workbooks contains up to 6000-7000 rows. It varies from workbook to workbook.

I'd be very happy if someone could solve this! :D

**Rawland_Hustle** · 01-20-2014, 11:30 AM

Was it okay?

**JOHN H. DAVIS** · 01-20-2014, 11:52 AM

Originally Posted by Rawland_Hustle

Hi!

I know, the title is very hard to undestand. What I'd like to do is turn this:

A	B
London	Blue
London	Green
New York	Yellow
New York	White
New York	Blue
Milano	Blue

Into this:

A	B
London	Blue, Green
New York	Yellow, White, Blue
Milano	Blue

Or this:

A	B	C	D
London	Blue	Green
New York	Yellow	White	Blue
Milano	Blue

As you can see, values in column A should only occure once. For all rows that each value occured, the values in column B should somehow be merged in the same row. Just look at the tables if my explanation doesn't make sense

Can I do this with a formula or do I need a VBA script? Obviously there are lots of more rows and columns in the real worksheet.

Thanks!

Maybe:

Please Login or Register  to view this content.

**AB33** · 01-20-2014, 12:49 PM

Another one!

**Rawland_Hustle** · 01-24-2014, 11:04 AM

You guys are awesome! It worked fine and I really appreciate your help!

THANKS!!!

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Delete rows where column A has duplicates based on values in Column B

[SOLVED] Find duplicates in column A, add values from column B (possibly delete duplicates)

[SOLVED] Remove rows based on duplicates in certain columns, and merge data from another column

Remove duplicates on column A and merge rows in column B

Identifying Duplicates based on values in another column

Bookmarks

Bookmarks

Posting Permissions