Concatenate, Delete Blank Rows & Remove Repeated Header

[Barieq]

Hi,

I have have worksheet on which data is pasted on daily basis. There are some data which appears on two cell. What I need

Concatenate those cells which in two rows (Column E, F & M)
Delete all the blank rows
Delete repeated Header Rows (Except Row One)

Al these process should be done on sheet Expected Result (Keeping the Original Data as it is)

[Marc L]

Hi,

the original formatting (colors) must be conserved ?

[JBeaucaire]

This macro will be very fast, even on huge sets of data. It expects your two sheets to be named as shown, so you can edit the macro to change the sheet names if needed.

Please Login or Register  to view this content.

[Barieq]

Hi Marc,

No need colors to be conserved, It is just for information only.

[maras_mak]

Test my code.

Please Login or Register  to view this content.

[Barieq]

@JBeaucaire

Thanks, It is so fast and accurate. Please one more request, could you please delete entire row if there is duplicate order numbers.

And If I delete the whole data in Result sheet and run the macro the date format is in General. Can you please format to date and time.

[Barieq]

@maras_mak

Thanks, Nice solution. Please one more request, could you please delete entire row if there is duplicate order numbers.

And If I delete the whole data in Result sheet and run the macro the date format is in General. Can you please format to date and time.

[jindon]

Try

Please Login or Register  to view this content.

[Barieq]

Hi Jindon, Thanks but still duplicate Order number need to be deleted.

[Marc L]

(removed, see both next posts)

[jindon]

Order Number?
Which column?

[Marc L]

As your expected result does not match what you expect for, so where is your order # ?

[Barieq]

Sorry ID (Column A)

[jindon]

You don't have duplicate, anyway...
Add one line

Please Login or Register  to view this content.

[Barieq]

Thanks for all those for the wonderful code, anyway I have managed to remove the duplicate ID numbers with below code.

Please Login or Register  to view this content.

[Barieq]

Thanks Jindon, you are always genius. It is solved.

[Marc L]

According to your attachment as a VBA beginner starter without any variable neither any loop
just using Excel basics - advanced filters & formulas - maybe not the fastest but fast enough :

Sub Demo1()
         Application.ScreenUpdating = False
    With Sheet1.UsedRange
        .Range("V2").Formula = "=ISNUMBER(A2)"
        .AdvancedFilter xlFilterCopy, .Range("V1:V2"), Sheet2.[A1:S1]
        .Range("E1:F1,M1").Copy Sheet2.[X1]
        .Range("V2").Formula = "=ISBLANK(A2)"
        .AdvancedFilter xlFilterCopy, .Range("V1:V2"), Sheet2.[X1:Z1]
        .Range("V2").Clear
    End With
    With Sheet2.[A1].CurrentRegion.Rows
        With .Item("2:" & .Count).Columns
             .Item(5).Value2 = .Parent.Evaluate(.Item(5).Address & "&"" ""&" & .Item(24).Address)
             .Item(6).Value2 = .Parent.Evaluate(.Item(6).Address & "&"" ""&" & .Item(25).Address)
             .Item(13).Value2 = .Parent.Evaluate(.Item(13).Address & "+" & .Item(26).Address)
             .Item(13).NumberFormatLocal = Sheet1.[M2].NumberFormatLocal & " " & Sheet1.[M3].NumberFormatLocal & " "
        End With
             .Columns("X:Z").Clear
             .RemoveDuplicates Array(1), xlYes
    End With
         Application.ScreenUpdating = True
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[Barieq]

@Marc

Thanks, Excellent.