How do I combine rows, combining duplicates as well as concatenati

[Guest]

I'm trying to combine rows of data and I need a certain column to concatenate
only if it is in a group of rows that have duplicate information in another
column.

Example:

Before...

Column 1 Column 2
X A
X B
Y C
Y D

Desired After...

Column 1 Column 2
X AB
Y CD

This is for a spreadsheet with thousands of rows

Any suggestions?

 

[Otto Moehrbach]

Donovan
Your example shows 2 cells in Column 1 which are the same, and then you
concatenate the corresponding 2 cells in Column 2. Can your data have more
than 2 cells in Column 1 with the same value? If so, do you want to
concatenate all the corresponding cells in Column 2?
HTH Otto

 

[Dave Peterson]

Maybe a macro????

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim oRow As Long
Dim PrevKey As String
Dim TempStr As String

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 1
TempStr = ""

'do the first
PrevKey = .Cells(FirstRow, "A").Value
TempStr = .Cells(FirstRow, "B").Value

'do the rest
For iRow = FirstRow + 1 To LastRow + 1
'lastrow + 1 makes sure last entry is processed
If .Cells(iRow, "A").Value = PrevKey Then
TempStr = TempStr & .Cells(iRow, "B").Value
Else
newWks.Cells(oRow, "A").Value = PrevKey
newWks.Cells(oRow, "B").Value = TempStr
PrevKey = .Cells(iRow, "A").Value
TempStr = .Cells(iRow, "B").Value
oRow = oRow + 1
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks for the reply Otto. It looks like someone else at our office figured
everything out, so don't worry about it.