Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel comparision [Solved/Closed]

Report
-
 Nitesh -
hello,
can any one help me solve the following comparison problem..


a(reference data) b(raw data) c(status)

ab xc
bc cb
cd ab match
ef pq
gh cd match
ij zx
kl qr
hj
rh
bc match
kl match


pls.. help to solve this problem of excel.

nitesh


See more

1 reply

Answer 1 / 1
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi NITESH,

Your query is unclear to me.

I quess you want to compare your reference data with your raw data and when there is a match, then enter match in the third column.
What would you consider a match?

Please explain in greater detail.

Best regards,
Trowa
View all 9 comments
Report

sorry to bother u again but when i run this code it run but it is unable to find all match.for example if there is 3 match in raw data of same value then it is shows match only for one .there also some more prob.but this one is main..
thanks again
Report
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi Nitesh,

To implement this code:
Open VB editor (ALT+F11).
Goto top menu, insert, module.
Paste the code in the big white field.

To run the code:
Hit ALT+F8 to open a window which displays the available macro's.

I don't understand the issue which you describe in your last post.
If data looks like:
Reference Raw
ab ab
cd ab
ab xs
gf ab

Then result will be:
Reference Raw
ab ab Match
cd ab Match
ab xs
gf ab Match
In this example there are 3 matches which are all found.

Can you give an example which the code doesn't handle correctly?

Best regards,
Trowa
Report

sorry i was wrong and your code is working fine. Can we add some thing in it.For example if a raw data is in form of a long text in each cell and then compare the reference data to find the match..
reference data(sheet1)----------------raw data(sheet2)---------------sheet 3
English--------------------------------thank you Trowa------------------
Russia--------------------------------he cannot speak English------match or say (English)


thanks again
Report
Posts
2691
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 14, 2021
 454
Hi Nitesh,

Try the following code: 
Sub test()
Dim lRow, lRow2 As Integer
lRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet1").Range("A1:A" & lRow)
    With Sheets("Sheet2").Range("D1:D" & lRow2)
        Set c = .Find(cell, LookIn:=xlValues)
        If Not c Is Nothing Then
        firstAddress = c.Address
            Do
                Sheets("Sheet3").Cells(c.Row, "A") = "Match"
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
Next cell

End Sub

Best regards,
Trowa
Report

HI
thank u so much for giving your time to my question..
i really appreciate you!!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!