Good Morning Sheeloo,

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

Hannah


"Sheeloo" wrote:

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" wrote:

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" wrote:

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


"Hannah" wrote:

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