+ Reply to Thread
Results 1 to 9 of 9

Counting unique text instances with conditions

  1. 11-19-2012, 04:53 AM #1
    kazaly
    kazaly is offline
    Registered User
    Join Date
    11-15-2012
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2013
    Posts
    14

    Counting unique text instances with conditions

    I'm having some trouble coming up with an approach to this.
    I have a 5 column list of over 4000 rows to extract counts from.
    The list is several ebay monthly invoices.
    What I am trying to do is derive a count of total stock offered plus sales made.

    The Columns are:

    Date / Item / ID / Fee / Amount

    The sales part is pretty straightforward as a fee is charged when a sale is made so I can use SUMIFS and reference Fee.
    The problem I have is that many items that don't sell get relisted each month.
    Also some, but not all, "Items" have multiple stock copies so when one sells another is listed.
    This always occurs after a sale.
    When it sells "Item" will have a Fee noted as "FVF"
    Items that are listed or relisted have a Fee noted as "Insert"
    ID is numeric and unique and when an item relists it gets a new ID.
    What I think I need is a way to count identical item occurrences until an FVF is found.

    The row structure looks like this:

    21/08/2012 / Ooby Dooby / 234765 / Insert / 0.05
    22/09/2012 / Ooby Dooby / 345876 / Insert / 0.05
    23/10/2012 / Ooby Dooby / 487590 / Insert / 0.05
    28/10/2012 / Ooby Dooby / 487590 / FVF / 0.78
    29/10/2012 / Ooby Dooby / 590682 / Insert / 0.05

    There are 2 copies of Ooby Dooby represented above and 1 was sold.

    I am trying to count multiple instances of this type and come up with the correct number of copies.
    This is really doing my head in
    Any help would be fantastic.
    Reply With QuoteRegister To Reply
  2. 11-19-2012, 05:00 AM #2
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Counting unique text instances with conditions

    Please attach a sample workbook with the expected output for better understanding.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved

    If the suggestion helps you, then Click *below to Add Reputation
    Reply With QuoteRegister To Reply
  3. 11-19-2012, 05:03 AM #3
    crzu
    crzu is offline
    Forum Contributor
    Join Date
    09-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2003, Excel 2007, Excel 2013
    Posts
    103

    Re: Counting unique text instances with conditions

    Hi, I maybe able to help. But I could not really understand your question.

    I understand you have the below data

    21/08/2012 / Ooby Dooby / 234765 / Insert / 0.05
    22/09/2012 / Ooby Dooby / 345876 / Insert / 0.05
    23/10/2012 / Ooby Dooby / 487590 / Insert / 0.05
    28/10/2012 / Ooby Dooby / 487590 / FVF / 0.78
    29/10/2012 / Ooby Dooby / 590682 / Insert / 0.05

    Are you trying to get?
    Ooby Dooby, Insert = 4
    Ooby Dooby, FVF = 1
    Click * to reward me...
    Thank you...
    Reply With QuoteRegister To Reply
  4. 11-19-2012, 05:16 AM #4
    kazaly
    kazaly is offline
    Registered User
    Join Date
    11-15-2012
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Counting unique text instances with conditions

    Hi again Sixthsense and thanks for your help the other day.
    I'm using SUMIFS like a pro now lol.
    Here is a copy of the data I'm working with.
    I need to count instances of Item (Book title) and work out how many actual books are in the list.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 11-19-2012, 05:27 AM #5
    canapone
    canapone is offline
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Counting unique text instances with conditions

    Hi All,

    probably a pivot table or using advanced filter are more practical.

    A possible solution

    Please Login or Register  to view this content.
    or using an helper column

    in F2 to be copied down and summed

    Please Login or Register  to view this content.

    Hope it helps
    Attached Files Attached Files
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  6. 11-19-2012, 05:33 AM #6
    :) Sixthsense :)
    :) Sixthsense :) is offline
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Counting unique text instances with conditions

    Not sure whether this is what you are looking for so please go through the attachment file for details.

    If this is not the expected output then please let us know the desired output for giving exact solution.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-19-2012, 06:01 AM #7
    kazaly
    kazaly is offline
    Registered User
    Join Date
    11-15-2012
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Counting unique text instances with conditions

    Thanks gentlemen but no cigar.
    I've put a simplified list on sheet 2 of this new workbook.
    The correct algorithm should return a total of 2 for Items.
    This is a tricky one and I've been on it all day with no luck.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 11-19-2012, 11:36 PM #8
    icestationzbra
    icestationzbra is offline
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Counting unique text instances with conditions

    see attached on Sheet2. i have meddled with your data for testing purposes.

    also, you will need to generate a unique list of items by using the Remove Duplicates functionality in Excel 2007. then, put this formula next to that unique list to get the counts.

    this solution is based on the assumption that you never "relist" another copy until the original listing is sold (never more than one copy for sale at the same time).
    Attached Files Attached Files
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -
    Reply With QuoteRegister To Reply
  9. 11-19-2012, 11:44 PM #9
    kazaly
    kazaly is offline
    Registered User
    Join Date
    11-15-2012
    Location
    Brisbane, Queensland
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Counting unique text instances with conditions

    Thanks everyone for your help last night.
    It's amazing what a nights sleep can do.
    I dragged the Item column wider and made a discovery!
    The item description gets additional text when a sale is made.
    As I already had the Fee column containing what amounted to a boolean (FVF/Insert) then a simple IF statement was all it took:

    Please Login or Register  to view this content.
    I just had to sort the list by 1. Item/2. Date and create a helper column for IF
    The list of 0's and 1's produced is then Summed = Total inventory stock past and present.
    Last edited by kazaly; 11-19-2012 at 11:58 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1