Problem in Merging Information

Hi,

I'm not sure if this is possible, because I have a working file where in I have a sheet for RAW Data (Information from PDF), then once I click the button It will merge the specific data in Result Tab.

The problem here that details is not constant. Usually when I paste the information the Customer Name is located in ODD number of cell. I also included Legends for the location of the information that I need to capture.

Please see attached file for references.

I hope can someone help me with this project

Originally Posted by 323100N

Hi,

Usually when I paste the information the Customer Name is located in ODD number of cell.

Excel doesn't really like usually, it prefers certainty.

What does the data look like when it's NOT in an odd numbered row? Please give us some examples perhaps with even numbered company rows in the middle of your data. Show various permutations. i.e. companies in :

alternating odd/even rows
Several odd blocks followed by several even blocks

Please read my question and answer the specific points I asked.

Your example showed only stuff on odd rows. We (I) need to see stuff that's also on even rows and also understand whether you can have several successive rows of even rows stuff and then several odd numbered rows of stuff and whether the mixture of odd/even rows is random or whether there is any consistency.

Hi 323100N

This is as close as I can come to your desired Results. This Code has been assigned to your Button...

Option Explicit
Sub Test()
    Dim ws As Worksheet
    Dim ws1 As Worksheet
    Dim Rng As Range
    Dim LR As Long
    Dim LR1 As Long
    Dim i As Long

    Application.ScreenUpdating = False
    Set ws = Sheets("RAW Data")
    Set ws1 = Sheets("Result")

    With ws1
        .UsedRange.Offset(1, 0).ClearContents
    End With

    With ws
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        Set Rng = .Range(.Cells(2, "A"), .Cells(LR, "A"))

        For i = 3 To LR Step 2
            With ws1
                LR1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1

                If (IsNumeric(ws.Cells(i, "D").Value)) Then
                    .Range("A" & LR1).Value = ws.Cells(i, "A").Value & " " & ws.Cells(i, "B").Value & " " & ws.Cells(i, "C").Value & " "
                    .Range("B" & LR1).Value = ws.Cells(i - 1, "C").Value
                    .Range("C" & LR1).Value = ws.Cells(i - 1, "D").Value
                Else
                    .Range("A" & LR1).Value = ws.Cells(i, "A").Value & " " & ws.Cells(i, "B").Value & " " & ws.Cells(i, "C").Value & " " & ws.Cells(i, "D").Value
                    .Range("B" & LR1).Value = ws.Cells(i - 1, "C").Value
                    .Range("C" & LR1).Value = ws.Cells(i - 1, "D").Value
                End If
            End With
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

Here's a non macro way in a cell on row 2 copied down

Formula:

=OFFSET($A$1,ROW()*2-2,0)&" "&IF(ISNUMBER(OFFSET($B$1,ROW()*2-2,0)),"",OFFSET($B$1,ROW()*2-2,0))&" "&IF(ISNUMBER(OFFSET($C$1,ROW()*2-2,0)),"",OFFSET($C$1,ROW()*2-2,0))&" "&IF(ISNUMBER(OFFSET($D$1,ROW()*2-2,0)),"",OFFSET($D$1,ROW()*2-2,0))

This will return the whole vendor name
For the account and amount use the same formula but change the $A$1 ref to $B$1 7 $C$1

You're welcome...glad I could help. Thanks for the Rep.