+ Reply to Thread
Results 1 to 26 of 26

Unique Values Without Remove Duplicates

  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Unique Values Without Remove Duplicates

    I have 2 columns of data.

    A 1 =1
    A 2 =1
    A 1 =0 (since duplicate of row 1)
    B 1 = 1
    B 1 = 0 (since duplicate of above row)
    C 2 = 1


    I need a formula for a hidden column that marks only the first value as unique and rest as duplicates.
    I.e. Hidden column would generate results as per col c above, giving me a total of 4 unique values.
    Last edited by Camel; 07-20-2012 at 07:50 AM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Unique Values Without Remove Duplicates

    If Data is in Column A
    In B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Is that what you were looking for?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    Hi

    Try this, with your data starting in A2

    =COUNTIF($A$1:$A2,A2)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,779

    Re: Unique Values Without Remove Duplicates

    You can shorten it:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Never use Merged Cells in Excel

  5. #5
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Hi

    I have tried that, but that only identifies if the data in col A is unique.

    What I need is for it to count if the combination of col A & Col B is unique and then assign a value to the first occurrence.

    e.g.
    Col A Col B Col C
    A test =1
    A user =1
    A test =0 (since duplicate of row 1, as both Col A & B have same values)

  6. #6
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Hi Fotis (I see you're from Greece...what a lovely place!) I have been many years ago.

    Actually going to visit Greece for 2 months in Sep on a non Excel related question:
    1) Do you know if you can get a Ferry pass? (like as we want to visit many islands)
    2) Or where I can find accommodation on some of the islands for a longer stay? :-)

  7. #7
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Unique Values Without Remove Duplicates

    The issue is that it's impossible to see what you have in Col A or in Col B from the formatting of the post. I think I get it now.

    Try this in C1 dragged down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Hi

    Getting closer. I managed to get the same result with a different formula.

    This only counts where both are unique. BUT I need it to count first of the set of duplicates.

    See attached sample sheet.

    Your help is very appreciated!
    Attached Files Attached Files

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    Hi from me, too.

    1)I think that it will better for all if you'll upload a small sample workbook.

    2) About Non Excel question, send a PM to me of what are you interset. I think i will be able to help you.

  10. #10
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Sample uploaded :-)

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    This, works for me. For you?. Starting at row2

    =IF(SUMPRODUCT(($A$1:A2=A2)*($B$1:B2=B2))=1;1;0)

    Edit: Change semi colons to comma.


    =IF(SUMPRODUCT(($A$1:A2=A2)*($B$1:B2=B2))=1,1,0)
    Last edited by Fotis1991; 07-19-2012 at 10:57 AM. Reason: Edit

  12. #12
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    I am getting an error (tried to ctrl shift enter as well)

    Can you send me a copy of the sheet where you get it to work? :-)

  13. #13
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Worked it out....

    It won't accept ; but instead needs a ,

    Strange?? Let me check it now on some more data and see how it goes.

  14. #14
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    Here it is. No CSE.
    Attached Files Attached Files

  15. #15
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    About Semi colons, i told you in my edit in my post#11

  16. #16
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Thank you very much for the help...been expanding my Excel skills these last few days.

    Will send you a PM to get some ideas from you about for Greece...

  17. #17
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    You are welcome.

    Waiting..

  18. #18
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Hi

    Just reviewing the formula need to check something.

    I see that the array expands as you go down the list.

    1) What if the values are below the current row? When you press F2, these are then not included in the array. (the current formula only searches the rows above it). If I change the formula to be B:B for the whole column (so it checks all data), then it only shows unique values as 1 and does not include the first instance of a series of duplicates.

    Any ideas on how to edit it so I can change the array to B:B (for the entire column)?

  19. #19
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    I am not able to follow you..

    As you drag the formula down, it's works for any rows....

    If you think no, upload the workbook with the new example.

  20. #20
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    Also I am trying to modify the formula slightly for another function I urgently need. (the first formula shows all unique values and where there are duplicates it only counts the first one.)

    This modification needs to count where there are duplicate values in Col b to col A value, and only count the first duplicate.

    I need to identify which rows in Col A have MULTIPLE rows in Col B, but only count the first instance where this occurs.

    Please see attached sample 2 for more info.

    Really appreciate the help. I've spent days on my spreadsheet and these are last few things I have been unable to solve!
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    I think it actually works - was just surprised as if you go to half way in col (i.e. row 6) and press F2, it shows it is only looking at rows until row 6 and ignoring the bottom. But it autochecks...

    Any ideas for the slight modification I posted? :-)

  22. #22
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    Nothing for now and as it is the time that i have to leave from the office(if in the meantime, find no solution)i'll see it again tomorrow morning.

  23. #23
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Unique Values Without Remove Duplicates

    Hi Camel,

    I managed to get the same result with a different formula. This only counts where both are unique. BUT I need it to count first of the set of duplicates.
    Sometimes a "slightly different" formula makes all the difference. I checked and my formula does work on your initial issue (see attached).

    I used this formula on your second issue
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Does that work for you?
    Attached Files Attached Files

  24. #24
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Unique Values Without Remove Duplicates

    Nice idea, ChemistB

  25. #25
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    You guys really know your stuff. I once spent 2 weeks doing a single advanced spreadsheet (that taught me most of what I know - but you guys come up with some great formulas!)

    The
    Please Login or Register  to view this content.
    nearly works.

    It assigns a value of 1 to the first of the series of duplicates, BUT it also assigns a value of 1 to the unique values. (i.e where col a has only 1 reference in col b) - for unique values it must assign a 0. I changed the first =1 to >1 and it worked like a charm! :-)

    Just a very big thank you for all your help. I'm sure I'll come across a few more queries as I complete my spreadsheet.

    Should I leave this thread open in case I come across any more queries for my spreadsheet or should I open up a new thread for them?

  26. #26
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Unique Values Without Remove Duplicates

    The formula works great.

    On a pivot table it now shows all the values as 1 but it also shows where the values are 0. How do I get it to only show the rows where the value is = 1 and exclude all rows where value = 0.

    I have set it to Sum of variable (from above formula).

    (I spent last Saturday learning all about pivot tables so I may be missing something obvious!) :-)

    (I set a filter that only shows values where it =1 and it works, but wondering if there is a neater way of doing it - as it's one more step I need to explain to someone how to do)

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1