Duplicate cells across multiple sheets

[kristell]

Hello - can you help please?

I have a spreadsheet with 4 tabs. In Sheet 4 I have a list of 'names' - col c rows 2-1760

I have three other sheets and want to know if anything in that column appears in any of the other sheets (probably in one of two columns) and if it does, highlight the cell in Sheet 4 Col C Red.

What would be the best way to do this? I am struggling with COUNTIF or Conditional Formatting and what I have done so far is not bringing anything back.

Thanking you in advance.

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of Sheet4 and one of the other 3 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[kristell]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of Sheet4 and one of the other 3 sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

That is difficult as it has sensitive information and is a work piece which due to GDPR I can't share.

I will try and cleanse it a bit and upload.

 

[awoohaw]

Here is an example on one sheet, just change the references to include sheet names:

Mr excel questions 58.xlsm
	A	B	C	D	E	F	G	H	I
1
2	Sheet1		Sheet2		Sheet3		Sheet4		CF Test
3	KAB		APQ		AYM		JII		FALSE
4	IVG		JTK		LGD		HVO		FALSE
5	FYC		REG		DOM		PKI		TRUE
6	PKI		TEY		XHA		DFP		FALSE
7	BYI		NEW		BGR		EDI		TRUE
8	EDI		ENX		GBO		RPR		TRUE
9	XSO		FOH		AJQ		NCV		FALSE
10	QON		RPR		KXO		KLV		TRUE
11	PYR		IFD		FHD		GCL		FALSE
12	POW		VSS		KLV		SGN		FALSE
Sheet1

Cell Formulas
Range	Formula
I3:I12	I3	=NOT(ISNA(MATCH(G3,VSTACK($A$3:$A$12,$C$3:$C$12,$E$3:$E$12),0)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3:G12	Expression	=NOT(ISNA(MATCH(G3,VSTACK($A$3:$A$12,$C$3:$C$12,$E$3:$E$12),0)))	text	NO

 

[kristell]

Hopefully this will work: spreadsheet (Help please.xlsx)

So the tab Other active searches - do any of the items in Column C appear in the other three sheets - SystemDashboards | Adult Profile Sec Lvl | Summary

 

[mumps]

It appears that you want to search both columns K and L in the "SystemDashboards" sheet. Is this correct? Will the data to search always be in these two columns in this sheet? Sheets "Adult Profile Sec Lvl" and "Summary" appear not to have any data to search. In which columns in these two sheets would the data to search be located?

 

[kristell]

Yes L & N in SystemDashboards
K in Adult Profile
L in Summary

I have mucked up the columns a bit but if you can help with this I should be able to work out different columns if I need them.

That is why I thought it might be easier for it to check the 'whole' of the other sheets?

 

[awoohaw]

try this as your CF rule beginning on second row of column C. (It is the same structure as the formula I have in POST #4)

=NOT(ISNA(MATCH(C2,VSTACK(SystemDashboards!$L$2:$L$100,SystemDashboards!$N$2:$N$100,' Adult Profile Sec Lvl'!$K$1:$K$100,Summary!$L$1:$L$100),0)))

 

[kristell]

That certainly has brought up some matches - I appreciate your support. Thank you.

 

[awoohaw]

My pleasure, Best Wishes!
- K