Find same numbers on first sheets column a in the second Sheet column a, if found, put a "Y" in column c on first sheet

[midiyuga]

help. find same numbers on first sheets column a in the second Sheet column a, if found, put a "Y" in column c on first sheet

Thanks so much!

 

[Asbestos_Jen]

Try this:

2024-08-20.xlsx
	A	B	C
1	NO SOCIO	NOMBRE	On Sheet2
2	1879670	Juan y Enedina Marroquin
3	909504	Israel y Maria Govea
4	3868009	Carlos y Esther Hernandez
5	834317	Sergio y Olga Alvarez
6	1890500	Mayolo y Wendy Rubio	Y
7	2123045	Aristeo Martinez
8	3018496	Mario Perez
9	2325225	Eloisa Alcantar
10	1905702	Arturo Martinez
11	4900219	Luis y Esther Aguilar	Y
12	3872883	Jose y Angelica Aleman
13	4553624	Sotero y Reyna Arzate
14	3399685	Martin e Idalid Perez
15	1748145	Alberto y Cruz Avila	Y
16	5690679	Eusebio y Laurentina Leal
17	215168	Samuel y Azalia Castaneda
18	5189133	Manuel y Angelica Castro
19	199784	Jesus y Josefina Cervantes
20	982906	Carlos y Ofelina Chalico
21	4971390	Elvira H. Contreras
22	3122140	Maynor y Linda Del Cid	Y
23	4203207	Delfino y Elpidia Duran
24	4938885	Helio y Alicia Gonzalez
25	4602904	Oscar y Perla Gonzalez
26	6497945	Antonio y Sara Hernandez
27	2769101	Martin y Norma Ibarra	Y
28	4562584	Jose y Gloria Mendez
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=IF(COUNTIF(Sheet2!$A:$A,Sheet1!A2),"Y","")

2024-08-20.xlsx
	A
1	NO SOCIO
2	4900219
3	1890500
4	1748145
5	3122140
6	2769101
Sheet2

 

[midiyuga]

It works! thanks so much! will it be much to ask to put a "N" (on sheet 1 column c) when a specific number is not found on both sheets?

 

[Asbestos_Jen]

It works! thanks so much! will it be much to ask to put a "N" (on sheet 1 column c) when a specific number is not found on both sheets?

You just need to change the false condition to "N" like so:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!A2),"Y","N")

 

[midiyuga]

You just need to change the false condition to "N" like so:

=IF(COUNTIF(Sheet2!$A:$A,Sheet1!A2),"Y","N")

Thanks SO much!

 

[Peter_SSs]

I would strongly suggest that you alter those formulas by removing the sheet name 'Sheet1' from them. It is a bad idea to use the sheet name of the sheet the formula is on in a formula on that sheet.
To demonstrate, below I have the post #4 formula in column C and the very same formula in column D but with the Sheet1 name reference removed. Both formulas correctly identify the items that are on sheet 2 (which I have manually coloured green.

Sample2.xlsm
	A	B	C	D
1	NO SOCIO	NOMBRE	On Sheet2	On Sheet2
2	1879670	Juan y Enedina Marroquin	N	N
3	909504	Israel y Maria Govea	N	N
4	3868009	Carlos y Esther Hernandez	N	N
5	834317	Sergio y Olga Alvarez	N	N
6	1890500	Mayolo y Wendy Rubio	Y	Y
7	2123045	Aristeo Martinez	N	N
8	3018496	Mario Perez	N	N
9	2325225	Eloisa Alcantar	N	N
10	1905702	Arturo Martinez	N	N
11	4900219	Luis y Esther Aguilar	Y	Y
12	3872883	Jose y Angelica Aleman	N	N
13	4553624	Sotero y Reyna Arzate	N	N
14	3399685	Martin e Idalid Perez	N	N
15	1748145	Alberto y Cruz Avila	Y	Y
16	5690679	Eusebio y Laurentina Leal	N	N
17	215168	Samuel y Azalia Castaneda	N	N
18	5189133	Manuel y Angelica Castro	N	N
19	199784	Jesus y Josefina Cervantes	N	N
20	982906	Carlos y Ofelina Chalico	N	N
21	4971390	Elvira H. Contreras	N	N
22	3122140	Maynor y Linda Del Cid	Y	Y
23	4203207	Delfino y Elpidia Duran	N	N
24	4938885	Helio y Alicia Gonzalez	N	N
25	4602904	Oscar y Perla Gonzalez	N	N
26	6497945	Antonio y Sara Hernandez	N	N
27	2769101	Martin y Norma Ibarra	Y	Y
28	4562584	Jose y Gloria Mendez	N	N
Sheet1

Cell Formulas
Range	Formula
C2:C28	C2	=IF(COUNTIF(Sheet2!$A:$A,Sheet1!A2),"Y","N")
D2:D28	D2	=IF(COUNTIF(Sheet2!$A:$A,A2),"Y","N")

Now suppose that I now decide that I want this data sorted by the values in column A, so I do that by selecting A1:D28 and then using the Sort option on the Data ribbon tab. The result is below.
You can see that the column C formula now shows 4 of the 5 rows that are on Sheet2 incorrectly with an "N" (red) and another 4 rows that are not on Sheet2 incorrectly with a "Y" (orange).
The column D formula still shows the correct results.

Sample2.xlsm
	A	B	C	D
1	NO SOCIO	NOMBRE	On Sheet2	On Sheet2
2	199784	Jesus y Josefina Cervantes	N	N
3	215168	Samuel y Azalia Castaneda	N	N
4	834317	Sergio y Olga Alvarez	N	N
5	909504	Israel y Maria Govea	N	N
6	982906	Carlos y Ofelina Chalico	N	N
7	1748145	Alberto y Cruz Avila	Y	Y
8	1879670	Juan y Enedina Marroquin	N	N
9	1890500	Mayolo y Wendy Rubio	N	Y
10	1905702	Arturo Martinez	N	N
11	2123045	Aristeo Martinez	Y	N
12	2325225	Eloisa Alcantar	Y	N
13	2769101	Martin y Norma Ibarra	N	Y
14	3018496	Mario Perez	N	N
15	3122140	Maynor y Linda Del Cid	N	Y
16	3399685	Martin e Idalid Perez	N	N
17	3868009	Carlos y Esther Hernandez	N	N
18	3872883	Jose y Angelica Aleman	N	N
19	4203207	Delfino y Elpidia Duran	Y	N
20	4553624	Sotero y Reyna Arzate	Y	N
21	4562584	Jose y Gloria Mendez	N	N
22	4602904	Oscar y Perla Gonzalez	N	N
23	4900219	Luis y Esther Aguilar	N	Y
24	4938885	Helio y Alicia Gonzalez	N	N
25	4971390	Elvira H. Contreras	N	N
26	5189133	Manuel y Angelica Castro	N	N
27	5690679	Eusebio y Laurentina Leal	N	N
28	6497945	Antonio y Sara Hernandez	N	N
Sheet1

 

[Asbestos_Jen]

Thanks for the tip, @Peter_SSs.