On Dec 18, 12:48*pm, yowzers
wrote:
Sheet 1 is for Store 1. *In column A I have dates, in column B I have
profits. *Sheet 2 is for Store 2 with the same data as Sheet 1. *However,
there is not necessarily an entry on every date. *For example, Store 1 could
have entries on 1/1, 1/2, 1/5. *Store 2 could have entries on 1/1, 1/3, 1/5.

How can I consolidate the data from these two sheets into one to look like
this:

1/1 *Store A profit * Store B profit
1/2 *Store A profit
1/3 * * * * * * * * * * * * * Store B profit
1/5 *Store A profit * Store B profit *

Here’s one way with Excel 2003.

In my example, the dates are Excel dates in 2009, and they start in A1
of Sheet1 and Sheet 2.

In Sheet3, column A is a helper column. It can be hidden later to
avoid clutter. The desired results will be in columns B:D.


In Sheet3!A1 put
=IF(AND(
ISERROR(
MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet1!$A:$A,0)),
ISERROR(
MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet2!$A:$A,0))),
0,1)

In Sheet3!A2 put
=IF(AND(
ISERROR(
MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet1!$A:$A,0)),
ISERROR(
MATCH(DATEVALUE("1/1/2009")+ROW()-1,Sheet2!$A:$A,0))),
0,MAX($A$1:$A1)+1)
and copy downward.

In Sheet3!B1 put
=IF(ROW()MAX(A:A),"",
DATEVALUE("1/1/2009")+MATCH(ROW(),A:A,0)-1)
and copy downward.

In Sheet3!C1 put
=IF(B1="","",
IF(ISERROR(VLOOKUP(B1,Sheet1!A:B,2,FALSE)),"",
VLOOKUP(B1,Sheet1!A:B,2,FALSE)))
and copy downward.

In Sheet3!D1 put
=IF(B1="","",
IF(ISERROR(VLOOKUP(B1,Sheet2!A:B,2,FALSE)),"",
VLOOKUP(B1,Sheet2!A:B,2,FALSE)))
and copy downward.

Modify to suit.