VBA find differences in 2 worksheets

[weissihm]

I have two large worksheets. One was run a couple months ago and the other was run this week. I'm looking for VBA to find the differences between the worksheets. Each worksheet is set up the same, but the data may not be in the same order, and may not have the exact same items.

Spreadsheets have about 60,000 rows and 50 columns.

Small set of Original data

INMID

INMDesc

INMCat

INMUnit

INMAltEntryConvFact

INMAltEntryUOM

INMForceSalesEntry

INMComment

INMFarmerTax

INMLastCost

INMLastVendor

INMMinimum

INMNoAnonymous

INMPrice1

258E-07

GREEN ENGRAVED

3113

EA

False​

Y

0.8467​

889810

0.0000​

False​

2.0500​

258E-03

NECK TAG ENGRAVED WHITE TAG W/ BLACK #

3050

EA

0.0000​

False​

Y

1.1011​

889810

0.0000​

False​

2.5900​

1

BURNS 1' HYD HOSE

3240

FT

0.0000​

False​

Y

9.25​

605210

0.0000​

False​

8.9900​

07.05114

PUMP 07.05114 -DI

3050

EA

False​

Y

0.0000​

0.0000​

False​

232.9500​

07.06772

VENTIER PURPLE -DI

3050

EA

False​

Y

0.0000​

0.0000​

False​

8.2000​

07.06773

TURBO DROP BLUE

3050

EA

0.0000​

False​

Y

0.0000​

0.0000​

False​

13.2900​

07.06839

SPRAYER PART

3240

EA

False​

Y

0.0000​

0.0000​

False​

2.5500​

07.06967

BUSHING

3050

EA

False​

Y

0.0000​

0.0000​

False​

4.9500​

07.06995

SPRAYER PART

3050

EA

False​

Y

0.0000​

0.0000​

False​

3.1900​

Updated Data

INMID

INMDesc

INMDesc

INMCat

INMUnit

INMAltEntryConvFact

INMAltEntryUOM

INMForceSalesEntry

INMComment

INMFarmerTax

INMLastCost

INMLastVendor

INMMinimum

INMNoAnonymous

INMPrice1

258E-07

GREEN ENGRAVED NECK TAG

GREEN ENGRAVED NECK TAG

3113

EACH

False​

Y

0.8467​

889810

0.0000​

False​

2.0500​

258E-03

NECK TAG ENGRAVED WHITE TAG W/ BLACK #

NECK TAG ENGRAVED WHITE TAG W/ BLACK #

3050

EACH

0.0000​

False​

Y

1.1011​

889810

0.0000​

False​

2.5900​

1

BURNS 1' HYD HOSE

BURNS 1' HYD HOSE

3240

FOOT

0.0000​

False​

Y

9.25​

605210

0.0000​

False​

8.9900​

07.05114

PUMP 07.05114 -DI

PUMP 07.05114 -DI

3050

EACH

False​

Y

0.0000​

0.0000​

False​

232.9500​

07.06772

VENTIER PURPLE -DI

VENTIER PURPLE -DI

3050

EACH

False​

Y

0.0000​

0.0000​

False​

8.2000​

07.06773

TURBO DROP BLUE

TURBO DROP BLUE

3050

EACH

0.0000​

False​

Y

0.0000​

0.0000​

False​

13.2900​

07.06839

SPRAYER PART

SPRAYER PART

3240

EACH

False​

Y

0.0000​

0.0000​

False​

2.5500​

07.06967

BUSHING

BUSHING

3050

EACH

False​

Y

0.0000​

0.0000​

False​

4.9500​

07.06995

SPRAYER PART

SPRAYER PART

3050

EACH

False​

Y

0.0000​

0.0000​

False​

3.1900​

I highlighted a few of the differences on the updated data, in red as a way to show what I need to find.

I don't care if one of the existing worksheets highlights the differences or if the differences are dumped to another sheet, I just need to be able find the differences.

I am on Windows 10, Microsoft 365.

Thank you in advance.

 

[Zot]

There must be one or several references as comparison variable.
Is it just the INMID column need to be matched? Comparing List1 to List2 will find what is in List1 not available in List1 but if you compare List2 to List1, the result is different.

 

[weissihm]

There must be one or several references as comparison variable.
Is it just the INMID column need to be matched? Comparing List1 to List2 will find what is in List1 not available in List1 but if you compare List2 to List1, the result is different.

Sorry, I didn't specify that. INMID is the column that needs to be matched. I want to compare List 2 to List 1 to see what changed since List 1 was ran first.

 

[Zot]

