Pull data from overall sheet to new sheet based on criteria

[edkawy79]

Hello, I need you help please on the attached sample file

In sheet 1 a sample for daily data for all customers (original file contain data of last 5 years and rows over 30000 rows).
On daily basis, I need to review the full transaction history for X customer statement and revise some entries if needed. (Revise the price / quantities, etc).
In sheet 2, I want to have the following please:

1. Cell B3, to create unique list for all customer (so I can choose the desired customer and show the report for him).
2. Since the costumer has too many transactions , I wish to use Cells G2 & G3 to show the report within specific period of time ( from – to )
3. Once the customer name selected in cell B3, the full transaction history must displayed in the below range A6:I6 until last row.
4. In column No 10 (column J), I wish to have formula to calculate the total amount for the customer ( I mean to subtract the total IN from the Total OUT for each row and show the balance in this column , so I can tract down when was last time the balance for this customer was 0 $.
5. the range of this rows preferable to be automatically adjusted for each print.
6. hope to consider add or remove column in overall table will not affect sheet 2

Hope this is clear and many thanks in advance for your help

[大灰狼1976]

Hi edkawy79!
Only 1 - 3 items are processed. You can choose the name in cell B3.
Please refer to the attachment.

[edkawy79]

Hi edkawy79!
Only 1 - 3 items are processed. You can choose the name in cell B3.
Please refer to the attachment.

大灰狼1976
Thanks a lot of your efforts and apologize for late reply, since I was testing th log

About point no 2 ( date cells ) I see that first I must to choose the period of time that I wish to have the report otherwise the data will be all pulled out. Is it possible to revise this to update customer report automatically ??
About point no 4 , this is very important need for my file , so I have put sample formula I am using in sheet 1, column K ( named Customer Total ) but it make my file very slow , is it possible to put this formula in VBA code so the file would be faster ?
Finally , is it possible to keep original formulas in sheet 2 same as sheet 1 ?

Many thanks again for your big help and I wish the above request will not bother you a lot.

Overall_Draft (1).xlsm

[p45cal]

Try changing your formula in cell K7 of the Sheet1 sheet from:
=SUBTOTAL(9,$I$7:I7)-SUBTOTAL(9,$H$7:H7)
to:
=SUMIF($B$7:$B7,[@Customer],I$7:I7)-SUMIF($B$7:$B7,[@Customer],H$7:H7)
so that your "so I can tract down when was last time the balance for this customer was 0 $." will work. Currently your subtotals formula only uses the visible cells and if the dates are filtered the sums only take into account those dates (they will exclude all prior data so will not show you when the balance was last $0).
Note that for this type of formula (both yours and mine) to work, the data in column A MUST be sorted oldest to newest (oldest date at the top)
The long time it's taking seems to be the filtering (and recalculation) of sheet1. I would use a pivot table instead with a timeline. It's much faster (no filtering of the original table at all). Out of time just now but if I get time I'll put a workbook together.

[edkawy79]

p45cal ... thanks a lot for your efforts, i changed the formula as you said and its ture , sheet 1 is very slow due to recalculation and this is big problem for me... the original file about 30000 rows ..
what about pivot table with timeline ... can you please show me example how it would work for me ??
thanks agian

[p45cal]

what about pivot table with timeline ... can you please show me example how it would work for me ??

Yes, but not right now.

[p45cal]

In the attached, on Sheet2b, select your customer in cell B3 or in the slicer at cell N1, play with the TimeLine at the top. Updates are almost instantaneous.

[Paul_Hossler]

@p45cal -- that's cool. I've never used Timeline before. Thanks for showing it

[p45cal]

edkawy79 I noticed a PM from you earlier today, but now it seems to have disappeared. Could you try again to post here or send me another Private Message.