Conditional Formatting in two sets of columns

[KMJM2019]

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

1/14/2019	WMT	200	3137865B7	25000	32318.45		1/14/2019	WMT	200	3137865B7	25000	32318.45
1/14/2019	WMT	201	3137865B7	25000	32318.45		1/14/2019	WMT	201	3137865B7	25000	32318.45
1/14/2019	WMT	202A	3137865B7	30000	32318.45		1/14/2019	WMT	203B	3137865B7	30000	32318.45
1/14/2019	WMT	203B	3137865B7	25000	32318.45		1/14/2019	WMT	202A	3137865B7	25000	32318.45
1/14/2019	TGT	204	3137865B7	25000	32318.45		1/14/2019	TGT	204	3137865B7	25000	32318.45
1/14/2019	TGT	205	3137865B7	25000	32318.45		1/14/2019	TGT	205	3137865B7	25000	32318.45
1/14/2019	SBUX	206	3137865B7	25000	32318.45		1/14/2019	SBUX	206	3137865B7	25000	32318.45
1/14/2019	SBUX	207	3137865B7	25000	32318.45		1/14/2019	SBUX	207	3137865B7	25000	32318.45
1/14/2019	SBUX	208	3137865B7	25000	32318.45		1/14/2019	SBUX	208	3137865B7	25000	32318.45
1/14/2019	TYCO	209	3137865B7	25000	32318.45		1/14/2019	TYCO	209	3137865B7	25000	32318.45
1/14/2019	TYCO	210	3137865B7	25000	32318.45		1/14/2019	TYCO	210	3137865B7	25000	32318.45
1/14/2019	TYCO	211	3137865B7	25000	32318.45		1/14/2019	TYCO	211	3137865B7	25000	32318.45

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[jtakw]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1/14/2019	WMT	200	3137865B7	25000	32318.45		1/14/2019	WMT	200	3137865B7	25000	32318.45		FALSE
2	1/14/2019	WMT	201	3137865B7	25000	32318.45		1/14/2019	WMT	201	3137865B7	25000	32318.45		FALSE
3	1/14/2019	WMT	202A	3137865B7	30000	32318.45		1/14/2019	WMT	203B	3137865B7	30000	32318.45		TRUE
4	1/14/2019	WMT	203B	3137865B7	25000	32318.45		1/14/2019	WMT	202A	3137865B7	25000	32318.45		TRUE
5	1/14/2019	TGT	204	3137865B7	25000	32318.45		1/14/2019	TGT	204	3137865B7	25000	32318.45		FALSE
6	1/14/2019	TGT	205	3137865B7	25000	32318.45		1/14/2019	TGT	205	3137865B7	25000	32318.45		FALSE
7	1/14/2019	SBUX	206	3137865B7	25000	32318.45		1/14/2019	SBUX	206	3137865B7	25000	32318.45		FALSE
8	1/14/2019	SBUX	207	3137865B7	25000	32318.45		1/14/2019	SBUX	207	3137865B7	25000	32318.45		FALSE
9	1/14/2019	SBUX	208	3137865B7	25000	32318.45		1/14/2019	SBUX	208	3137865B7	25000	32318.45		FALSE
10	1/14/2019	TYCO	209	3137865B7	25000	32318.45		1/14/2019	TYCO	209	3137865B7	25000	32318.45		FALSE
11	1/14/2019	TYCO	210	3137865B7	25000	32318.45		1/14/2019	TYCO	210	3137865B7	25000	32318.45		FALSE
12	1/14/2019	TYCO	211	3137865B7	25000	32318.45		1/14/2019	TYCO	211	3137865B7	25000	32318.45		FALSE
Sheet464

Cell Formulas
Range	Formula
O1	=SUMPRODUCT(--($A1:$F1=$H1:$M1))<>6

 

[jim may]

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

 

[KMJM2019]

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.

 

[KMJM2019]

Thank you Jim, that's exactly what I'm looking for!!! I really appreciate it!!

 

[jtakw]

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.

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.

 

[KMJM2019]

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?