Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In attached workbook, both transaction type (inflow & outflow) can be done with a single customer. Say Customer "X" has 2 inflow * 100 and 1 outflow * 50, the closing bal will be 150. The requirement is to identify the transaction relates to closing bal (col F) of each customer on FIFO (first in first out)basis.
    Attached Files Attached Files
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Could you give us an idea how the end result should look?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784650' date='15-Jul-2009 07:24']Could you give us an idea how the end result should look?[/quote]
    Possibly looking for pivot table type results? with bal per customer?
    [pre]Sum of Amt.
    Cust Total
    sun 11482
    Asso 19225
    dave 24775
    fransis 68496
    Llyod -44136
    ultra -25170
    Grand Total 54672
    [/pre]

  4. #4
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784650' date='15-Jul-2009 07:24']Could you give us an idea how the end result should look?[/quote]
    it should look something like "summary" in attachment within same sheet or in different sheet through a simple Index/match formula, an array formula, a macro etc. etc . If a lot is partially outflown, balancing figure is required, if possible. If not possible, entire qty for that lot can be shown.
    Attached Files Attached Files
    Regards
    Prasad

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='ase001' post='784652' date='15-Jul-2009 07:53']Possibly looking for pivot table type results? with bal per customer?
    [pre]Sum of Amt.
    Cust Total
    sun 11482
    Asso 19225
    dave 24775
    fransis 68496
    Llyod -44136
    ultra -25170
    Grand Total 54672
    [/pre][/quote]
    Initially, i tried a pivt table but it has some restriction and does not serve the purpose. Thanks for taking time.
    Regards
    Prasad

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm sorry, I don't have the slightest idea how the summary table is obtained. Perhaps someone else will be able to help you.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    [quote name='prasad' post='784657' date='15-Jul-2009 08:23']Initially, i tried a pivt table but it has some restriction and does not serve the purpose. Thanks for taking time.[/quote]
    Prasad, you'll need to explain the summary requirements better than the summary table.
    From what I can see of the summary table there does not appear to be one rule as to how it is derived.
    The first entries are inflow, but then your llyod entries are inflow and outflow and missing some of your inflow.
    Can you give definitive rules for the summary?

  8. #8
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Pl have a look in attachment. In Col "D" of summary sheet, required qty of last transaction of that particular cust from col "D" of Data sheet. Hope this will make some sense.
    Attached Files Attached Files
    Regards
    Prasad

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the same lookup formula as in column C, but refer to data!$D$2:$D$24 instead of data!$A$2:$A$24

  10. #10
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='784671' date='15-Jul-2009 11:24']You could use the same lookup formula as in column C, but refer to data!$D$2:$D$24 instead of data!$A$2:$A$24[/quote]
    Thanks Hans, reached to a level but stuck again...

    How to drive value for Col F & G & so on.
    Attached Files Attached Files
    Regards
    Prasad

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use the LARGE function in an array formula. See attached version.

    BTW I have assumed that "2nd last", "3rd last" etc. refer to the dates sorted ascending within each customer.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •