Macro to combine multiple columns into one (list of names, values, description)

[jayped]

Hi


I have a sheet set up as shown below:

A B C D E
Names Amount Description Amount Description

John 500 Nov Statement
Mary 1200 Nov Statement 200 Dec Statement
Susan 750 Nov Travel Advance 430 Dec Statement
Clint 160 Dec Statement

How can I combine columns D and E with B and C using a macro (or any other possible solution) having the amount and description falling directly under the individual's name. I would like to end up with something like this on a new sheet without having to copy and paste and insert lines:

A B C
Names Amount Description

John 500 Nov Statement
Mary 1200 Nov Statement
Mary 200 Dec Statement
Susan 750 Nov Travel Advance
Susan 430 Dec Statement
Clint 160 Dec Statement

Why The data is set up like the above is because I have a list of names of employees where i have calculated a variance as at the end of a certain period based on a comparison between a worksheet and the ledger. The variances are basically amounts to be reconciled. In some cases the amounts will make up more than one class of activity and therefore I would like to see the 'description' and the amount rather than just a total figure, hence why its split out going across . Is there a better way of doing this?


Thanks,

Jamie

 

[jim may]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

Can your ORIGINAL 5 columns ever be MORE columns with additional Amount Description running out multiple times (and columns)?

 

[jayped]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

Can your ORIGINAL 5 columns ever be MORE columns with additional Amount Description running out multiple times (and columns)?

Yes, it could be more.

 

[jim may]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

Yes, it could be more.

I will get back to you (TODAY HOPEFULLY). I will write a Macro
to achieve your goal. My only Q is it would be "cleaner" if I knew the Limits of
how many columns the "Amount Description (2 column)" combo might run out to the right.

Jim

 

[jayped]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

I will get back to you (TODAY HOPEFULLY). I will write a Macro
to achieve your goal. My only Q is it would be "cleaner" if I knew the Limits of
how many columns the "Amount Description (2 column)" combo might run out to the right.

Jim

I very much appreciate your help on this! I'd place a limit of 4 combos max.

 

[jim may]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

OK, Here's My Example. Create yourself a NEW Workbook with the data in this post and allow it to be your teacher, meaning unless you are confident of how things work don't mix my example with your LIVE DATA. So here you go - My Original Data First:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K
1	Names	Amount	Description	Amount	Description	Amount	Description	Amount	Description	Amount	Description
2	John	500	Nov Statement	350	May Spreadsheet
3	Mary	1200	Nov Statement	200	Dec Statement	800	Jan Statement	400	May Spreadsheet	600	Feb Document
4	Susan	750	Nov Travel Advance	430	Dec Statement
5	Clint	160	Dec Statement

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Main​

Copy this Macro into a Standard Module:

Sub RearrangeMyData()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    LC = Cells(i, Columns.Count).End(xlToLeft).Column
        If LC > 3 Then
        k = 0
        MyLC = WorksheetFunction.Max(MyLC, LC)
        RowsToInsert = ((LC - 3) / 2)
        Cells(i + 1, 1).Resize(RowsToInsert).EntireRow.Insert
            Do
            For j = RowsToInsert To 1 Step -1
                 Cells(i + (RowsToInsert - k), 2).Value = Cells(i, LC - 1).Value
                 Cells(i + (RowsToInsert - k), 3).Value = Cells(i, LC).Value
                 LC = LC - 2
                 k = k + 1
            Next
            RowsToInsert = RowsToInsert - 1
            Loop Until iRows = 0
        End If
Next i
Range(Cells(1, 4), Cells(1, MyLC)).EntireColumn.Delete
MylrA = Cells(Rows.Count, "A").End(xlUp).Row
Set mynewRng = Range("A2:A" & MylrA)
mynewRng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Columns(1).Value = Columns(1).Value
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

And After running the macro your sheet should look THUSLY...

Excel 2010

	A	B	C
1	Names	Amount	Description
2	John	500	Nov Statement
3	John	350	May Spreadsheet
4	Mary	1200	Nov Statement
5	Mary	200	Dec Statement
6	Mary	800	Jan Statement
7	Mary	400	May Spreadsheet
8	Mary	600	Feb Document
9	Susan	750	Nov Travel Advance
10	Susan	430	Dec Statement
11	Clint	160	Dec Statement

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Main​

Hope this HELPS !!!

Jim

 

[jayped]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

THANK YOU SO MUCH! I tried it with your original data first and then with mine.

Just one more question... how do i get it to update each month when the data changes?

 

[jim may]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

THANK YOU SO MUCH! I tried it with your original data first and then with mine.

Just one more question... how do i get it to update each month when the data changes?

The Macro is a START to FINISH process.

Given this fact, what about using or keeping the file that you have now (which works) AS A TEMPLATE.
And then each time you have new data -- Just Paste or type it into your Raw Data Sheet, then run the Macro.

Each time I'd Just Save the File as follows: MyData-01-31-2019; then (next month) MyData-02-28-2019, etc.
By the Way -- the Macro accommodates a limitless number of "Amount Descriptions", not just the 4 mentioned.

Another ASSUMPTIONS -- which CANNOT EXIST for the Macro to run (Without a hitch) is there can be NO BLANKS anywhere to the Left of another Filled in Amount Description. Know what I mean? OK?

Glad it worked for you.

Jim

 

[jayped]

Re: Help on macro to combine multiple columns into one (list of names, values, description)

Okay, I'll do that.. yes, I know what you mean by no blanks to the left. Thanks.