Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unique entries in column? (97/2000)

    I have a sheet with a column which has about 13000 names (e.g. cities). Many are the same and they are alphabetically sorted.
    I need to create in another sheet a small column with all cities used. Don't want to use VBA, guess an Array-formula could do this... (o... of course I intend to have this result column update & generate itself. So I'm not looking for manual things but really some formulas... [number of unique cities < 30])...

    Example:
    Berlin
    Berlin
    Berlin
    London
    London
    Paris
    Paris
    Paris
    Paris

    My goal
    Berlin
    London
    Paris

    Any suggestions?

    Thanks,

    Erik Jan

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Have you tried a pivot table?

    You would have to put a 'count of city' in the DATA area as well as 'city' in the ROW area

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Erik Jan,

    Too hard to explain, but I've attached an example (Excel 2000). In the first column, I've entered some data. The second column extracts the unique entries, leaving the other cells blank. In the third column, the blanks are eliminated. I must admit, I am not clever enough to invent this. I took it from Chip Pearson's website. But, it works without VBA.
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Unique entries in column? (97/2000)

    ErikJan, you may also want to look at Data, Filter, Advanced Dialog, to see if "copy to another location ,unique records only" fits your needs.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    I have attached a small example. The formula will only work if the list is sorted and with no spaces.

    You also have to add the range "cities" to create a dynamic range. The formula for the range is included.

    This is an array formula I have no idea how long it will take to calculate on a 13,000 entry list. You might want to think about the unique list capability of the advanced filter.

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    I'll try to attach again.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Thanks, this seems to do the job.... problem is now that I'm trying to understand what happens... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Here's the formula... (for cell C9)

    <font color=blue>=IF(OR(C8="",MIN(IF(C8<A9:A16,ROW(A9:A1 6),""))=0),"",INDEX(Cities,MIN(IF(C8<A9:A16,ROW(A9 :A16),"")),1))</font color=blue>

    Now most of it I can grab... what I seem to be missing however is the reason for the "forward look gap size".... Please note the A9:A16 references... this doesn't cover the full column length and is eight long... why eight? What does this do??

    Erik Jan

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Sorry, I did not fully convert the formula after I added the named range. Try this.
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    I honestly think that a pivot table is the easiest way to go - these formulas look too long winded.

    I've attached an example file - see what you think
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Oh, I agree. I would go with a pivot table or even a unique list from advanced filter.

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Thanks all... I'll surely check out the 'formula' approach. Played around last Friday and noticed this is rather slow indeed.
    The Pivot-table looks good, never realized I could use the table in formulas around it (so e.g. count # uniques by just determining # rows in a formula 'outside' the table)... sorry.
    The advanced filter list is nice as well but I'm unsure as to how I could get to it (other then seeing it visually...)

    Anyway, thanks again, I've enough try out the coming weeks when time allows. I'll keep on monitoring the thread just in case...

    Erik Jan

  12. #12
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Data - Filter - Autofilter

    Just make sure you're in part of the range you wish to filter.

    At the top there's also a subtotal formula which i use a lot with filters - use the pulldown bars to get what you want
    Attached Files Attached Files

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Tears ;-)

    Wrt AutoFIlter... I see that and use it all the time. Problem is that you can LOOK at it but no more.... there's no way (I think) to actually USE the unique list somewhere on the sheet. That's my problem...

    EJ

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Unique entries in column? (97/2000)

    Filter, Advanced, Unique, Copy has limitations in that in XL97 it can only be copied to the same sheet, but once it is setup it remembers it's last filter source and target ranges. So will filtering it to a range name and then =VLOOKUP the names work for your purposes?
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique entries in column? (97/2000)

    Yep... you're right... didn't know that... learning all the time <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Erik Jan

Page 1 of 2 12 LastLast

Posting Permissions

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