Comparing Data

[MCR007]

I have an Excel worksheet with a list of construction components (i.e a moulding) each with a unique ID. For instance "NT_03.12". The column is around 1,500 items long.

Each component has a drawing that a works contractor has drawn. I have this in workbook 2. The data is from an online document register. The works contractor names their drawing title slightly different but always contains the unique ID instance "corner moulding NT_03.12" this column is around 1,200 items long.

I would like to cross compare the two workbooks to see what data is missing (which drawings I do not have).

Example:

Component	Drawing Title
NT_03.12	Special SP_06.22
NT_03.14	Straight Moulding NT_03.12
NT_02.11	Corner Moulding NT_03.12
NX_03.01	Vertical moulding SW_09.17
SW_09.17	Oversized SP_06.22
SP_06.22
TT_03.45

I want to be able to show/ highlight between the two worksheets the drawings that are missing, with TT_03.45 & NX_03.01 in the above example missing.

I have tired to do some research and use conditional formatting with "use an formula to determine which cells to format" but it doesn't work as the information does not line up in the rows and the cell data does not contain the exact same information.

Any guidance on how to resolve would be greatly appreciated.

 

[JamesCanale]

MrExcelPlayground16.xlsx
	A	B	C
2	Component		Drawing Title
3	NT_03.12		Special SP_06.22
4	NT_03.14		Straight Moulding NT_03.12
5	NT_02.11		Corner Moulding NT_03.12
6	NX_03.01		Vertical moulding SW_09.17
7	SW_09.17		Oversized SP_06.22
8	SP_06.22
9	TT_03.45
Sheet8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A9	Expression	=NOT(OR(ISNUMBER(SEARCH(A3,$C$3:$C$7))))	text	NO

 

[MCR007]

Thank you, think i'm going wrong somewhere.

 

[JamesCanale]

The a2:a8 should just be a2. It will 'drag down' the rest of the way. The b2:b8 should be b$2:b$8.

So:
=NOT(OR(ISNUMBER(SEARCH(A2,B$2:B$8))))

 

[MCR007]

The a2:a8 should just be a2. It will 'drag down' the rest of the way. The b2:b8 should be b$2:b$8.

So:
=NOT(OR(ISNUMBER(SEARCH(A2,B$2:B$8))))

This has been so helpful! Thanks! I have now been able to do this between worksheets as per the below.

One last thing I need to figure out if you can please assist.

I need to do a count if on the items below if that colour?

This will give me the number of drawings missing as well as being able to visually identify them (I know I can use the filter but would like to demonstrate it at the bottom).

 

[JamesCanale]

The formula gets a bit convoluted:

MrExcelPlayground16.xlsx
	A	B	C
2	Component		Drawing Title
3	NT_03.12		Special SP_06.22
4	NT_03.14		Straight Moulding NT_03.12
5	NT_02.11		Corner Moulding NT_03.12
6	NX_03.01		Vertical moulding SW_09.17
7	SW_09.17		Oversized SP_06.22
8	SP_06.22
9	TT_03.45
10
11	4	Missing
Sheet8

Cell Formulas
Range	Formula
A11	A11	=LET(a,--NOT(ISNUMBER(SEARCH(A3:A9,TRANSPOSE(C3:C7)))),b, COLUMNS(a),c,SEQUENCE(b,1,1,0),d,MMULT(a,c)=b,SUM(--d))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:A9	Expression	=NOT(OR(ISNUMBER(SEARCH(A3,$C$3:$C$7))))	text	NO