Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel
Previous Topic Next Topic

Using functions in excel, match, vlockup [Closed]

Report
-
 viki -
Hello,

I have two columns with numbers and i need to find the numbers in 1st column that apear in 2nd and vice versa. But my problem is next: when I use MATCH function or Vlookup that it allow repeating matches. To explain better it looks like fallows:

Before using functions

AB
13
25
32
11
18
58
8

When I use Match or Vlookup i get this:

ABC-I typed =match(A1,$B$1:$B$6,0)
164
253
32#N/A
114
184
582
8 5

I would need it to match me numbers without repeating certian matches (in this case number 1 when it matches it once- because this waay it turns out like all numbers 1 from colum A have match in column B although in column B 1 appear once.

I hope I managed to explain it well. I appologize for any mistakes in language (Im from Croatia :P)! Thanks a lot!

Pozz from Croatia



7 replies

Answer 1 / 7
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 798
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.
Answer 2 / 7

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
Answer 3 / 7

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
Answer 4 / 7
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
 798
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 

=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
Answer 5 / 7

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
Answer 6 / 7

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
Answer 7 / 7

Can not make nice spaces between columns :(, PLEASE give efort to understand. THANK YOU!
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!