Find duplicates in column pairs??

[squirrellydw]

Is there an easy way find duplicate names the show up in different columns? The tricky part is it would have to cover columns in pairs for example if John Doe is listed in column B and K, or C and L, or D and M, or E and N, or F and O, or G and P, or H and Q. I have it working now but I had to make a rule for each column? Just trying to find an easier / better way.

 

[squirrellydw]

I just used CF to fill in any blank white, looks like its working

 

[Peter_SSs]

t if there is a blank cell it's filling in red,

I don't think those cells are are completely blank. Perhaps they have a formula returning "" (or previously had a formula returning "" and cells have had values pasted)


In any case ..

is there away to have it ignore blank cells so they are not filled in?

.. 'Yes'

Change the CF formula to this and you won't need the extra 'white' conditional formatting.

=AND(MATCH(B2,OFFSET(B2,-ROW(B2)+1,9-18*(COLUMN(B2)>9),100),0),B2<>"")

 

[squirrellydw]

That worked, thank you. What the best way to check if they have a formula in the cells, I thought I deleted all them.

 

[squirrellydw]

I don't think those cells are are completely blank. Perhaps they have a formula returning "" (or previously had a formula returning "" and cells have had values pasted)


In any case ..

.. 'Yes'

Change the CF formula to this and you won't need the extra 'white' conditional formatting.

=AND(MATCH(B2,OFFSET(B2,-ROW(B2)+1,9-18*(COLUMN(B2)>9),100),0),B2<>"")

My bad, that didn't work. it fixed the blanks but now none of the duplicates show in red

 

[Peter_SSs]

Firstly, note that I mentioned that the cells may not have a formula but may have had one in the past and removed by Copy/Paste Special (Values)

You can check what cells have formula by selecting a range of cells, pressing F5 -> Special .. -> Formulas -> OK
Only cells with formulas in the originally selected range will now be selected.

it fixed the blanks but now none of the duplicates show in red

Hmm, I'm not sure why that happened, Try writing the formula this way.

25 02 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		LH	KO	LZ	UX	VD	AE	UR			UX	SX	FO	ZS	DG	ME	KN
3		XX	XQ	IY	ZQ	KF	PO	JL			SF	JL	CS	UV	BE	NU	NA
4		YV	FE	QI	QC	XB	FW	GD			KX	NS	VH	IO	VK	NF	JL
5		PV	IO	RO	PL	LQ	VU	CP			ZS	IB	RF	JI	SX	CY	GX
6		WY	IB	SJ	QB	HA	XK	WR			UN	YX	KX	BU	WQ	KT	BE
7		FZ	CI	YP	EO	NC	CW	FJ			BN	TJ	QV	QF	VW	GC	IG
8		PS	QM	EV	KD	IQ	ZS	QY			GM	FT	CM	YW	ZI	PO	RE
9			IJ	DA	YM	RB	ML	AM			DR	WS	MO	XC	YC	PO	CR
10			WO	XV	OG	NA	AI	SG			LO	CK	OI	QJ	NG	PO	ES
11			UB	TP	SC	QU	JI	RH			OS	ZM	HK	HK	LD	PO	LO
12		WN	ZO	SS	JS	FA	IE	WT				VK	JM	XT	KT	MJ	SZ
13		PA	AK	FD	UU	TU	XO	QY				FP	QF	IR	VN	MK	HG
14		IS	OV	BV	VI	NP	TG	NZ				FY	DT	RI	JS	NU	DD
15		GP	HF	GV	MD	YH	QJ	GK				XJ	MY	TB	AN	SE	PO
16		CM	LL	XS	FG	TY	WX	FL			SC	MI	VH	ZQ	KE	XO	VB
17		YF	NP	BX	VO	NJ	OC	ZZ			JF	WB	DC	CD	UO	JE	JZ
18		FF	HG	LH	JE	PE	AB	EY			HA	FP	GP	ES	JR	FD	RO
19		PA	DU	UK	GJ	PZ	KP	TM			MS	KO	TE	IB	VL	GY	QY
20		SY	DY	VC	WP	HP	QL	FB			UX	QQ	OK	BL	XX	MZ	US
CF Dupes (3)

