DATA MATCHING

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,
i'm having the data containing six months share markets whole sale deal. it is having around 1163 scripts. for the same scripts i have retail deal for the same duration six months. data as follows

WHOLE SALE DEAL
DATE SCRIPT NAME PRICE
1/2/2009 A 23
2/3/2009 A 34
2/4/2009 B 76

RETAIL DEAL
DATE SCRIPT NAME PRICE
1/2/2009 A 23
2/2/2009 A 34
3/2/2009 A 25

Like wise its avail for all 1163 scripts

what i want is.. the retail script name should be matched upon with the respective dates as on the whole sale deal, rest of the data should be elimnated.

what i have to do?

pls help me

thank you..........



1 reply

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
all the data are in sheet - sheet1-like this

see this ulr

https://authentification.site/files/22649013/ashok.xls

see the formula in D3 which is copied down and also the formula in I3 which is also copied down.
to be safe the sheet is copied in sheet 2 also

now try this macro (which is the vbeditor )

=========
Sub test()
Dim r As Range, c As Range, cfind As Range, x As String
Dim r1 As Range
Worksheets("sheet1").Activate
Set r = Range(Range("I3"), Range("I3").End(xlDown))
Set r1 = Range(Range("d3"), Range("d3").End(xlDown))
For Each c In r
x = c.Value
Set cfind = r1.Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then
Range(c.Offset(0, -3), c).Clear
End If
Next c

End Sub

======================