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