Conditional Formatting in two sets of columns

K

KMJM2019

New Member
Joined
Jan 2, 2019
Messages
10
Hello,

I am trying to figure out how to use conditional formatting to verify the data set in columns A through F match the data in columns H through M. I have tried setting up rules, and it's worked for everything except when someone accidentally reverses data that is very close. For example, line C3 says 202A on the left side, but says 203B in line J3. Line C4 says 203B, and J4 says 202A. I need this to flag as differing, but with the rules I set up, there is a 202A and 203B on both sides of the data set, so it doesn't see it as a difference. How do I make sure these are flagged as differing using conditional formatting? I can't use If/Then statements, it needs to be something a little easier for the end user I am setting this up for. There is already a very complex macro ahead of this sorting that I don't want to touch, so I'm not sure how to add on another macro to the already processed data, and frankly I don't want to. Is there a way to use conditional formatting to flag these differences? Thank you!!!

[TABLE="class: grid, width: 754, align: left"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD="align: right"]200[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD="align: right"]200[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD="align: right"]201[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD="align: right"]201[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD]202A[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD]203B[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD]203B[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]WMT[/TD]
[TD]202A[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]TGT[/TD]
[TD="align: right"]204[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]TGT[/TD]
[TD="align: right"]204[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]TGT[/TD]
[TD="align: right"]205[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]TGT[/TD]
[TD="align: right"]205[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]206[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]206[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]207[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]207[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]208[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]SBUX[/TD]
[TD="align: right"]208[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]209[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]209[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]210[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]210[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
[TR]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]211[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[TD] [/TD]
[TD="align: right"]1/14/2019[/TD]
[TD]TYCO[/TD]
[TD="align: right"]211[/TD]
[TD]3137865B7[/TD]
[TD="align: right"]25000[/TD]
[TD="align: right"]32318.45[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi,

If you mean using Excel's Native CF function...

You can concatenate the Cell Values A1:F1 and compare to H1:M1, or use SUMPRODUCT.

CF, use formula...enter formula as shown below in O1
Change <> to = if you want to reverse the logic:


Book1
ABCDEFGHIJKLMNO
11/14/2019WMT2003137865B72500032318.451/14/2019WMT2003137865B72500032318.45FALSE
21/14/2019WMT2013137865B72500032318.451/14/2019WMT2013137865B72500032318.45FALSE
31/14/2019WMT202A3137865B73000032318.451/14/2019WMT203B3137865B73000032318.45TRUE
41/14/2019WMT203B3137865B72500032318.451/14/2019WMT202A3137865B72500032318.45TRUE
51/14/2019TGT2043137865B72500032318.451/14/2019TGT2043137865B72500032318.45FALSE
61/14/2019TGT2053137865B72500032318.451/14/2019TGT2053137865B72500032318.45FALSE
71/14/2019SBUX2063137865B72500032318.451/14/2019SBUX2063137865B72500032318.45FALSE
81/14/2019SBUX2073137865B72500032318.451/14/2019SBUX2073137865B72500032318.45FALSE
91/14/2019SBUX2083137865B72500032318.451/14/2019SBUX2083137865B72500032318.45FALSE
101/14/2019TYCO2093137865B72500032318.451/14/2019TYCO2093137865B72500032318.45FALSE
111/14/2019TYCO2103137865B72500032318.451/14/2019TYCO2103137865B72500032318.45FALSE
121/14/2019TYCO2113137865B72500032318.451/14/2019TYCO2113137865B72500032318.45FALSE
Sheet464
Cell Formulas
RangeFormula
O1=SUMPRODUCT(--($A1:$F1=$H1:$M1))<>6
 
Upvote 0
On a copy of your data sheet

CLEAR ALL PREVIOUS Conditional Formatting from your table.

Now, Highlight the range C1:C12

While C1:C12 is selected go (on the Menu) to the Conditional Formatting, Create Rule
Select Use a Formula and in the Formula Textbox enter (with cell C1 as the Activecell) =NOT(EXACT($C1,$J1))

Then Select a Fill Color

OK, OK, out

Cells C3 and C4 should be COLORED...
 
Last edited:
Upvote 0
That definitely works, so thank you for that! I'd like it to go a bit further though, where it will flag the cell with a color to show exactly what doesn't match. There can be hundreds of lines daily, so making it easy to pick out exactly what doesn't match is the goal. Is a formula unavoidable in this scenario because the values in each range aren't unique? Is there a way to highlight the differences by row or column rather than range without having to go row by row? Thank you for your help, I really appreciate it! This forum is amazing.
 
Upvote 0
KMJM2019 said:
I am trying to figure out how to use conditional formatting to verify the data set in columns A through F match the data in columns H through M.
Click to expand...

Glad Jim gave you what you want, but you had said you wanted to compare A:F with H:M as stated in your OP, which is what I gave you, whereas, Jim's formula only compares Column C and Column J.
 
Last edited:
Upvote 0
Another related question: How you can single out 2 columns of different length? For example, I need to compare a list of numbers in column F with column O for differences. A data set has 6 columns (A-F), that I need to compare to 6 different columns (J-O). Using conditional formatting, how can I compare the two columns F and O when they have differing amounts of rows?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
418
rahildhody
R
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
368
harzer
H
M
  • Solved
Number of unique daily clock-ins
Replies
3
Views
553
marshak
M
C
  • Question Question
Excel Formula to keep records of drawn cards
Replies
3
Views
281
Cquake
C
W
  • Solved
How many rows between each data point in a column
Replies
3
Views
550
Fluff
Fluff

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,633
Latest member
DougMo

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