+ Reply to Thread
Results 1 to 9 of 9

Dynamic Unique List

  1. 03-01-2010, 04:03 PM #1
    John Bates
    John Bates is offline
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Dynamic Unique List

    Hi,

    I am trying to put together a formula (column D) that will give me a unique list of all countries (column C), given that column B's adjacent cell contains "COUNT". To restate it another way, if column B contains "COUNT" then return a unique list of those adjacent country names in column C. This formula will need to be in cells D3:D1002.

    I was thinking an array would work but for some reason I'm not able to have it be conditional on if column B equals "COUNT".

    Thanks for all your time and help!!!
    John
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-01-2010, 05:18 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic Unique List

    id try it like this helper cols on sheet 2 as you have 2007 you can wrap up col g in =iferror() to hide n/a
    just altered it a tad to change ranges no need to look up whole cols as it slows things down
    Attached Files Attached Files
    Last edited by martindwilson; 03-01-2010 at 05:58 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 03-01-2010, 05:50 PM #3
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dynamic Unique List

    John, I would use a key column as well, to keep the solution robust. On your original sheet, put this formula in that tiny column in A3:
    =IF(COUNTIF($C$1:C3,C3)>1,A2,A2+1)
    ...copy that cell down the column.

    Then in D3 put this and copy down:
    =IF(ROW($A1)>MAX(A:A), "", INDEX($C:$C, MATCH(ROW($A1), $A:$A, 0)))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  4. 03-01-2010, 05:57 PM #4
    John Bates
    John Bates is offline
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: Dynamic Unique List

    Hi MartinWilson,

    Thanks so much for your help. Because of some of the limitations of the particular doc (the attached doc is just a scenario mock-up...not the real doc) that I am using due to external data queries I have to try to keep this to a single column.

    I put together a formula in column E of the attachment but it processes SLOWLY and does not account for column B equaling "COUNT". Any thoughts on something like this?

    THANKS AGAIN!!
    John
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 03-01-2010, 06:08 PM #5
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic Unique List

    to be honest things would get worse array formulas can be slow and in not sure if you could do it even then without a helper.another way is to advanced filter for unique values using a criteria range(still needs another column) or some nice code that jb will leap into action and provide for you lol
    Reply With QuoteRegister To Reply
  6. 03-01-2010, 06:19 PM #6
    John Bates
    John Bates is offline
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: Dynamic Unique List

    Hi JBeaucaire,

    Thanks so much for your help with this! One thing I noticed was your formulas is that all you are calculating the unique values for all of column C rather than for the group of countries located directly next to "COUNT" in column B. The final formula should start with "India" from cell C179 and then only include 43 countries. Thanks again for your help! My biggest struggle is getting a unique list to calculate only for those values that should be included ("COUNT"). Does that make sense?

    Thanks again,
    John
    Reply With QuoteRegister To Reply
  7. 03-01-2010, 06:34 PM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Dynamic Unique List

    Maybe this in A3, copied down:
    =IF(B3<>"COUNT", A2, IF(SUMPRODUCT(--($B$3:$B3="COUNT"),--($C$3:$C3=C3))=1, A2+1, A2))
    Reply With QuoteRegister To Reply
  8. 03-01-2010, 07:06 PM #8
    John Bates
    John Bates is offline
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: Dynamic Unique List

    Perfect!! Thank you so much!!!
    Reply With QuoteRegister To Reply
  9. 03-01-2010, 07:51 PM #9
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    re: Dynamic Unique List

    =IF(B3<>"COUNT", A2, IF(SUMPRODUCT(--($B$3:$B3="COUNT"),--($C$3:$C3=C3))=1, A2+1, A2)) i like that!
    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