Hi Satvik,
MATCH is faster than vlookup.
If your master file is unsorted, here are my best suggestions:
1. =IF(ISERROR(MATCH($A2,'[master
file.xlsx]Sheet1'!$A$2:$A$11,0)),"Non Callable","Callable")
2. {=IF(ISERROR(MATCH($A2:$A11,'[master
file.xlsx]Sheet1'!$A$2:$A$11,0)),"Non Callable","Callable")}
Option 2 is a multi-cell array formula that should be a bit faster than
option 1 and will also make your file a little smaller. Enter the formula
in A2 of your DNC sheet, making sure the range $A2:$A11 references every row
in your DNC sheet. Highlight the Callable column all the way down to the
last row and then confirm your multi-cell array formula by hitting
ctrl-shift-enter. If you take this route the DNC sheet will not be sortable
and it will be hard to delete rows due to the formula spanning all those
rows. That's the downside and the benefit may be only slight.
If your master file is sorted, you can use these versions that should be
even faster:
1. =IF(INDEX('[master file.xlsx]Sheet1'!$A$2:$A$11,MATCH($A2,'[master
file.xlsx]Sheet1'!$A$2:$A$11))=$A2,"Callable","Non Callable")
2. {=IF(INDEX('[master
file.xlsx]Sheet1'!$A$2:$A$11,MATCH($A2:$A11,'[master
file.xlsx]Sheet1'!$A$2:$A$11))=$A2:$A11,"Callable","Non Callable")}
Notice that for an unsorted master file, there is no need to include INDEX
in the formula. All you need to know is if the tel. no. is in the file.
Match determines that and it's position. Index would do unneeded work to
rerieve the number.
I use Index in the formula for a sorted list, because although Match is
blazing fast with a sorted list, it will not return an error if the number
is not found, so we need to verify that it found the correct number.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Satvik
Sent: Friday, March 02, 2012 5:49 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Urgent Help Required...!
Shaik,
As off now I'm doing the same logic. Where as my data volume is too
high....! Is there any other logic to map the same...
Regards,
Satvik
Sent from BlackBerryR on Airtel
_____
From: Shaik Waheed <waheedb...@gmail.com>
Sender: excel-macros@googlegroups.com
Date: Fri, 2 Mar 2012 20:43:19 +0530
To: <excel-macros@googlegroups.com>
ReplyTo: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Urgent Help Required...!
Hi,
You can use vlookup to find the callable # from DNC sheet. Find the attached
file for ur reference.
On Sat, Mar 3, 2012 at 6:59 AM, Satvik <satvik...@gmail.com> wrote:
Thanks for the replay Shaik...
Yes I need to check with DNC..
Sent from BlackBerryR on Airtel
_____
From: Shaik Waheed <waheedb...@gmail.com>
Sender: excel-macros@googlegroups.com
Date: Fri, 2 Mar 2012 18:11:44 +0530
To: <excel-macros@googlegroups.com>
ReplyTo: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Urgent Help Required...!
Hi,
Do you want to chek whether the master file ph.# are callable or
non-callable by verying it with NDNC sheet?
On Sat, Mar 3, 2012 at 4:16 AM, Satvik <satvik...@gmail.com> wrote:
Hi Every One,
Friend I need a big help from you all, as I work in BPO company as a MIS
executive but I am not aware about Access or SQL. But every day I need to
scrub the NDNC data which I receive it from my client side.. Volume of a
data will be around 5 to 6 lake. Where I get NDNC scrubbed file from the
NDNC link using this file I need to map the NDNC status to my Master file.
Will any one help me to do my work easier and do my other work @ the same
time. Attached the sample files...
Sample 1 - master file &
Sample 2 - NDNC file.
Regards,
Satvik
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
