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?