Excel: Combine and match two data

[Natalie.H.Kim]

have two different financial statements for different years (see
below)

A B
1 Cash & Cash Equivalents 159,100
2 ST Financial Instrument 140,000
3 Marketable securities 117,104


A B
1 Cash & Cash Equivalents 200,000
2 Marketable securities 245,123
3 Account receivables 25,345


I want this to look like


A B C
1 Cash & Cash Equivalents 159,100 200,000
2 ST Financial Instruments 140,000 -
3 Marketable securities 117,104 245,123
4 Account receivables - 25,345


The list is very long with different items but I need to combine them.
I have been manually inserting a row to the main sheet, copying and
pasting from the other sheet.


Is there easier way to consolidating the two files into one?

 

[Dave Peterson]

I'd add a new sheet.

Copy the values in column A of sheet1 to A1 of this new sheet.
Then copy the values in column A of sheet2 under the last entry of that new
sheet (in column A).

Then add a header in A1 (insert a new row if you need to).

Then use Data|Filter|Advanced filter to get the unique entries and plop those
into B1.

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use =vlookup()'s to return each of those amounts in column B and C.

In B2:
=if(iserror(vlookup(a2,sheet1!a:b,2,false)),"",vlookup(a2,sheet1!a:b,2,false))

In c2:
=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())