The rest is to be deleted except for the bill no. lines

[sbvaram]

Dear all,

I have a sheet that comes output from a software, which I have placed in the main data sheet. It has to be converted to step by step which I have shown in sheets.
Step 4 is my last sheet i need FROM VBA CODE.


also enclosed herewith pics & sheet attached.

Thank u in Advanced..

STEP-1-001.png
STEP-2-001.png
STEP-3-001.png
STEP-4-FINAL.png

[maras_mak]

Check my suggestion.
You have to change the names because they are different, e.g. the 'MAIN DATA' sheet is "LIBERTY WINES, SOLAPUR (Pro", and in the 'CUSTOMER DATA' sheet is "LIBERTY WINES, SOLAPUR (Prop.Sandeep Talreja)".
The results are in sheet 'DATA CONVERT STEP-4- & FINAL' from cell J4.

[sbvaram]

Sir, Thanks for the reply,
when i run this code , Showing Display error , ( expected array )

pls check..

[sbvaram]

Sir, Actually
LIBERTY WINES, SOLAPUR (Pro is the name of this customer group, their under their name is LOTUS WINES**, LIBERTY WINES***, LUCKY WINES**

[jindon]

Try

Sub test()
    Dim a, b, i As Long, ii As Long, txt As String, w, x
    b = Application.Trim(Sheets("CUSTOMER DATA").Cells(1).CurrentRegion.Value)
    a = Application.Trim(Sheets("MAIN DATA").[a3].CurrentRegion.Value)
    With CreateObject("Scripting.Dictionary")
        For i = 2 To UBound(a, 1)
            If a(i, 1) = "" Then a(i, 1) = a(i - 1, 1)
            If a(i, 2) <> "" Then
                txt = Join(Array(a(i, 1), a(i, 2), a(i, 3)), Chr(2))
                If Not .exists(txt) Then
                    ReDim w(1 To 8)
                    For ii = 1 To UBound(a, 2)
                        w(ii + IIf(ii > 1, 1, 0)) = a(i, ii)
                    Next
                    x = Application.Match(a(i, 1) & "*", Application.Index(b, 0, 3), 0)
                    If IsNumeric(x) Then w(2) = b(x, 4)
                    .Item(txt) = w
                End If
            End If
        Next
        a = Application.Index(.items, 0, 0)
    End With
    With Sheets.Add
        Sheets("main data").Rows(3).Copy .[a3]
        .[b3].Insert xlShiftToRight
        .[b3].Value = "Company ID"
        .[a4].Resize(UBound(a, 1), UBound(a, 2)) = a
        .[a3].CurrentRegion.Borders.Weight = 2
        .Columns.AutoFit
    End With
End Sub

[maras_mak]

sbvaram, you were right. There was a problem with the tables declaration.
The corrected code below.

[sbvaram]

Yess... Correct , 100% perfect...
Thank u Very Much sir,

Sorry, I did not reply to you yesterday, yesterday was the office holiday.

Thank u once again!!!