Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a large table (20-30 cols, 10-15K rows). What I need to do is to find the number of rows that match identically in one (or two) particular field(s), but not in another specific field (rest of the fields are irrelevant). Additionally I would like to be able to use conditional formatting to highlight such rows. Is this possible to do without scripting?

Example table with 10 rows and 4 cols:

2.558658418     106.47  STSGGTAALGCLVK      P01857 
2.558658418     106.47  STSGGTAALGCLVK      P01860   <--
2.4505791896    106.4   LYHSEAFTVNFGDTEEAK  P01009
1.3850997023    106.34  EQADFAIEALAK        P35579
1.3850997023    106.34  EQADFALEALAK        Q7Z406      
0.6654422739    105.36  RFDEILEASDGIMVAR    P14618-2
2.0767656337    105.26  STSESTAALGCLVK      P01859
2.0767656337    105.26  STSESTAALGCLVK      P01859
2.0767656337    105.26  STSESTAALGCLVK      P01861   <--
2.0767656337    105.26  STSESTAALGCLVK      P01861   

What I would like in this scenario is to get the count to be 8 and if possible to have the rows I marked with arrows (for the sake of having an example) to be highlighted. Note that if both the third and the fourth fields are identical (i.e. if the difference between the rows are elsewhere in the table) it's not a row of interest.

I don't normally work with Excel/OOCalc so I feel a little out of place working with such tables. I came across some how-tos/forums one of which contains the suggestion of using COUNTIFS (e.g. =COUNTIFS(C2:C114, "YES", F2:F114, "> 0")) or the OOCalc equivalent with SUMPRODUCT (e.g. =SUMPRODUCT(C2:C114="YES" ; F2:F114>0)

The problem with that approach is that it matches the cell contents to a predefined value like "YES". In my case I would like to compare the cell contents to the contents of the cell immediately above/below. Is it possible to tweak the above formulas to fit to my case?

share|improve this question

1 Answer 1

One way to mark the duplicates (it looks like that that is what you are after) is to put this in cell E1 and then copy it down.

=COUNTIF(C$1:C1;C1)

or

=COUNTIF(C$1:C1,C1)

*depending on your country settings

What this does is put a 1 for each unique or first item and a counter number for all subsequent items. You could "advance" this with:

=IF(COUNTIF(C$1:C1;C1)>1;"<--";"")

or

=IF(COUNTIF(C$1:C1,C1)>1,"<--","")  

The formula is then:

=IF(COUNTIF(C$1:C1;C1)>1;IF(COUNTIF(D$1:D1;D1)=1;"<--";"");"")

Oh, b.t.w. In your example, you show 3 arrows, but the data between Cell C4 and Cell C5 is different! Therefore My formula only shows 2 arrows.

enter image description here

AutoFormat

If you want to autoformat each marked row you format Cell A1 with the following:

Select: Formula
Enter: IF($E1="<--")
Create a newColour Scheme (e.g. MyYellow) and define the background for that scheme.

Now use the format painter to pick up the format of cell A1 and paste it over A1 to D8.

enter image description here

share|improve this answer
    
the arrows are something I added to show what I mean in the example, so I can't really use them in the real case –  posdef Feb 28 '13 at 15:14
    
So the only thing you want is the highlighted yellow rows? –  Robert Ilbrink Feb 28 '13 at 15:16
    
not sure what you mean by that... What I have are the numbers and letters (i.e. the arrows don't exist in the table); I want to know how many rows count as "duplicates" according to my criteria, and if possible highlight them. –  posdef Feb 28 '13 at 15:18
    
Have you tried this solution? –  Robert Ilbrink Feb 28 '13 at 15:22
    
Well this would identify duplicates with respect to field 3, but what happens if the rows are identical with respect to field 3 and 4? Those rows are not of interest. –  posdef Feb 28 '13 at 15:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.