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

jayped

Board Regular
Joined
Mar 20, 2019
Messages
54
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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)?
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFGHIJK
NamesAmountDescriptionAmountDescriptionAmountDescriptionAmountDescriptionAmountDescription
JohnNov StatementMay Spreadsheet
MaryNov StatementDec StatementJan StatementMay SpreadsheetFeb Document
SusanNov Travel AdvanceDec Statement
ClintDec Statement

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]500[/TD]

[TD="align: right"]350[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1200[/TD]

[TD="align: right"]200[/TD]

[TD="align: right"]800[/TD]

[TD="align: right"]400[/TD]

[TD="align: right"]600[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]750[/TD]

[TD="align: right"]430[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]160[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Main



Copy this Macro into a Standard Module:

Code:
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
ABC
NamesAmountDescription
JohnNov Statement
JohnMay Spreadsheet
MaryNov Statement
MaryDec Statement
MaryJan Statement
MaryMay Spreadsheet
MaryFeb Document
SusanNov Travel Advance
SusanDec Statement
ClintDec Statement

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]500[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]350[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1200[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]200[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]800[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]400[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]600[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]750[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]430[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]160[/TD]

</tbody>
Main
Hope this HELPS !!!

Jim
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top