Macro to compare the data between 2 files and generate the count

Hello Guys,

I need help in creating a Macro which compare the Customer ID’s present in Column H of Sample1 file with Column B of both Sample1 and Sample2 files.

The Count of Sample1 file should come in Column I and the count of Sample2 file should come in Column J of Sample1 file.

After above steps macro should automatically pick the lowest value (value should be greater than 0, if value is 0 than macro should consider it as blank) from Column I and J and paste it to Column K.

For more clarification please refer the attached sample files.

In Sample1 file I had also shown that how the data should look after running the Macro.

Thanks,
MG

Hello Manish, The same can be achieved with formula solution also take a look
in I2 copy paste below
=IF(H2="","",COUNTIF($B$2:$B$8,H2))

in J2 copy paste below
=IF(I2="","",COUNTIF([Sample2.xlsx]Sheet1!$B$2:$B$6,H2))

*change the range criteria according to your actual data

in K2 copy paste below then hold control and shift together and hit enter and release all three keys
=IF(H2="","",MIN(IF(I2:J2>0,I2:J2)))

drag all the formulas down.

select I2 to K8 go to conditional formatting ---> select new rule--> add a formula---> type =len($K2)>0
in format select yellow color.