Cell Formulas
Range	Formula
B11,K13:K14	B11	=IF(1=2,5,"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:Q20	Expression	=MATCH(B2,OFFSET(B2,-ROW(B2)+1,9-18*(COLUMN(B2)>9),100),0)*(B2<>"")	text	NO

 

[squirrellydw]

that looks like it fixed it, thank you again. you are awesome!!

 

[Peter_SSs]

that looks like it fixed it, thank you again.

Cheers. Thanks for the confirmation.

 

[djclements]

@Peter_SSs Personally, I thought your first suggestion using relative column references was the best one. The same concept can also be used with one rule; plus, the length of the lookup range can be reduced with Trim Refs (pending release to all channels). For example, with cell B2 selected (active):

Formula: =IF(B2<>"",IF(COLUMN()<COLUMN($K$2),MATCH(B2,K:.K,0),MATCH(B2,XEW:.XEW,0)))
Applies to: =$B$2:$H$100,$K$2:$Q$100

Note: =IF(B2<>"",MATCH(B2,IF(COLUMN()<COLUMN($K$2),K:.K,XEW:.XEW),0)) is shorter, but will match 0's with blank cells when used in Conditional Formatting.

While column XEW is technically 16,375 columns to the right of column B, it's also 9 columns to the left of column B (relatively speaking). When the formula is applied to column K, it will be referencing column B.

Cheers!

 

[Peter_SSs]

@djclements
Thank you for that additional information. I was not even aware of Trim refs (or TRIMRANGE) but they look interesting.

However, I am not understanding how it is working. In the mini sheet below, it works in conditional formatting applied across the columns shown, but not as a worksheet formula applied across those columns. What am I missing?

Although I just copied the B5 formula down and across, the formulas change when the hit column J. Not only does the #REF! appear in the formula (not unexpected but where it appears is unexpected) but the whole structure & order of the formula has changed. I have not seen anything like that before.

25 02 03.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2		LH	KO	LZ	UX	VD	AE	UR			UX	SX	FO	ZS	DG	ME	KN
3		UX	XQ	IY	ZQ	KF	PO	JL			SF	JL	CS	UV	BE	NU	NA
4
5		#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	FALSE	FALSE	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
6		2	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	FALSE	FALSE	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
CF Dupes (4)

Cell Formulas
Range	Formula
B5:I6	B5	=IF(B2<>"",IF(COLUMN()<COLUMN($K$2),MATCH(B2,K:.K,0),MATCH(B2,XEW:.XEW,0)))
J5:Q6	J5	=J2<>"" IF(COLUMN()<COLUMN($K$2),IF(MATCH(J2,S:.S,0),J2,MATCH(#REF!,,0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:Q3	Expression	=IF(B2<>"",IF(COLUMN()<COLUMN($K$2),MATCH(B2,K:.K,0),MATCH(B2,XEW:.XEW,0)))	text	NO

 

[djclements]

@Peter_SSs
This behavior of relative column references starting over at column A, after reaching the last column (XFD), does not apply when using a standard range reference directly in the worksheet. However, it will work in Conditional Formatting, as well as in Name Manager. For example, if you were to select cell B2 and define a name called "Col9ToLeft" that Refers to: =!XEW:.XEW, when you select cell K2 afterwards and go back into Name Manager, you will see the definition now Refers to: =!B:.B.

After doing so, =IF(B2<>"",IF(COLUMN()<COLUMN($K$2),MATCH(B2,K:.K,0),MATCH(B2,Col9ToLeft,0))) will work, BUT it will create a circular reference when dragged across to columns K thru Q in your mini-sheet example, because the formulas used in rows 5 and 6 will be included in the lookup range, due to the use of Trim Refs, and will be cross-referencing each other. For testing purposes, just change K:.K to K$2:K$3 and it will work as expected.