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