converting 4 rows of data onto one row

[littlevic]

first off I'm sorry about the title of the thread!

I have electricity invoice data come in from a supplier electronically. Columns A through E are the same data relating to the same invoice number and the same meter. The charges however has been split over 4 rows as follows:

e.g.

Column A Column B Column C Column D Column E Column F Column G
Meter Ref Site Name Site Address Postcode Inv No Day Units £ Night Units £
Meter Ref Site Name Site Address Postcode Inv No Standing Charge
Meter Ref Site Name Site Address Postcode Inv No Availability Charge
Meter Ref Site Name Site Address Postcode Inv No Settlement Charge

Can anyone tell me how I'd go about getting all of the charges onto 1 row as I need to upload this into a different system in a one row per record format?

thanks for your help

 

[TKB]

Are there multiple invoices listed on each sheet? As in, do you have multiple "jobs" just one after another?

like,

Job1
Job1
Job1
Job1

Job2
Job2
Job2
Job2

Etc.

Or is it just the one per sheet?

 

[littlevic]

Are there multiple invoices listed on each sheet? As in, do you have multiple "jobs" just one after another?

like,

Job1
Job1
Job1
Job1

Job2
Job2
Job2
Job2

Etc.

Or is it just the one per sheet?

yes there are multiple invoices (jobs) on one sheet (as your example above ad infinitum) - thanks

 

[TKB]

Ok, next question(sorry, trying to formulate a macro for you). Are the separate invoices listed one after another with no spaces in between, or are there spaces in between each one?

 

[littlevic]

Ok, next question(sorry, trying to formulate a macro for you). Are the separate invoices listed one after another with no spaces in between, or are there spaces in between each one?

there are no blank rows between invoices (jobs) - thanks again

 

[sumantaimd]

Try this Macro

Sub Merge_CommonRow_3rd()
'Jerry Beaucaire (4/26/2010)
'For duplicated values in column A data is sorted and merged
Dim LastRow As Long, Rw As Long
Dim LastCol As Long, Col As Long
Dim delRNG As Range
Application.ScreenUpdating = False


LastRow = Range("A" & Rows.Count).End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column



'Sort table by column A
Range("A1", Cells(LastRow, LastCol)).Sort Key1:=Range("A1"), _
Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers


'start the delete rng
Set delRNG = Range("A" & LastRow + 10)

'Merge data and mark rows for deletion at the end
On Error Resume Next
For Rw = LastRow To 3 Step -1
If Range("A" & Rw) = Range("A" & Rw - 1) Then
For Col = 2 To LastCol
If Cells(Rw - 1, Col) = "" Then Cells(Rw - 1, Col) = Cells(Rw, Col)
Next Col
Set delRNG = Union(delRNG, Range("A" & Rw))
End If
Next Rw


'Delete and cleanup
delRNG.EntireRow.Delete xlShiftUp
Set delRNG = Nothing
Application.ScreenUpdating = True
MsgBox "DONE"

End Sub

For More you can see
https://jerrybeaucaires-excelassistant/merge-functions/merge-rows