Bring scattered data to correct columns

Please supply more details. A sample of what you are working with and what you want would be helpful as well.

Thanks for the reply I'm attaching a sample of what the spreadsheet looks like now and a sample of what I need it to look like.

Originally Posted by vadaniels

Thanks for the reply I'm attaching a sample of what the spreadsheet looks like now and a sample of what I need it to look like.

Hello, I know this post is quite old but if you often need to merge columns from distinct files or to merge similar lines, I suggest you have a look at podbox.com. It's an alpha release for the moment but moving fast to meet the users demand... It's free and will remain free. Please let me know if you want me to create your account to test it.

I'm having a similar challenge on formatting cells/columns/rows. i have a worksheet and the last row contains transaction descriptions.(extracted from a PDF bank statement). After converting the PDF to excel the last column had its rows scattered or separated how do i merge or trim them without having to do it one at a time cos its like 45pages
find attached an extract of the whole document.

Your original data is all over the place. Is there any logic as to the placement of the data?

Not originally, no thats why I set up the header. I just wanted to know if there was anyway to put these information in order underneath the header with out have to drag each bit of information to its proper header.

Without knowing the logic behind the placement of the data, there is no way of determining what goes where.
Can you post the "After" for your Sample1 book? Currently, it looks like the data in row 2 does not belong with the rest of the data. Put the before and after in the same book.

Here is a New sample thanks again for at least taking a look I appreciate it

You an use this macro to reorganize the data.

Sub shuffleData()
Dim i As Long, strA As String
Application.ScreenUpdating = False
i = 2
Do Until Cells(i, 1) = ""
    Cells(i, 2) = Cells(i + 1, 1)
    Cells(i, 3) = Cells(i + 1, 3)
    Cells(i, 4) = Cells(i + 3, 4)
    Cells(i, 5) = Cells(i + 1, 4)
    Cells(i, 5).NumberFormat = "mm/dd/yyyy"
    Cells(i, 8) = Cells(i + 1, 7)
    Cells(i, 9) = Cells(i + 2, 7)
    Cells(i, 10) = Cells(i + 3, 10)
    Cells(i, 11) = Cells(i + 3, 11)
    Cells(i, 12) = Cells(i + 3, 12)
    Range(Cells(i, 10), Cells(i, 12)).NumberFormat = "#,##0"
    Range(Cells(i + 1, 1), Cells(i + 3, 12)).Delete shift:=xlUp
    i = i + 1
Loop

'remove duplicates in A column
i = 2
strA = Cells(i, 1)
i = i + 1
Do Until Cells(i, 1) = ""
    If Cells(i, 1) = strA Then
        Cells(i, 1) = ""
    Else
        strA = Cells(i, 1)
    End If
    i = i + 1
Loop
Application.ScreenUpdating = True
End Sub

For some reason it isnt working:
I copied the marcos
I hit alt+F11 to bring up the VBE
I hit insert module and then pasted the marcros into the module
I then backed of that and went to the excel spreadsheet and hit alt+f8 and select shuffle data and then I hit run but it doesnt do anything. Did I do something wrong?

the macro is set up to look for the first row of data on row two. Is the first row of data on row 2?

change the two i=2 lines to i = first row under the header. Assuming of course that the data is how you presented it in the "New sample" book

thanks again

Greetings! This thread seems to ask the sort of question I have, but I was unable to adjust the sample macro provided above.

In the attached sample table, I wish to shuffle the values (1-12) to their respective columns (B-M). The full table has 2,400 rows with 4,600 values.

Thank you for any guidance.