Comparing 2 Columns

[SpeedStick]

I have 2 columns that I am using to identify information (for over 14,000+ records). The first column is used to group the information by number. The second column shows different letters assigned to these numbers. Is there a formula that will allow me to identify a group like 655 and identify the value least common in that group 'K' and then add background color or highlight the cell that K is in.

Number Letter
655 G
655 K
655 G
656 F
656 G
656 F
656 G
657 D
657 D
657 E
658 B
658 B
658 C

 

[mintjulep]

Try a pivot table.

 

[SpeedStick]

Thank you for your post. The pivot did not work for me.

One change, I am trying to highlight only where the letters do not match for range of a number. For instance:

Number Letter
659 K
659 K
659 K

This would not be hightlighted because the letters are the same. In the examples above the all of those would be highlighted because the letters are different.

 

[SkipVought]

You can use Confitional Formatting.

In the Formual Is Textboc enter a COUNTIFS() function if you have Excel 2007+ or use SUMPRODUCT()

Using your example...
[tt]
=COUNTIFS($a$2:$a$999,A2,$b$2:$b$999,b2)=1

or

=SUMPRODUCT(--($a$2:$a$999=A2)*($b$2:$b$999=b2))=1

[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SpeedStick]

Thank you for your response. I used the CountIFS() in 2007 application. When I implement it highlighted the items in before the correct item. What suggestion would you have to correct this item.

 

[SpeedStick]

Thank you for your response. I used the CountIFS() in 2007 application. When I implement it highlighted the items in before the correct item. What suggestion would you have to correct this item.

In addition, what were the 2 dashes (--) in the other equation suppose to be.

=SUMPRODUCT(--($a$2:$a$999=A2)*($b$2:$b$999=b2))=1

 

[SkipVought]

According to your requirement, you want highlighted only the combination of number and letter that are unique. When there are multiple occurrences, you do not want a highlight.

Is that correct? That is what the formula in the CF does.

If, however, you are referencing the incorrect row in your formula, the CF will not occur in the correct row.

TIP:
[tt]
Select ALL the data you want CFed
Activate the CF Window
Make ALL your references with respect to the UPPER-LH Cell in your selection.
[/tt]

Regarding the "2 dashes (--)", for instance
[tt]
-(-1) = 1
[tt]
all this does is coerce the arithmetic to occur.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SpeedStick]

Dear Skip,

Thank you for answering my question. One tweak, if I need to highlight the lease common letter - see below. In essence, if there are mulitple least common values and highlight each one of those for each instance they occur.

Thank you for your help.

Scott

Number Letter
655 G
655 K
655 G
655 K
655 G

 

[SpeedStick]

Dear Skip,

Thank you for answering my question. One tweak, if I need to highlight the lease common letter - see below. In essence, if there are mulitple least common values and highlight each one of those for each instance they occur.

Thank you for your help.

Scott

Number Letter
655 G
655 K
655 G
655 K
655 G

In addition, I realize I need to handle single instances of numbers. Presently, if a number only appears once that number is highlighted. I do not want that single number to be highlighted.

Number Letter
655 G
655 K
655 G
655 K
655 G
659 T
670 K
670 K
670 G

 

[SkipVought]

OK,

Post an example of the various conditions and the highliting that you want.

Please use TGML markup: TT and HIGHLIGHT makkups. If you do not know what that means, search for TGML on this web page and follow th link.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SpeedStick]

Also, I would like to highlight the entire range for numbers that have different values. So below, 655 and 670 all there rows would be highlighted.



Number Letter
655 G
655 K
655 G
655 K
655 G
659 T
670 K
670 K
670 G
671 A
671 A
671 A

 

[SkipVought]

Also, I would like to highlight the entire range for numbers that have different values. So below, 655 and 670 all there rows would be highlighted.

[tt]
Number Letter[highlight]
655 G
655 K
655 G
655 K
655 G[/highlight]
659 T[highlight]
670 K
670 K
670 G[/highlight]
671 A
671 A
671 A
[/tt]
Do you really mean...

I would like to highlight the entire range for numbers where the letters are not all the same?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

In answer to the last post, consider using COUNTIF referencing the Number column and comparing that with COUNTIFS referencing both columns.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SpeedStick]

Yes, exactly that is what I want. Thank you for the example. I posted my example just during the time you posted your example.

 

[SpeedStick]

Is anyone aware of VB code that can resolve this issue?

 

[SkipVought]

No VBA required!

Use Conditional Formatting, an Excel spreadsheet feature, employing the two function I suggested.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SpeedStick]

How would set up the both functions? Would they be impedded in each other or would you set it up run one functiona after the other?

 

[SkipVought]

if the COUNTIF using the number criteria equals the COUNTIFS using the number and letter criteria, then they are all the same.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}