I have two worksheets, containing a list of transactions on two separate accounts (a savings account and a credit card). I want to get each row from the first two worksheets into a third so that I can build a pivot table from the consolidated data. How can I achieve that?

That is, I have 10 rows in sheet A, and 10 rows in sheet B, I want to end up with all 20 rows in another sheet C. It's not important to me what order they are in. Both source worksheets have the same format.

I've tried a few things:

  • Excel's "Consolidate" feature doesn't seem to achieve this (although it's possible I'm missing something).
  • Pivot table features for referencing multiple ranges have the ability for "join"-like logic, but that's not what I want either.
  • Pivot table features for multiple consolidation ranges sound good, but I can't get that feature to do what I need either.

I'm using Excel 2013.

There is a significant number of very similar questions, but I haven't found a question that wants to do exactly this.

My data looks exactly like this:

Sheet A

Date        Value       Description
21/01/2016  -$43.11     Pay credit card
18/01/2016  -$180.00    Movie tickets

Sheet B

Date        Value       Description
17/01/2016  -$21.00     Video games
18/01/2016  -$18.00     Beer

Sheet C (desired output)

Date        Value       Description
21/01/2016  -$43.11     Pay credit card
18/01/2016  -$180.00    Movie tickets
17/01/2016  -$21.00     Video games
18/01/2016  -$18.00     Beer
  • Could you please share your formula
    – Linga
    Jan 22 '16 at 5:30
  • @Linga, there's no formula, just data, but I will add it in
    – Boinst
    Jan 22 '16 at 5:47
  • are you wanting to do this with or formula or just combine the data? I'd just use copy and paste personally. Click in A1 or the top left, hold the left mouse button and drag to the bottom right corner of the data, and type contol-c, now move to A1 of sheet 3 and hit control-v, repeat to copy the sheet 2 data but this time click in the next empty row before you paste. done.
    – Tyson
    Jan 22 '16 at 6:54
  • @Tyson I'm looking for something more reproducible than c&p. I expect to update "A" and "B" each month.
    – Boinst
    Jan 22 '16 at 12:57

Please try the below sample code..

Sub test()
'change the sheet name and range name as yours

Set Rng = Sheets("Sheet1").Range("A1:A10")
Set Rng1 = Sheets("Sheet2").Range("A1:A10")

i = 1
j = 0

For Each cell In Rng
Sheets("Sheet3").Range("A" & i).Value = cell.Value
Sheets("Sheet3").Range("A" & i).Offset(0, 1).Value = cell.Offset(0, 1).Value
Sheets("Sheet3").Range("A" & i).Offset(0, 2).Value = cell.Offset(0, 2).Value
i = i + 1
Next cell
j = i

For Each cel In Rng1
Sheets("Sheet3").Range("A" & j).Value = cel.Value
Sheets("Sheet3").Range("A" & j).Offset(0, 1).Value = cel.Offset(0, 1).Value
Sheets("Sheet3").Range("A" & j).Offset(0, 2).Value = cel.Offset(0, 2).Value

j = j + 1
Next cel

End Sub

Hope this will help you


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.