Related:
- Using functions in excel, match, vlockup
- Search function vba excel - How-To - Excel
- How do i use an if function in excel for dates - How-To - Excel
- How to change enter key function in excel - How-To - Excel
- Maximum number of if functions in excel - How-To - Excel
- Excel match two columns and output third ✓ - Forum - Excel
7 replies
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
not clear.
in which column of second table did you put the match formula
match formula only give the row number where the firt argument occurs
read again help under "match"
in the second table what are those 6 and 4
little more explanations are required
where is the firsts list and second lits same sheet different sheets.
in which column of second table did you put the match formula
match formula only give the row number where the firt argument occurs
read again help under "match"
in the second table what are those 6 and 4
little more explanations are required
where is the firsts list and second lits same sheet different sheets.
viki
columns are on same sheet and i type formula in column C.
column A column B column C
112,11 555 4
131 131 2
547 777 #N/A
777 112,11 3
112,11 4
amount 112,11 exsists in column B only once in 4th line but match function gives me match for another same amount 112,11 as found in column B.
i need some function or any alike that will matc me number only once and not repearing that as match to other repeating amounts in column A.
Please if anyone can tell me how to match same numbers from two columns that are on same sheet wothout repeating one match.
Thanks a lot in front!!!
Pozz
column A column B column C
112,11 555 4
131 131 2
547 777 #N/A
777 112,11 3
112,11 4
amount 112,11 exsists in column B only once in 4th line but match function gives me match for another same amount 112,11 as found in column B.
i need some function or any alike that will matc me number only once and not repearing that as match to other repeating amounts in column A.
Please if anyone can tell me how to match same numbers from two columns that are on same sheet wothout repeating one match.
Thanks a lot in front!!!
Pozz
viki
here is once again how the columns and result look alike:
column A column B column C
112,11 555 4
131 131 2
547 777 #N/A
777 112,11 3
112,11 4
column A column B column C
112,11 555 4
131 131 2
547 777 #N/A
777 112,11 3
112,11 4
venkat1926
- Posts
- 1862
- Registration date
- Sunday June 14, 2009
- Status
- Contributor
- Last seen
- July 30, 2015
You should have given how you wanted the formula column to look like
THE COLUMN D gives according to your formula
the column E gives according to a new formula
do you want as it is in column E????????????
A B C D E
112,11 555 4 4 #N/A
131 131 2 2 2
547 777 #N/A #N/A 4
777 112,11 3 3 1
112,11 4 4 #N/A
THE COLUMN D gives according to your formula
the column E gives according to a new formula
=MATCH(B1,$A$1:$A$5,0)
do you want as it is in column E????????????
A B C D E
112,11 555 4 4 #N/A
131 131 2 2 2
547 777 #N/A #N/A 4
777 112,11 3 3 1
112,11 4 4 #N/A
viki
I ll explain better. I have two large columns (like 7000 numbers in each) and my task is to match equal numbers from column A with those in column B and column B with A. The best would be to use MATCh function first to compare A with B and than B with A and after that delete all numbers that have match. After that i ll get a list of those that have no match and that is it. I WOULD BE ABLE TO USE MATCH FUNCTION THE WAY I JUST EXPLAINED IT IF THERE ARE NO REPEATING NUMBERS IN COLUMN A OR B MORE THAN ONCE (first table under text) OR they repeat IN EQUAL NUMBER OF REPEATS (table two under text)BECAUSE IF THERE ARE REPEATS IN DIFFERENT NUMBER THAN I GET WRON RESULT (I WILL SHOW IT DOWN THERE in table three).
column A column B
111 222
222 555
333 333
444
777
or with equal number of repeatings (number 111 appers twice in both columns)
column A column B
111 333
222 222
333 777
111 111
444 111
666
Here in table three is explained my problem, for which I am searching the solution, and that is how to do matching that will match me numbers like fallows (I ll use small numbers so it is easier to track what i need):
NOTE: ALL COLUMNS ARE ON THE SAME SHEET AND IN COLUMN C, IN WHICH I COMPARE column A with B, I TYPE IN FIRST FREE CELL = MATCH(A2,$B$2:$B$6,0) AND I GET AS IT IS WRITTEN IN column IN THIS TABLE. AS YOU SEE IN COLUMN C NUMBER 1 FROM COLUMN A IS IN 5TH ROW IN COLUMN B, NUMBER 2 FROM COLUMN A IS IN 3RD ROW IN COLUMN B, NUMBER 3 FROM A DOESN APPERS IN COLUMN B, NUMBER 4 FROM A IS IN 4TH ROW IN COLUMN B, ANOTHER NUMER 1 FROM COLUMN A IS MATCH WITH NUMBER 1 IN 5TH ROW OF COLUMN B (AND HERE MY PROBLEMS STARTS) THAT HAS ALREADY BEEN A MATCH TO NUMBER 1 FROM FIRST ROW OF COLUMN A. MY PROBLEM IS THAT I GET WRONG PICTURE FROM COLUMN C NOW BECAUSE IT SHOWS ME LIKE EACH NUMBER 1 FROM COLUMN A HAS A PAIR IN COLUMN B. I COULD JUST SOLWE THIS BY SEEING HOW MANNY SAME NUMBER MATCHES WILL APPER WHEN I COMPARE COLUMN B (IN COLUMN C I HAVE THREE MATCHES MARKED AS 5 FOR NUMBER 1 FROM COLUMN A BUT IN COLUMN D IT MATCHES ME NUMBER 1 FROM COLUMN B ONLY ONCE WITH NUMBER MATCH 2 WHICH MEANS TWO OF THREE NUMBERS 1 FROM COLUMN A HAVE NO MATCH IN COLUMN B) AS IT IS SHOWN IN COLUMN D IN THIS TABLE. BUT I HAVE SO LARGE DATA THAT I CANT LOOK AT EACH ROW AND TRACK HOW MANNY NUBERS GOT SAME MATCH.
column A column B column C column D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 5 5
1 5
3 #N/A
I WOULD NEED IT TO BE SOMETHING LIKE I LL SHOW IN TABLE FOUR UNDER:
NOTE: COMUMN C COMPARES A WITH B AND WHEN IT MATCHES ME ONE NUMBER FROM COLUMN B I NEED IT NOT TO MATCH IT AGAIN WITH NUMBER IN COLUMN A.
COLUMN D COMPARES B WITH A.
column A column B column C column D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 #N/A #N/A
1 #N/A
3 #N/A
IF I MANAGE TO GET THIS AS IS SHOWN IN TABLE ABOVE I WOULD SOLWE MY PROBLEM. AS WE CAN SE FROM RESULTS IN COLUMN C AND D NUMBERS TWO NUMBERS 1 AND TWO NUMBERS 3 FROM COLUMN A DO NOT HAVE MATCH IN COLUMN B AND NUMBER 5 FROM COLUMN B HAS NO MATCH IN COLUMN A AND ALSO FROM TWO NUMBERS 4 FROM COLUMNB B ONE HAS NO MATCH IN COLUMN A, BECAUSE NUMBER 4 APPERS ONLY ONCE IN COLUMN A.
I DONT KNOW WHAT TYPE OF FORMULA GIVE EXCEL TO MANAGE THIS RESULT :( PLEASE IF SOMEONE KNOWS TO EXPLAIN ME I WOULD BE GREATEFUL!!!!!!
POZDRAV FROM CROATIA
column A column B
111 222
222 555
333 333
444
777
or with equal number of repeatings (number 111 appers twice in both columns)
column A column B
111 333
222 222
333 777
111 111
444 111
666
Here in table three is explained my problem, for which I am searching the solution, and that is how to do matching that will match me numbers like fallows (I ll use small numbers so it is easier to track what i need):
NOTE: ALL COLUMNS ARE ON THE SAME SHEET AND IN COLUMN C, IN WHICH I COMPARE column A with B, I TYPE IN FIRST FREE CELL = MATCH(A2,$B$2:$B$6,0) AND I GET AS IT IS WRITTEN IN column IN THIS TABLE. AS YOU SEE IN COLUMN C NUMBER 1 FROM COLUMN A IS IN 5TH ROW IN COLUMN B, NUMBER 2 FROM COLUMN A IS IN 3RD ROW IN COLUMN B, NUMBER 3 FROM A DOESN APPERS IN COLUMN B, NUMBER 4 FROM A IS IN 4TH ROW IN COLUMN B, ANOTHER NUMER 1 FROM COLUMN A IS MATCH WITH NUMBER 1 IN 5TH ROW OF COLUMN B (AND HERE MY PROBLEMS STARTS) THAT HAS ALREADY BEEN A MATCH TO NUMBER 1 FROM FIRST ROW OF COLUMN A. MY PROBLEM IS THAT I GET WRONG PICTURE FROM COLUMN C NOW BECAUSE IT SHOWS ME LIKE EACH NUMBER 1 FROM COLUMN A HAS A PAIR IN COLUMN B. I COULD JUST SOLWE THIS BY SEEING HOW MANNY SAME NUMBER MATCHES WILL APPER WHEN I COMPARE COLUMN B (IN COLUMN C I HAVE THREE MATCHES MARKED AS 5 FOR NUMBER 1 FROM COLUMN A BUT IN COLUMN D IT MATCHES ME NUMBER 1 FROM COLUMN B ONLY ONCE WITH NUMBER MATCH 2 WHICH MEANS TWO OF THREE NUMBERS 1 FROM COLUMN A HAVE NO MATCH IN COLUMN B) AS IT IS SHOWN IN COLUMN D IN THIS TABLE. BUT I HAVE SO LARGE DATA THAT I CANT LOOK AT EACH ROW AND TRACK HOW MANNY NUBERS GOT SAME MATCH.
column A column B column C column D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 5 5
1 5
3 #N/A
I WOULD NEED IT TO BE SOMETHING LIKE I LL SHOW IN TABLE FOUR UNDER:
NOTE: COMUMN C COMPARES A WITH B AND WHEN IT MATCHES ME ONE NUMBER FROM COLUMN B I NEED IT NOT TO MATCH IT AGAIN WITH NUMBER IN COLUMN A.
COLUMN D COMPARES B WITH A.
column A column B column C column D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 #N/A #N/A
1 #N/A
3 #N/A
IF I MANAGE TO GET THIS AS IS SHOWN IN TABLE ABOVE I WOULD SOLWE MY PROBLEM. AS WE CAN SE FROM RESULTS IN COLUMN C AND D NUMBERS TWO NUMBERS 1 AND TWO NUMBERS 3 FROM COLUMN A DO NOT HAVE MATCH IN COLUMN B AND NUMBER 5 FROM COLUMN B HAS NO MATCH IN COLUMN A AND ALSO FROM TWO NUMBERS 4 FROM COLUMNB B ONE HAS NO MATCH IN COLUMN A, BECAUSE NUMBER 4 APPERS ONLY ONCE IN COLUMN A.
I DONT KNOW WHAT TYPE OF FORMULA GIVE EXCEL TO MANAGE THIS RESULT :( PLEASE IF SOMEONE KNOWS TO EXPLAIN ME I WOULD BE GREATEFUL!!!!!!
POZDRAV FROM CROATIA
viki
SORRY FOR MESS IN TABLES WBOVE SO I PASTE THEM AGAIN :P
TABLE 1)
column A column B
111 222
222 555
333 333
444
777
TABLE 2)
column A column B
111 333
222 222
333 777
111 111
444 111
666
TABLE 3)
A B C D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 5 5
1 5
3 #N/A
TABLE 4)
A B C D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 #N/A #N/A
1 #N/A
3 #N/A
TABLE 1)
column A column B
111 222
222 555
333 333
444
777
TABLE 2)
column A column B
111 333
222 222
333 777
111 111
444 111
666
TABLE 3)
A B C D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 5 5
1 5
3 #N/A
TABLE 4)
A B C D
1 5 5 #N/A
2 2 3 3
3 4 #N/A 5
4 1 4 2
1 4 #N/A #N/A
1 #N/A
3 #N/A
Subscribe To Our Newsletter!