Your column titles are also not in sync (not same sequence)? 50 columns instead of 14 shown in top table? You have two column with same title in 2nd table?

 

[weissihm]

I'm not sure why the second column of named INMDesc is showing, it is not on my original set of data. Must have been something with the copy and paste. The reason I only show 14 columns is to give a sample of what I'm doing. Some of the spreadsheets I need to compare are less than 50 columns. I was able to get XL2BB to work, so I included a sample of the data, showing all of the columns. You will see on the Updated Data file the items in red text are changes that I'm looking for the VBA to find and distinguish for me.

Background on this: We are moving to a new software and these are our items. I have already sent a list of items to the software company to get into the system, however as we get closer to go live, I need to provide the software company updated data showing any changes that have taken place since the original data was sent.

Original Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE
1	INMID	INMDesc	INMCat	INMUnit	INMAltEntryConvFact	INMAltEntryUOM	INMForceSalesEntry	INMComment	INMFarmerTax	INMLastCost	INMLastVendor	INMMinimum	INMNoAnonymous	INMPrice1	INMPrice2	INMPrice3	INMPrice4	INMQtyBrk2	INMQtyBrk3	INMQtyBrk4	INMPriceSuggRetail	INMReminder	INMDiscontinued	INMReplacementItemID	INMSaleComment	INMSerial	INMServiceRevenue	INMStatus	INMTaxable	INMType	INMVendor1	INMWeight	INMWorkInstructions	INMCompanion	INMCompanionMatch	INMFractPrice	INMFuel	INMOilEnergyType	INMRestrFuel	INMDensity	INMLiquidDensity	INMApplicationRate	INMAppRateUOM	INMBlendConvFact	INMBlendUOM	INMBushelWeight	INMCDMSID	INMChemicalKit	INMComplexFert	INMEPANumber	INMEPATier2	INMGrainItem	INMLot	INMManufacturer	INMMFItem	INMMSDS	INMPhosphate	INMPotash	INMPrecisionAg	INMPrecisionStdConv	INMPrecisionStdUOM	INMRestrChem	INMSulphur	INMTonnageTax	INMUreaNitrogen	INMAssembly	INMAOBuildType	INMAOMarkupPriceTon	INMIngredient	INMInScaleWeight	INMMarketCost	INMMedicatedFeed	INMMixerItemID	INMNewPrice1	INMNewPrice2	INMNewPrice3	INMNewPrice4	INMNewQtyBrk2	INMNewQtyBrk3	INMNewQtyBrk4	INMP1Factor	INMP1Factor2	INMP1Input	INMP1Model	INMP1Operator	INMP1Operator2	INMP2Factor	INMP2Factor2	INMP2Input	INMP2Model	INMP2Operator	INMP2Operator2	INMP3Factor	INMP3Factor2	INMP3Input	INMP3Model	INMP3Operator	INMP3Operator2	INMP4Factor	INMP4Factor2	INMP4Input	INMP4Model	INMP4Operator	INMP4Operator2	INMPriceAtMarket	INMPriceRule	INMUseBOMFuturePrice	INMVFDRequired	INMWEMScaleID
2	258E-07	GREEN ENGRAVED	3113	EA			False		Y	0.8467	889810	0.0000	False	2.0500									False			False	False	A	True	I	889810	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
3	258E-03	NECK TAG ENGRAVED WHITE TAG W/ BLACK #	3050	EA	0.0000		False		Y	1.1011	889810	0.0000	False	2.5900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	020790	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
4	1	BURNS 1' HYD HOSE	3240	FT	0.0000		False		Y	9.5131	605210	0.0000	False	8.9900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	212990	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
5	07.05114	PUMP 07.05114 -DI	3050	EA			False		Y	0.0000		0.0000	False	232.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
6	07.06772	VENTIER PURPLE -DI	3050	EA			False		Y	0.0000		0.0000	False	8.2000									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
7	07.06773	TURBO DROP BLUE	3050	EA	0.0000		False		Y	0.0000		0.0000	False	13.2900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
8	07.06839	SPRAYER PART	3240	EA			False		Y	0.0000		0.0000	False	2.5500									False			False	False	A	True	I	212990	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	False	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
9	07.06967	BUSHING	3050	EA			False		Y	0.0000		0.0000	False	4.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
10	07.06995	SPRAYER PART	3050	EA			False		Y	0.0000		0.0000	False	3.1900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
11	07.07144	TIP	3050	EA			False		Y	0.0000		0.0000	False	4.2200									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
12	07.07342	SPRAYER PART	3050	EA			False		Y	0.0000		0.0000	False	56.4900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
13	07.07963	HANDLE	3050	EA			False		Y	0.0000		0.0000	False	13.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
14	07.08041	SPRAYER PART	3050	EA			False		Y	0.0000		0.0000	False	1.1000									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
15	07.08189	1.5 CAP POLY -DI	3050	EA			False		Y	0.0000		0.0000	False	12.6900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
16	07.09541	CREEN 07.09541	3050	EA			False		Y	0.0000		0.0000	False	39.0000									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
17	07.10256	BUSHING 07.10256	3050	EA			False		Y	0.0000		0.0000	False	3.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
18	07.10260	BUSHING	3050	EA			False		Y	0.0000		0.0000	False	4.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
19	07.10276	BUSHING 07.10276	3050	EA			False		Y	2.9100	802730	0.0000	False	6.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
20	07.10279	SPRAYER PART	3050	EA			False		Y	0.0000		0.0000	False	0.8900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
Original

