PRP

Hello,

I want to match the same numbers from two Sheets.

Sheet1 have 10000 mobile numbers
and
Sheet2 got 4000 mobile numbers

Now, all the 4000 mobile numbers in sheet2 are in 10000 numbers from Sheet1
Can we know which mobile no. are matching from sheet1.

Is there any formula or code which will show the same number from sheet1

Please help,

Thanks in Advance,

ReeKorl

Are all of the numbers (the 10000) unique, or are there multiple instances of a particular number in that list of 10000? If they're all unique, it will be quite simple to look them up from the second page.

Assumptions: Sheet 1 is called Sheet1 in Excel. On sheet 1, you have a list of phone numbers in column A, from cell A1 to cell A10000. You have the information you want transferred onto sheet 2 in column B, alongside the numbers in column A. On sheet 2, you have the list of 4000 numbers in column A, from cell A1 to A4000. You want the information put alongside these numbers in column B.

The formula you need to put into column B on sheet 2 is as follows:

Enter this formula into cell B1 on sheet 2, auto fill it to the bottom of the phone numbers on the sheet, and you'll get the info you need.

You can customise the formula I've supplied to point it at the right places, but it should work perfectly.

Note: The phone numbers need to be in the same format, so if one is in the format 1800-800-8000 and on the other sheet it is 18008008000, it will not recognise them as being the same.

Brief explanation of the formula: INDEX is looking up a value that you want - in this case it's somewhere on Sheet1 in cells B1 through B10000. MATCH is telling it which value to look up, by referencing the value directly to the left of it on Sheet2, and finding it on Sheet1. The final 0 in the MATCH formula is saying do an exact find, so if the exact same value comes up, it takes it. If there are multiple values the same in Sheet1, it will take the first matching value it finds.

-EDIT

After reading over your problem again, it's possible I've misunderstood what you want... do you want to show on sheet 1 which numbers match with a number on sheet 2? If so, you can use the same type of formula to check this.

Put this formula in B1 on sheet 1 and it will return the phone number in Sheet2 which matches the one next to the one on sheet 1. You can go one step further and put an =IF in there too, as if it can't find the number it will return an #N/A error.

Therefore:

will bring up the word "Match" if the number is on both sheet 1 and sheet 2, or "No Match" if it is only on sheet 1.

Hope this helps (and that I haven't confused you too much!). If anything is too confusing, let me know and I'll make a sample spreadsheet for you to look at.

PRP

Hello, I understood the basic now,...It was not confusing but I learned something from u....

your 1st formula was okay to me,....

What I understand and got the result after the formula I entered is :
I got the match if there any from both the sheets which is perfect,...

Now what I need to find out is :
I want to delete the mobile numbers from sheet1 which is repeating in
sheet2

For eg:
Some of the numbers in sheet1 is repeated in Sheet2

Sheet1 Sheet2
7887670102 7887670103
7887670103 7887670104
7887670104 7887670109
7887670105 7887670110
7887670106 7887670115
7887670107 7887670116
7887670108 7887670117
7887670109
7887670110
7887670111
7887670112
7887670113
7887670114
7887670115
7887670116
7887670117
7887670118
7887670119
7887670120

Is there any way where I can remove the same repeated numbers from
Sheet1

Thanks in Advance,

ReeKorl

The best way to do this would be to flag the repeated numbers on sheet 1. The second method up there should do the job.

Once this is done, make sure each column has a title in the top cell, then click on one of them. Select Autofilter by going Data -> Filter -> Autofilter (or shortcut Alt+DFF) and some arrows will appear on the top row. Click on the arrow at the top of the column showing match or no match, and select Match. This will auto hide any columns which aren't on both pages.

Now, select all rows with data in, then right click and delete them. Click the filter arrow again and select (all) and it will show everything again, but this time it will only show ones which aren't on both sheets as you've just deleted the matching ones.

ALT+DFF again will turn off the autofilter, and you can also delete the header row now.

Hopefully this should do what you're after.

PRP

Hello,

I am going somewhere wrong I guess,....

When I put the following formula it shows Match which is fine. But when I drag down till my datas, it shows the same answer Match instead of "No Match" for some rows where numbers are different,

=IF(ISERR(INDEX(Sheet2!$A$1:$A$4000,MATCH(A1,Sheet2!$A$1:Sheet2!$A$4000,0))),"No Match","Match")


Secondly how do I do flag,....?

Sorry if its really simple but 'm still learning,.....

Thanks,...

ReeKorl

Oops, my bad. Should be ISNA rather than ISERR at the start in that formula. (the result dosn't actually return an 'error' as they define #N/A as a result rather than the error it actually is)

Flagging was just my way of saying you visibly display which match and which don't. Nothing technical, just how I described it.

Hope this sorts things out!

PRP

hEY mATE, gOT IT,.... i TRIED "ISNA" WHICH WORKED,....

tHANK YOU,...

cHEERS!!!

ReeKorl

No problem. Glad to have helped