# Excel Formula Help

Copper Contributor

# Excel Formula Help

I need help Highlighting cells in a column based on the number of matches found in another column. Example column A( to highlight) has 3 cells with value "AM" but column B only has 2 with value "AM" I only want it to highlight the top 2 hits in column A and not all of them

11 Replies

# Re: Excel Formula Help

With the above setup, Cond. Format. rule with formula that Applies to: =\$B\$3:\$B\$12:

``=IF(COUNTIF(\$C\$3:\$C\$12, \$B3), COUNTIF(\$B\$3:\$B3, \$B3) <= COUNTIF(\$C\$3:\$C\$12, \$B3))``

# Re: Excel Formula Help

this unfortunately did not yield a result for me. I am working with Carrier SCAC codes and trying to use this to find the amount of available trailers in our yard vs how many loads we have going out with that carrier scac, example i have 5 loads for BYLR and only 4 BYLR trailers, then i have 31 loads for HIRS but only have 10 trailers. Thank you for your help

# Re: Excel Formula Help

Well, looking at your picture the formula you implemented isn't the one I shared. Carefully look at where the \$ are in my formula/sample please

I'm afraid I won't be able to help further without the actual workbook if you can't make it work

(How to upload & share file i.e. with OneDrive

# Re: Excel Formula Help

My mistake, I will give it another attempt and let you know how it goes, thank you!

# Re: Excel Formula Help

So this worked to an extent, now the problem is it is not finding matches to color based on the top result, would it be possible to add another criteria to highlight the cell nearest to the top of the worksheet , I can send you the worksheet if you'd like.

# Re: Excel Formula Help

would it be possible to add another criteria to highlight the cell nearest to the top of the worksheet

Not sure I understand. Could you post a picture to illustrate what you expect?

I can send you the worksheet if you'd like

Feel free to attach it to your next post. If you're not allowed to attached a file yet, you can send it to me via Private Message

# Re: Excel Formula Help

@Lorenzo I sent the file to you in a private message.

# Re: Excel Formula Help

would it be possible to add another criteria to highlight the cell nearest to the top of the worksheet

???Not sure I understand. Could you post a picture to illustrate what you expect?

(Not my call but is there a good reason your Table5 (sheet Finished FA) has > 1M rows?)

# Re: Excel Formula Help

No good reason, didnt realize that im working on putting together this report the way we usually run it before i decided to build this version. Ill send it to you in a pvt msg.

# Re: Excel Formula Help

Copy/Paste of what I sent you in a Private Message (w/o the attachement - see in PM)
OK, so if I understand you well, with the pictures you shared, you were saying that the Cond. Format. rule didn't do what you expect. Could you double-check the attached version? I checked a few and this looks good to me....

TBH I have no idea why this didn't work before and why it does now as the only changes I made are:
- Formatted your data in 'Yard Equipement Dump' as Table named TableYardEquip
- Created Named Range YardEquipTrailers (=TableYardEquip[Trailer SCAC])
for clarity and to be more efficient than provisioning as you did with:

``=IF(COUNTIF('Yard Equipment dump'!\$A\$2:\$A\$260,\$D2)...``

- Updated the Cond. Format rule as follow:

``=IF(COUNTIF(YardEquipTrailers,\$D2), COUNTIF(\$D\$2:\$D2,\$D2) <= COUNTIF(YardEquipTrailers,\$D2))``

You will have to update it accordingly in VBA...

I'm not really good at VBA but there are some "strange" (to me) things in your code. Probably something you should consider look at... I would also recommend:
- Resize Table5 to its actual number of rows
- Update the Cond. Format. rule Applies to: accordingly
- In VBA work with Table5 (perso. I would give it a name) instead of sheet 'Finished FA'

(If you need help with VBA there are good guys on this Community)

# Re: Excel Formula Help

(Hope you had a safe trip). Did you have a chance to look at the last modifs I suggested? Still have issues?