1

I have the following three columns as data:

enter image description here

I am using the formula =IF(COUNTIFS($A:$A;A2;$B:$B;B2;$C:$C;C2)=1;1;0) as my output in the Output column. Basically I am giving a non-unique row a 0 and a unique row a 1.

However as you can see I want to give the first occurency of a unique combination of ART, Date and ISIN an 1 and the rest a 0. Why is my currently used formula wrong?

I appreciate your answer!

Carol.Kar
  • 4,581
  • 36
  • 131
  • 264

1 Answers1

1

That's because your formula currently will put 1 for all unique rows and 0 for all duplicate rows, irrespective of whether they appear for the first time or not.

You can get the behaviour you're looking for by playing around a bit with the range lockings. For instance, you could try this:

=IF(COUNTIFS($A$2:$A2;A2;$B$2:$B2;B2;$C$2:$C2;C2)=1;1;0)

On the first row, the above formula will count only within the first row, so the result is bound to be 1.

When it reaches the second row, it will check the first 2 rows and will find that it's the same as the first one, so will return 2 as count and give you 0.

Notice how I locked the ranges: The first reference is completely locked $A$2:$A2 while the second one has the row variable $A$2:$A2.

Jerry
  • 70,495
  • 13
  • 100
  • 144
  • Thx for your answer! However, doesn`t this approach require a certain order of my data? If not, why not? – Carol.Kar Mar 27 '14 at 09:32
  • @Kare No, it doesn't require your data to be ordered in any way. Let's say we are comparing only 1 column. First row has 1, the count is 1. Second row has 2, the count returns 1 again (since it's new). Third row has 1 again, the count will return 2 (1 from the first row, and one now), so the `IF` will return 0. – Jerry Mar 27 '14 at 09:39