Updated Data

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE
1	INMID	INMDesc	INMCat	INMUnit	INMAltEntryConvFact	INMAltEntryUOM	INMForceSalesEntry	INMComment	INMFarmerTax	INMLastCost	INMLastVendor	INMMinimum	INMNoAnonymous	INMPrice1	INMPrice2	INMPrice3	INMPrice4	INMQtyBrk2	INMQtyBrk3	INMQtyBrk4	INMPriceSuggRetail	INMReminder	INMDiscontinued	INMReplacementItemID	INMSaleComment	INMSerial	INMServiceRevenue	INMStatus	INMTaxable	INMType	INMVendor1	INMWeight	INMWorkInstructions	INMCompanion	INMCompanionMatch	INMFractPrice	INMFuel	INMOilEnergyType	INMRestrFuel	INMDensity	INMLiquidDensity	INMApplicationRate	INMAppRateUOM	INMBlendConvFact	INMBlendUOM	INMBushelWeight	INMCDMSID	INMChemicalKit	INMComplexFert	INMEPANumber	INMEPATier2	INMGrainItem	INMLot	INMManufacturer	INMMFItem	INMMSDS	INMPhosphate	INMPotash	INMPrecisionAg	INMPrecisionStdConv	INMPrecisionStdUOM	INMRestrChem	INMSulphur	INMTonnageTax	INMUreaNitrogen	INMAssembly	INMAOBuildType	INMAOMarkupPriceTon	INMIngredient	INMInScaleWeight	INMMarketCost	INMMedicatedFeed	INMMixerItemID	INMNewPrice1	INMNewPrice2	INMNewPrice3	INMNewPrice4	INMNewQtyBrk2	INMNewQtyBrk3	INMNewQtyBrk4	INMP1Factor	INMP1Factor2	INMP1Input	INMP1Model	INMP1Operator	INMP1Operator2	INMP2Factor	INMP2Factor2	INMP2Input	INMP2Model	INMP2Operator	INMP2Operator2	INMP3Factor	INMP3Factor2	INMP3Input	INMP3Model	INMP3Operator	INMP3Operator2	INMP4Factor	INMP4Factor2	INMP4Input	INMP4Model	INMP4Operator	INMP4Operator2	INMPriceAtMarket	INMPriceRule	INMUseBOMFuturePrice	INMVFDRequired	INMWEMScaleID
2	258E-07	GREEN ENGRAVED NECK TAG	3113	EACH			False		Y	0.8467	889810	0.0000	False	2.0500									False			False	False	A	True	I	889810	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
3	258E-03	NECK TAG ENGRAVED WHITE TAG W/ BLACK #	3050	EACH	0.0000		False		Y	1.1011	889810	0.0000	False	2.5900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	020790	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
4	1	BURNS 1' HYD HOSE	3240	FOOT	0.0000		False		Y	9.2500	605210	0.0000	False	8.9900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	212990	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
5	07.05114	PUMP 07.05114 -DI	3050	EACH			False		Y	0.0000		0.0000	False	232.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
6	07.06772	VENTIER PURPLE -DI	3050	EACH			False		Y	0.0000		0.0000	False	8.2000									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
7	07.06773	TURBO DROP BLUE	3050	EACH	0.0000		False		Y	0.0000		0.0000	False	13.2900	0.0000	0.0000	0.0000	0	0	0	0.0000		False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000	0.0000	L	0.0000		0		False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0	0.0000	0	True	0.0000	False		0.0000	0.0000	0.0000	0.0000	0	0	0	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
8	07.06839	SPRAYER PART	3240	EACH			False		Y	0.0000		0.0000	False	2.5500									False			False	False	A	True	I	212990	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	False	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
9	07.06967	BUSHING	3050	EACH			False		Y	0.0000		0.0000	False	4.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
10	07.06995	SPRAYER PART	3050	EACH			False		Y	0.0000		0.0000	False	3.1900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
11	07.07144	TIP	3050	EACH			False		Y	0.0000		0.0000	False	4.2200									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
12	07.07342	SPRAYER PART	3050	EACH			False		Y	0.0000		0.0000	False	56.4900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
13	07.07963	HANDLE	3050	EACH			False		Y	0.0000		0.0000	False	13.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
14	07.08041	SPRAYER PART	3050	EACH			False		Y	0.0000		0.0000	False	1.1000									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
15	07.08189	1.5" CAP POLY -DI	3050	EACH			False		Y	0.0000		0.0000	False	12.6900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
16	07.09541	CREEN 07.09541	3050	EACH			False		Y	0.0000		0.0000	False	41.9900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
17	07.10256	BUSHING 07.10256	3050	EACH			False		Y	0.0000		0.0000	False	3.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
18	07.10260	BUSHING	3050	EACH			False		Y	0.0000		0.0000	False	4.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
19	07.10276	BUSHING 07.10276	3050	EACH			False		Y	2.9100	802730	0.0000	False	6.9500									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
20	07.10279	SPRAYER PART	3050	EACH			False		Y	0.0000		0.0000	False	0.8900									False			False	False	A	True	I	006058	0.0000		False	True	False	False		False	0	0.0000		L					False	False		False	False	0				0.0000	0.0000	False	1.0000	O	False	0.0000	0	0.0000	False	0		0	True	0.0000	False									0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0.0000	0.0000	N	N	/	+	0	M	False	0
Updated

 

