Unique Values Without Remove Duplicates

You can shorten it:

Formula:

Please Login or Register  to view this content.

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)

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? :-)

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!

Sample uploaded :-)

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? :-)

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.

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

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)?

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!

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? :-)

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?

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)