Merge Cells / Keep Text for Unique Column 1 Cell Values?

Whoa

New Member
Joined
Sep 19, 2003
Messages
5
I have a simple task that I can't figure out.

I have rows of data with two columns per row:

A C
A M
A Q
B R
B F
C D
C J

and I want to select those cells and have it change them to:

A C, M, Q
A
A
B R, F
B
C D,J
C

Column 1 is sorted. A,B,C are not the real values of my data but you get the idea. There might be 20 A rows, 30 B rows, etc. I want to process through them and concatenate the Column 2 values for every unique Column 1 value.

Could anybody help me with this coding? I keep trying and I keep failing.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
HTML's treating multiple spaces as one messed things up a bit.

The idea is that I start with two columns of data and I end up with two columns of data. Only the values of cells in the second column change, either to blank or to a long string that is a concatenation of all the other second column cells associated with the same, identical cell 1 value.

Hope that makes sense.
 
Upvote 0
Hi Whoa:

I had a bit of play with the following macro ...
Code:
Sub yWhoa()
    CountNC = 0
    For j = 2 To 100
        K = j + CountNC - 1
        Cells(K, 1).Select
        If cels(K, 1) = "" Then Exit Sub
        Cells(ActiveCell.Row, 3) = Cells(ActiveCell.Row, 2)
        CountNC = WorksheetFunction.CountIf([a2:a8], ActiveCell): Debug.Print CountNC
        For i = 1 To CountNC - 1
            Cells(ActiveCell.Row, 3) = Cells(ActiveCell.Row, 3) & Cells(ActiveCell.Row + i, 2)
        Next i
    Next j
End Sub
to produce the following result ...
y030919h2.xls
ABCD
1Field1Field2ComboField
2ACCMQ
3AM
4AQ
5BR
6BFFD
7CDDJ
8CJ
Sheet6


I kept the integrity of the source data and produced the resulting concatenation in column C.

I hope this helps. If I have misunderstood the question -- my apologies!
 
Upvote 0
Thank you so much!

These solutions have really helped me and I've got it working now!
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top