Combine information from rows

[Hannah]

Good afternoon.

I have an Excel 2003 spreadsheet which has several thousand rows of customer
details. In many of these rows the first columns (A-M - which contain name,
phone number address etc) are duplicated however the data contained in the
following columns (N-CM) is different. Is there any way that I can combine
the rows so that I have one for each individual customer?

I found an example elsewhere on the forum that uses the Index array formula,
but I am not sure how to adapt it so it would work with my spreadsheet.

Many thanks

Hannah

 

[Sheeloo]

How will you combine the information in columns N-CM if there are multiple
rows for same values in A-M?

 

[Hannah]

The basic layout is as detailed below

Contact Company Address No Ordered Cost Contacts Demo
Joe Bloggs ABC1 1 New town 1
Joe Bloggs ABC1 1 New town 567.00
Joe Bloggs ABC1 1 New town 1
Joe Bloggs ABC1 1 New town 1
Jane Doe DEF1 25 New Town 2
Jane Doe DEF1 25 New Town 1,134.00
Jane Doe DEF1 25 New Town 1
Jane Doe DEF1 25 New Town 0

Happy to lose all the duplicates in the first columns, but need to
consolodate the data in the others so that it looks something like:

Contact Company Address No Ordered Cost Contacts Demo
Joe Bloggs ABC1 1 New town 1 567.00 1 1
Jane Doe DEF1 25 New Town 2 1,134.00 1 0

I hope this makes sense

Thank You

 

[Sheeloo]

Understood.
When I get time today, I will try to come up with a solution where I will
combine data from col M from all rows for the same person into col M and
delete other records, and so on for other columns.

 

[Sheeloo]

Are you familiar with VBA programming?

Here is the basic code;
Sub AppendValues()

Dim i, j, FirstRow As Integer
Dim Primarykey, NextId, AppendedString As String
i = 1

Primarykey = ActiveSheet.Cells(i, 1).Value
AppendedString = ActiveSheet.Cells(i, 2).Value

FirstRow = 1

For i = 2 To 21
NextId = ActiveSheet.Cells(i, 1).Value
If NextId = Primarykey Then
AppendedString = AppendedString & ActiveSheet.Cells(i, 2).Value
Else
ActiveSheet.Cells(FirstRow, 2).Value = AppendedString
FirstRow = i
Primarykey = NextId
AppendedString = ActiveSheet.Cells(i, 2).Value
End If
Next

End Sub

Assumption: Data is sorted on Column A which also contain the value for
which one needs to find duplicate rows and joing the values in Column B

This looks for the same value in Col A and appends values in Column B
against the first occurrence in Col A.

Do you want me to complete the code or can you do that yourself?

Can someone out there complete this code for Hannah?

 

[Sheeloo]

Here is the complete code;
(Do not forget to sort the data on first 13 columns A-M. Also if your first
row contains headers then change the first i to 2. This code will also put a
DELETE in column CN so that you can filter on DELETE and remove the duplicate
rows.

To run it Open the file on the sheet contatining the data, hit ALT-11, Click
Insert->New Module, Paste the code and hit the PLAY button
Go back to the sheet and verify results...

DO MAKE A COPY OF YOUR DATA BEFORE TESTING THE CODE. There is no error
handling code.

Let me know if works for you.

Option Explicit
Sub AppendValues()

Dim i, j, FirstRow As Integer
Dim Primarykey, NextId, AppendedString(78) As String

'Change this to 2 if you have header rows
i = 1
Primarykey = ""
NextId = ""

For j = 1 To 13
Primarykey = Primarykey & ActiveSheet.Cells(i, j).Value
Next

' N-CM = 78 columns

For j = 1 To 78
AppendedString(j) = ActiveSheet.Cells(i, j + 13).Value
Next

FirstRow = 1

'Change this to 3 if you have header rows
For i = 2 To 21

For j = 1 To 13
NextId = NextId & ActiveSheet.Cells(i, j).Value
Next

If NextId = Primarykey Then
For j = 1 To 78
AppendedString(j) = AppendedString(j) & ActiveSheet.Cells(i, j +
13).Value
Next
ActiveSheet.Cells(i, 91).Value = "Delete"
Else
For j = 1 To 78
ActiveSheet.Cells(FirstRow, j + 13).Value = AppendedString(j)
AppendedString(j) = ""
Next
FirstRow = i
Primarykey = NextId
NextId = ""

For j = 1 To 78
AppendedString(j) = AppendedString(j) & ActiveSheet.Cells(i, j +
13).Value
Next
End If
NextId = ""
Next

End Sub

 

[Hannah]

Good Morning Sheeloo,

Think that has done it - many thanks for your assistance!

Hannah