[Zot]

You have huge data here. I'm suing Dictionary object to loop faster and once found match, I deleted the matched item so the looping will become less and less and get faster towards the end.
For column D, EA and EACH, FT and FOOT are not the same and so I just don't bother about them. This go to 100 columns but you can change in this line

For nCol = 2 To 100

See if this code works

Sub Compare()

Dim nCol&, eRow1&, eRow2&
Dim StartTime#, SecondsElapsed#
Dim key1, key2, INMID1, INMID2
Dim rngINMID1 As Range, rngINMID2 As Range
Dim DictINMID1 As Object, DictINMID2 As Object
Dim ws1 As Worksheet, ws2 As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

StartTime = Timer

' Change sheetname accodingly if not Sheet1 and Sheet2
Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

' Create Dictionary for each INMID
Set DictINMID1 = CreateObject("Scripting.Dictionary")
Set DictINMID2 = CreateObject("Scripting.Dictionary")

' Fine last row of lists
eRow1 = ws1.Range("A1").End(xlDown).Row
eRow2 = ws2.Range("A1").End(xlDown).Row

' Define range for each INMID
Set rngINMID1 = ws1.Range("A2", "a" & eRow1)
Set rngINMID2 = ws2.Range("A2", "a" & eRow2)

' Load data into INMID Dictionary
For Each INMID1 In rngINMID1
    DictINMID1.Add INMID1.Value, INMID1.Row
Next
For Each INMID2 In rngINMID2
    DictINMID2.Add INMID2.Value, INMID2.Row
Next

' Loop through Dictionaries values
For Each key1 In DictINMID1
    For Each key2 In DictINMID2
        If key1 = key2 Then
            ' Shade every 3rd row for ease of visualization
            If DictINMID2(key2) Mod 3 = 1 Then
                ws2.Range("A" & DictINMID2(key2), "BC" & DictINMID2(key2)).Interior.ColorIndex = 15
            End If
            ' Mark difference for remaining columns (49 columns) in the row where INMIDs matched
            For nCol = 2 To 100
                If Not ws1.Cells(DictINMID1(key1), nCol) = ws2.Cells(DictINMID2(key2), nCol) Then
                    ws2.Cells(DictINMID2(key2), nCol).Font.ColorIndex = 3
                End If
            Next
            ' Removing the matched key2 from list and exit loop
            DictINMID2.Remove key2
            Exit For
        End If
    Next
Next

'Determine how many seconds code took to run
SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
  
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[weissihm]

I tried your code on the small set of data, after I purposely made more changes and it seems to work perfectly! This will save me a ton of time. I can't thank you enough. AWESOME!!!

 

[Zot]

I tried your code on the small set of data, after I purposely made more changes and it seems to work perfectly! This will save me a ton of time. I can't thank you enough. AWESOME!!!

Good to hear it works. Thanks for update.... and yes I expected you to made modification to your requirement. Highlight columns and marked columns are not the same either ?