highlighting color for three columns together and match with another column between two sheets

A

abdo meghari

Well-known Member
Joined
Aug 3, 2021
Messages
612
Office Version
  1. 2019
I would match column B,C,D together into sheet REPORT with column B for sheet ENTERING . if there are items for sheet ENTERING not existed based on sheet REPORT, then should highlight the items into column B,C,D are existed into sheet REPORT and not existed into sheet ENTERING . the matching depends on columns B,C,D together for sheet REPORT with column B for sheet ENTERING .
list.xlsx
AB
1ITEMBRAND
21BS 205/55R16 RE003 THI
32BS 205/65R15 EP150 INDO
43BS 205/65R15 MY02 THI
54BS 205/65R15 T01 JAP
65BS 205/65R15 T005 THI
76BS 185/65R15 B250 JAP
87BS 195/60R15 AR20 INDO
98BS 195/60R15 EP150 THI
109BS 195/60R15 T001 JAP
1110BS 195/60R15 150EZ THI
1211BS 195/65R15 MY02 THI
1312BS 195/65R15 EP150 THI
1413BS 195/65R15 EP150 JAP
1514BS 195/65R15 T001 JAP
1615BS 195/55R16 EP300 THI
1716BS 175/70R13 B25 INDO
1817BS 175/70R13 EP150 THI
1918BS 185/70R13 EP150 INDO
2019BS 175/65R14 EP150 THI
2120BS 175/65R14 B25 INDO
2221BS 175/70R14 EP150 THI
2322BS 175/70R14 MY02 THI
2423BS 185/65R14 TEC THI
2524BS 185/65R14 150EZ INDO
2625BS 185/65R14 EP150 INDO
2726BS 195/70R14 150EZ INDO
2827BS 185/65R15 TC10 INDO
2928BS 185/65R15 T005 INDO
3029BS 185/65R15 T01 JAP
ENTERING


list.xlsx
ABCD
1ITEMBRANDTYPEORIGIN
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150THI
43BS 185/70R13 EP150INDO
54BS 175/65R14 EP150THI
65BS 175/65R14 B25INDO
76BS 175/70R14 EP150THI
87BS 175/70R14 MY02THI
98BS 185/65R14 TECTHI
109BS 185/65R14 150EZINDO
1110BS 185/65R14 EP150INDO
1211BS 195/70R14 EP150INDO
1312BS 185/65R15 TC10INDO
1413BS 185/65R16 T005 INDO
1514BS 185/65R15 T01JAP
1615BS 185/65R15 B250JAP
1716BS 195/60R15 AR20INDO
1817BS 195/60R15 EP150THI
1918BS 195/60R15 T001JAP
2019BS 195/60R15 150EZTHI
REPORT





result
list.xlsx
ABCD
1ITEMBRANDTYPEORIGIN
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150THI
43BS 185/70R13 EP150INDO
54BS 175/65R14 EP150THI
65BS 175/65R14 B25INDO
76BS 175/70R14 EP150THI
87BS 175/70R14 MY02THI
98BS 185/65R14 TECTHI
109BS 185/65R14 150EZINDO
1110BS 185/65R14 EP150INDO
1211BS 195/70R14 EP150INDO
1312BS 185/65R15 TC10INDO
1413BS 185/65R16 T005 INDO
1514BS 185/65R15 T01JAP
1615BS 185/65R15 B250JAP
1716BS 195/60R15 AR20INDO
1817BS 195/60R15 EP150THI
1918BS 195/60R15 T001JAP
2019BS 195/60R15 150EZTHI
REPORT

thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
How about
Excel Formula: 
=COUNTIFS(Entering!$B:$B,TRIM($B2)&" "&TRIM($C2)&" "&TRIM($D2))
 
Upvote 0
Solution
this is what I got when using CF
list.xlsx
ABCD
1ITEMBRANDTYPEORIGIN
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150THI
43BS 185/70R13 EP150INDO
54BS 175/65R14 EP150THI
65BS 175/65R14 B25INDO
76BS 175/70R14 EP150THI
87BS 175/70R14 MY02THI
98BS 185/65R14 TECTHI
109BS 185/65R14 150EZINDO
1110BS 185/65R14 EP150INDO
1211BS 195/70R14 EP150INDO
1312BS 185/65R15 TC10INDO
1413BS 185/65R16 T005 INDO
1514BS 185/65R15 T01JAP
1615BS 185/65R15 B250JAP
1716BS 195/60R15 AR20INDO
1817BS 195/60R15 EP150THI
1918BS 195/60R15 T001JAP
2019BS 195/60R15 150EZTHI
REPORT
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D20Expression=COUNTIFS(ENTERING!$B:$B;TRIM($B2)&" "&TRIM($C2)&" "&TRIM($D2))textNO
 
