# Unique Values Without Remove Duplicates

1. ## 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.

2. ## Re: Unique Values Without Remove Duplicates

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

3. ## Re: Unique Values Without Remove Duplicates

Hi

Try this, with your data starting in A2

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

4. ## Re: Unique Values Without Remove Duplicates

You can shorten it:

Formula:
`Please Login or Register  to view this content.`

5. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`

8. ## 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.

9. ## 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.

11. ## 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)

12. ## 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. ## 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. ## Re: Unique Values Without Remove Duplicates

Here it is. No CSE.

15. ## Re: Unique Values Without Remove Duplicates

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

16. ## 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. ## Re: Unique Values Without Remove Duplicates

You are welcome.

Waiting..

18. ## 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. ## 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. ## 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.

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

21. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`
Does that work for you?

24. ## Re: Unique Values Without Remove Duplicates

Nice idea, ChemistB

25. ## 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. ## 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)

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

#### 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