Search sheet 1 and update the summar in sheet 2

[nianchi111]

HI All,

I have 2 sheets in an excel.

take the first name in the sheet 1 and search in sheet 2 and based on the country from and to update in the "1" in the sheet 1 under the respective contries.

In sheet 2 there would be multiple entries so it has to search for all the data and update in sheet 1, once this is done it has to take the next name is sheet 1 and do the same.


In sheet 1 I want a summary of this like below

	USA	Canada	Iran	Mexico	Pakistan	India
Vimal	1​	1​	1​	1​		1​
Kumar	1​	1​		1​	1​	1​

Sheet 2 - has the list of data of people who travelled to different countries

Name	From	To
Vimal	India	USA
Vimal	Canada	Iran
Vimal	Iran	Mexico
Kumar	Canada	Mexico
Kumar	Mexico	USA
Kumar	USA	Canada
Kumar	India	Pakistan

Thanks,
Vimal Vikraman

 

[Fluff]

There is, but I don't have time at the moment.
If nobody else jumps in, I'll have a look tomorrow.

 

[nianchi111]

There is, but I don't have time at the moment.
If nobody else jumps in, I'll have a look tomorrow.

Thank you so much for the time you spent for me !!!

 

[Fluff]

Ok, how about

Sub nianchi()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long
   
   Ary = Sheets("Sheet2").Range("A1").CurrentRegion.Value2
   Nary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   
   With CreateObject("scripting.dictionary")
      For r = 2 To UBound(Ary)
         .Item(Ary(r, 1)) = .Item(Ary(r, 1)) & "|" & Ary(r, 2) & "|" & Ary(r, 3)
      Next r
      For r = 2 To UBound(Nary)
         For c = 2 To UBound(Nary, 2)
            If InStr(1, .Item(Nary(r, 1)), Nary(1, c), vbTextCompare) Then Nary(r, c) = 1
         Next c
      Next r
   End With
   Sheets("Sheet1").Range("A1").CurrentRegion.Value = Nary
End Sub

 

[nianchi111]

Thnak you so much.. you are a life savior !!!!

 

[Fluff]

You're welcome & thanks for the feedback.