If a number in one column appears in another
Closed
Sherri
-
Dec 2, 2009 at 01:15 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 6, 2010 at 08:39 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Apr 6, 2010 at 08:39 PM
Related:
- If a number in one column appears in another
- Display two columns in data validation list but return only one - Guide
- How to delete column in word - Guide
- Tweetdeck expand column - Guide
- Excel count occurrences of string in column - Guide
- What symbol typically appears to the left of a secure site? - Guide
4 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 2, 2009 at 08:22 PM
Dec 2, 2009 at 08:22 PM
suppose your data is like this in column A and B from row no 1
hdng1 hendg2
4 8
13 7
18 8
8 19
17 19
9 18
18 20
7 14
16 14
in C2 copy paste this formula
you will get like this (this will get where it is located)
hdng1 hendg2
4 8 a4
13 7 a8
18 8 a4
8 19 #N/A
17 19 #N/A
9 18 a3
18 20 #N/A
7 14 #N/A
16 14 #N/A
N/A means the number in column B of the same row is not available in column A
I have designed for only 10 rows you extend it to as many rows as you need.
dollar signs are important.
if you want only yes or no answers the formula will be
please confirm whether thsis is ok
hdng1 hendg2
4 8
13 7
18 8
8 19
17 19
9 18
18 20
7 14
16 14
in C2 copy paste this formula
="a"&MATCH(B2,$A$2:$A$10,0)
you will get like this (this will get where it is located)
hdng1 hendg2
4 8 a4
13 7 a8
18 8 a4
8 19 #N/A
17 19 #N/A
9 18 a3
18 20 #N/A
7 14 #N/A
16 14 #N/A
N/A means the number in column B of the same row is not available in column A
I have designed for only 10 rows you extend it to as many rows as you need.
dollar signs are important.
if you want only yes or no answers the formula will be
=IF(ISNUMBER(MATCH(B2,$A$2:$A$10,0)),"yes","no")
please confirm whether thsis is ok
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 2, 2009 at 08:29 PM
Dec 2, 2009 at 08:29 PM
sorry I forgot to add
copy C2 down. easy way of doing this is
take the cursor to the right bottom of cell C2 where the symbol turns to +
then click that +
copy C2 down. easy way of doing this is
take the cursor to the right bottom of cell C2 where the symbol turns to +
then click that +
I have two columns of numbers. I would like to determine the unique numbers in one column. What is the formula please
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Apr 6, 2010 at 08:39 PM
Apr 6, 2010 at 08:39 PM
this can be done by using advanced filter. but if you need a formla try thi.
suppose data is like this from A1 to A8
1
2
3
2
1
1
2
3
in B1 copy this formula
=IF(COUNTIF(A1:$A$8,A1)=1,A1,"")
note dollar signs.
copy B1 down . you will get in B1 to B8
1
2
3
suppose data is like this from A1 to A8
1
2
3
2
1
1
2
3
in B1 copy this formula
=IF(COUNTIF(A1:$A$8,A1)=1,A1,"")
note dollar signs.
copy B1 down . you will get in B1 to B8
1
2
3
