0

I have 2 cells of temperature and salinity.

Sal      Temp      
34,9882  -1,4744      FALSE
34,9883  -0,0721      FALSE
34,9884  -0,8864      TRUE
34,9884  -0,8864      TRUE
34,9888  -1,5913      FALSE
34,9888  -1,5946      FALSE

Here I have sorted them according to Sal from lowest to highest. As you can see rows 3 and 4 have the same sal and temp. How can I identify such rows which are the same in a excel template with thousands of rows? (If the sal is the same but temp different e.g. rows 5 and 6, then it is FALSE) Ideally all the data are not sorted. I tried sorting but obviously this is not a good solution as you still have to identify by eye. I am only at =EXACT(A1;A2) and this only sees if any sal are the same but not if temp are also the same.

Bala
  • 67
  • 1
  • 7
  • no i want to retain the duplicates... – Bala Sep 23 '13 at 14:12
  • 1
    Not sure what you arr trying to achieve, but `=AND(EXACT(A1;A2);EXACT(B1;B2))` will deal with both columns. – LS_ᴅᴇᴠ Sep 23 '13 at 14:13
  • i'm just doing a check to make sure... if i have some "True", then I would have to do other things... but your answer also means I have to sort the data first... Is it possible to just look for these duplicates... like in rows 3 and 4 IF they were say in row 3 and row 999? – Bala Sep 23 '13 at 14:19

1 Answers1

2

If you don't mind adding an additional column, then in column D (assuming your data is in col A:C) use the formula

=COUNTIFS($A$2:$A$7;"="&A2;$B$2:$B$7;"="&B2)

Be sure to change A7 and B7 to the end of your range. This counts how many repeats match the condition of what is in column A and B for the current row. Unique rows will evaluate to 1, while anything with a repeat will be 2/+. Data does not have to be sorted for this to work. Note that it only identifies duplicates in a generic sense (a specific "clone group" is not uniquely identified against another clone group), but you'll know how many duplicates there are for that set of data (Sal of 349,884 and temp of -0,8864).

Hope this helps.

Bala
  • 67
  • 1
  • 7
chewmewaba4
  • 75
  • 6
  • you could add an `=1` to the end of that formula and then use it for conditional formatting instead of as another column if you just want a quick visual aid – Dan Sep 23 '13 at 15:11
  • hi @chewmewaba4... i just wanted to understand your last statement... what do you mean by identifying duplicates in a generic sense and specific clone group? – Bala Sep 23 '13 at 15:36
  • @Bala, if you have another duplicate pair (beside the 349,884 and -0,8864 [I'll call this pair 1 for later use]) they will also so "2" in the new column. If you attempted to filter "pair 1" using the new column, you'd end up with all the clone group pairs, not the specific clone group (pair 1). Or, maybe better put, if you filtered on the value "1" in the new column, you'd have all the singlet unique data points, rather than just one data point. If you want to filter down to finding a particular/specific duplicate group, you'll need to do some extra filtering. – chewmewaba4 Sep 23 '13 at 16:47