Can't solve simple? problem. Can some genius out there help!

[Carl S.]

Want to pull simple data from two tables/sheets into a third and use formulas
(array or otherwise), not macros or filters. I only want to select rows for
customer CARL. Below is the example:

Table Deposits (First Table, one data source)
Customer Date Deposit
Carl 1/25/2008 $150,000.00
Carl 2/28/2009 $50,000.00
Joe 3/5/2008

Table Withdrawls (2nd Table another data source)
Customer Date Withdrawl
Carl 1/28/2008 (6000)
Joe 3/1/2008 (3000)
Carl 4/12/2008 (4000)
Joe 5/5/2008 (7000)

The desired final result is listed below. The column with the name CARL can
be omitted...just here to show the result data.

Customer Date Depost Withdrawl
Carl 1/25/2008 $150,000.00
Carl 1/28/2008 (6000)
Carl 2/28/2009 $50,000.00
Carl 4/12/2008 (4000)

Thanks for any help offered!!! Whatever FORMULAS are arrived at will be
greatly appreciated!

 

[Ashish Mathur]

Hi,

Try this

1. Add another columns to the deposits table and give it a heading, say
Action and all the entries under the column should be Deposits
2. Add another columns to the withdrawals table and give it the same heading
I.e. Action and all the entries under the column should be Withdrawals
3. Select the deposits table (including the header row and the new column
added) and assign it a name, say dep
4. Select the withdrawals table (including the header row and the new column
added) and assign it a name, say withd
5. Save the file (say try.xls), assume on the desktop
6. Now click on any blank cell and go to
In Excel 2003: Data > Import External Data > New Database query
In Excel 2007: Data > Get External Data > From Other Sources > From
Microsoft Query
7. Select Excel files and click on OK
8. Navigate to the desktop and select try.xls in the left had pane. Click
on Next
9. In the Choose columns dialog box, select dep and click on the greater
then symbol
10. In the Choose columns dialog box, select withd and click on the greater
then symbol
11. Click on Next and in the dialog box which appears next, click on OK
12, Click on the SQL button and type the following:

Select * from dep union all select * from withd

13. Click on OK/Finish
14. Go to File > Return Data to MS Office Excel
15. You should now see the import data box (if it does not appear, then
press Alt+tab to switch to a different window and return to try.xls)
16. Select pivot table and do the following:
- Drag Customer to the Page fields area and select Carl;
- Drag Date to the row area; and
- Drag Action to the Data area.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Herbert Seidenberg]

Excel 2007
Added to my previously suggested macro method:
Mathur's simple SQL procedure:
Modified and illuminated.
http://www.mediafire.com/file/jjwutgjwdhm/11_18_09b.xlsm