asked on

Search Values in Excel worksheet

Search Values in Excel worksheet

I have 2 Excel worksheets, they are the same. I mean same column names

on Worksheet 2 I would like to compare each row based on Column A with all rows of worksheet 1 and see if it exists

exmaple:
worksheet1 has a column A

Column A

awcds
mkhgc
fgtyol
etc..

on Worksheet column A
hgbnht
pppp
ggtg
mkhgc
jhtyu

I want the code to grab the first data of column A in worksheet 2 and look it up on Worksheet 1 Column A if it finds it it will write for instance on column P of worksheet 2 how many times it found that Data in Column A Worksheet 1

Thank you

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

Try below:

Sub Compare_N_Count()
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim LRow1 As Long, LRow2 As Long
Set Sh1 = Worksheets("Sheet1")
Set Sh2 = Worksheets("Sheet2")
LRow1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
LRow2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Sh2.Range("P2:P" & LRow2).FormulaR1C1 = "=COUNTIF(Sheet1!R2C1:R[" & LRow1 & "]C1,RC1)"
Sh2.Range("P2:P" & LRow2).Value = Sh2.Range("P2:P" & LRow2).Value 'You may delete this line if you don't want formula to be displayed
Application.ScreenUpdating = False
End Sub

Open in new window

Rob Henson

See attached file using COUNTIF function on sheet2.

Will there be duplicates in the values on Sheet2? Do you want the count to appear more than once if there are?
Countif-Function.xlsx

Rob Henson

If you were putting the count of values in the next column, ie column B rather than column P, you would be able to make use of a mouse shortcut to populate down column B.

Enter the formula in the first cell where needed in column B and then move the mouse cursor over the bottom right corner of that cell. It will change to a plus sign when at the corner, double click here and it will fill down as far as required based on the population of column A.

jskfan

ASKER

Thank you Guys..
I will try it later