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

So basically, I would want to consolidate the dates in column A with no
duplicates, and then have column B list Store A profits for that day (if any)
and column C list Store B profits for that day (if any). And then I can
manually total Store A + B into column D.

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"yowzers" wrote in message
...
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

So basically, I would want to consolidate the dates in column A with no
duplicates, and then have column B list Store A profits for that day (if
any)
and column C list Store B profits for that day (if any). And then I can
manually total Store A + B into column D.

Excel 2007 PivotTable
Consolidate stores on multiple tabs.
http://www.mediafire.com/file/wcjgiznmztw/12_26_09.xlsx

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.