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]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[nianchi111]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

microsoft Excel for microsoft 365 MSO (16.0.13801.21072) 32 bit

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C
1	Name	From	To
2	Vimal	India	USA
3	Vimal	Canada	Iran
4	Vimal	Iran	Mexico
5	Kumar	Canada	Mexico
6	Kumar	Mexico	USA
7	Kumar	USA	Canada
8	Kumar	India	Pakistan
Sheet2

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1		USA	Canada	Iran	Mexico	Pakistan	India
2	Vimal	1	1	1	1	0	1
3	Kumar	1	1	0	1	1	1
Sheet1

Cell Formulas
Range	Formula
B2:G3	B2	=--(COUNT(FILTER(ROW(Sheet2!$A$2:$A$10),(Sheet2!$A$2:$A$10=$A2)*((Sheet2!$B$2:$B$10=B$1)+(Sheet2!$C$2:$C$10=B$1))))>0)

 

[nianchi111]

Thank you So much !!! This work perfectly. You have saved me. Thanks you so much.....

 

[nianchi111]

Sorry to bother you again, I have 20K + names in sheet1 and when pasting this formula in all the cells its file is getting corrupted.

Is there any way to solve this please.

 

[Fluff]

In what way is it getting corrupted?

 

[nianchi111]

it says loading and now I'm unable to open that file.

i think it still running in the background as I'm unable to open any of the excel files

 

[Fluff]

In that case your only other option would be VBA, but that won't update automatically.

 

[nianchi111]

Thank you, is there a way to do this in VBA please