Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
combine details on rows
I have a spreadsheet which has been extracted and imported from an external
source. The first row shows headings. Each subsequent row relates to a client and depending on the information, the details are shown on separate rows but never in the same column Example: A,B,C,D,E,F,G,H,I ClientRef,Name,Address,PostCode,Birthdate,Occupati on,Status,CarMake,CarModel BLJ1,Joe Bloggs,The House,DY13 9LT,,,,AUDI,A4 BLJ1,Joe Bloggs,The House,DY13 9LT,10/06/1982,Consultant,Employed,,, I need to combine the details so each ref appears only once and all the relevant details are shown along the one row. Any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
combine details on rows
So each column in each group only has a single entry--but it could be on a
different row? If yes: Option Explicit Sub testme() Dim FirstRow As Long Dim LastRow As Long Dim wks As Worksheet Dim FirstCol As Long Dim LastCol As Long Dim iCol As Long Dim iRow As Long Dim FoundAMatchingRow As Boolean Set wks = Worksheets("sheet1") With wks FirstRow = 2 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row FirstCol = 5 'first 4 columns are used to match up rows For iRow = LastRow To FirstRow + 1 Step -1 FoundAMatchingRow = True For iCol = 1 To FirstCol - 1 If .Cells(iRow, iCol).Value < .Cells(iRow - 1, iCol).Value Then FoundAMatchingRow = False Exit For End If Next iCol If FoundAMatchingRow = True Then For iCol = FirstCol To _ .Cells(iRow, .Columns.Count).End(xlToLeft).Column If .Cells(iRow, iCol).Value < "" Then 'move it up .Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value End If Next iCol .Rows(iRow).Delete End If Next iRow End With End Sub I assumed that the first 4 columns are used to determine if the rows match and should be processed together. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sarah (OGI) wrote: I have a spreadsheet which has been extracted and imported from an external source. The first row shows headings. Each subsequent row relates to a client and depending on the information, the details are shown on separate rows but never in the same column Example: A,B,C,D,E,F,G,H,I ClientRef,Name,Address,PostCode,Birthdate,Occupati on,Status,CarMake,CarModel BLJ1,Joe Bloggs,The House,DY13 9LT,,,,AUDI,A4 BLJ1,Joe Bloggs,The House,DY13 9LT,10/06/1982,Consultant,Employed,,, I need to combine the details so each ref appears only once and all the relevant details are shown along the one row. Any ideas? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine Data from Multiple Rows | Excel Discussion (Misc queries) | |||
Combine simmilar rows | Excel Discussion (Misc queries) | |||
combine 2 rows into 1 record | Excel Discussion (Misc queries) | |||
Combine rows | Excel Discussion (Misc queries) | |||
how to combine the multiple rows into one rows? | Excel Worksheet Functions |