Upvote 0
Oops, missed the =0 at the end of the formula
 
Upvote 0
can you show me how the formula should be ?
I put this
VBA Code: 
=COUNTIFS(ENTERING!$B:$B;TRIM($B2)&" "&TRIM($C2)&" "&TRIM($D2)&0)
doesn't work
 
Upvote 0
You just need to add =0 to the very end of the formula I posted.
 
Upvote 0
I no know what happens .somtimes doesn't accept the formula when add =0 and sometimes accept but nothing happens
this is the whole data
list.xlsm
AB
1ITEMBRAND
21BS 205/55R16 RE003 THI
32BS 205/65R15 EP150 INDO
43BS 205/65R15 MY02 THI
54BS 205/65R15 T01 JAP
65BS 205/65R15 T005 THI
76BS 185/65R15 B250 JAP
87BS 195/60R15 AR20 INDO
98BS 195/60R15 EP150 THI
109BS 195/60R15 T001 JAP
1110BS 195/60R15 150EZ THI
1211BS 195/65R15 MY02 THI
1312BS 195/65R15 EP150 THI
1413BS 195/65R15 EP150 JAP
1514BS 195/65R15 T001 JAP
1615BS 195/55R16 EP300 THI
1716BS 175/70R13 B25 INDO
1817BS 175/70R13 EP150 THI
1918BS 185/70R13 EP150 INDO
2019BS 175/65R14 EP150 THI
2120BS 175/65R14 B25 INDO
2221BS 175/70R14 EP150 THI
2322BS 175/70R14 MY02 THI
2423BS 185/65R14 TEC THI
2524BS 185/65R14 150EZ INDO
2625BS 185/65R14 EP150 INDO
2726BS 195/70R14 150EZ INDO
2827BS 185/65R15 TC10 INDO
2928BS 185/65R15 T005 INDO
3029BS 185/65R15 T01 JAP
ENTERING


list.xlsm
ABCD
1ITEMBRANDTYPEORIGIN
21BS 175/70R13 B25 INDO
32BS 175/70R13 EP150THI
43BS 185/70R13 EP150INDO
54BS 175/65R14 EP150THI
65BS 175/65R14 B25INDO
76BS 175/70R14 EP150THI
87BS 175/70R14 MY02THI
98BS 185/65R14 TECTHI
109BS 185/65R14 150EZINDO
1110BS 185/65R14 EP150INDO
1211BS 195/70R14 EP150INDO
1312BS 185/65R15 TC10INDO
1413BS 185/65R16 T005 INDO
1514BS 185/65R15 T01JAP
1615BS 185/65R15 B250JAP
1716BS 195/60R15 AR20INDO
1817BS 195/60R15 EP150THI
1918BS 195/60R15 T001JAP
2019BS 195/60R15 150EZTHI
REPORT
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:D20Expression=COUNTIFS(ENTERING!$B:$B,TRIM($B2)&" "&TRIM($C2)&" "&TRIM($D2)=0)textNO
 
Upvote 0
You need to put the =0 at the very end of the formula, ie AFTER the final bracket.
 
Upvote 0
actually also I did it before posted last post .
but I no know where is the mistake . it's strange !!!
now it works .
thanks very much (y)
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
add specific character in the beginning of item into column
Replies
9
Views
616
Fluff
Fluff
A
  • Solved
rearranging columns by reversing in location and create headers
2 3
Replies
20
Views
756
Alex Blakenburg
A
A
  • Solved
replace item for range with item for Table based on column
2
Replies
13
Views
910
Legacy 143009
L
A
  • Question Question
delete unmatched item for two sheets with another two sheets and put the similar to adjacent
Replies
1
Views
414
abdo meghari
A
A
  • Question Question
insert column to auto-number for each item into two sheets
Replies
0
Views
301
abdo meghari
A

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top