Excel Formula Help

Copper Contributor

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

Hi @DakotaHoughotn78 

 

Sample.png

 

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))

 

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

 

DakotaHoughotn78_0-1707728798533.png

 

@Lorenzo 

@DakotaHoughotn78 

 

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

My mistake, I will give it another attempt and let you know how it goes, thank you!
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.

@DakotaHoughotn78 

 

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

 

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

@DakotaHoughotn78 

File received - thanks - but what about my other point:)

 

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?)

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.

@DakotaHoughotn78 

 

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)

Hi @DakotaHoughotn78 

 

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