column / row merge sort

[JD]

I have a spreadsheet that consists of rows of names and
columns of data pertaining to that name information. If a
name has more than one occurance, that name has more than
one row. I want to consolidate all the data for one name
to 1 row.

Short of doing it all by hand is there a better way?


thanks in advance!
JD

 

[Dave Peterson]

Depends on what you want to do with the data and what the data is.

If it's just numeric data, it may be as painless as using a pivottable to
summarize it. If the data contains text, then maybe concatenating fields
(multiple lines in one cell?) or maybe you want to just add the info to unused
cells to the right.

 

[JD]

I just need to add the alpha data to the cells on the
right.

Thanks

 

[Dave Peterson]

Before you get any useful response, you're going to have to post some details.

The list of names is in what column?
The alpha data is in what columns?

And it doesn't matter about the order of the data after the copy?

 

[JD]

Thanks. I have columns that list the name, account
number, balance, open date, type, etc. The rows are the
individuals data. A person can have more than one account
so they have more than one row, but it is still the same
person. For the people that have more than one account, I
would like to end up with only 1 row. It doesn't matter
what order it ends up in. It will be send somewhere else
where more data regarding the individual will be added.
This could probably be done easier in Crystal Reports, but
I am faster in Excel. I am still learning Crystal.
Thanks for helping.

 

[Dave Peterson]

I made a bunch of assumptions.

1. There's a header row in row 1
2. The names are in column A.
3. The stuff to copy is in column B then all the way to the right
(until you run out of entries)
4. The worksheet name is sheet1
5. It doesn't matter where anything is pasted in the lower numbered row.

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myCol As Long
Dim iRow As Long
Dim DestCell As Range
Dim rngToCopy As Range

'I'm guessing that the name is in column A.
myCol = 1

'name of sheet??
Set wks = Worksheets("sheet1")

With wks
'with headers in row 1
FirstRow = 2
LastRow = .Cells(.Rows.Count, myCol).End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, myCol).Value = .Cells(iRow - 1, myCol).Value Then
'it matches
Set rngToCopy = .Range(.Cells(iRow, "B"), _
.Cells(iRow, .Columns.Count).End(xlToLeft))
Set DestCell = .Cells(iRow - 1, _
.Columns.Count).End(xlToLeft).Offset(0, 1)
rngToCopy.Copy _
Destination:=DestCell
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub