+ Reply to Thread
Results 1 to 14 of 14

Micro for Searching Data from One FILE and perform action if the Data is Found.

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Question Micro for Searching Data from One FILE and perform action if the Data is Found.

    Hi Guys you can take me as New in Micros but heard Micros do lots of magic stuffs in Office 2007.
    Well I am into eCommerce Business and need to print lots of Label but first have to search for Serviceable Pincode for Different Courier Service.
    I use 3 Couriers Service "DTDC" , "ARAMEX" & "DHL-Bluedart".
    Hence have 3 Excel File with List of PINCODE (Country Code) which i have to search individually in each Courier Service File and return back to my Label Data and assign which Service to use for each labels.

    What i am exactly looking for is some micro in which i type in the pincode (6 digit for India) and the pincode is searched into 3 excel file which contains the list of pincode if the pincode is available it should return that file name. If the pincode is available in all 3 excel file should display all 3 names so i come to know that this pincode is serviceable with all 3 courier service.

    Or we can also do like make 1 excel file with 3 sheet of 3 couriers, somthing like that if it works..

    If the above stuff sounds confusing please let me know i can explain in detail as well...
    hoping to hear from you guys soon...

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Do you have a sample file that you can upload?

    Also, having 1 workbook with 3 sheets is a better option than 3 workbooks.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Well dont have option to upload the file.
    But its simple Pincode List as under :

    Bluedart List:

    PINCODE ZONE T T CAREADESC
    799001 B 48 hrs AGARTALA
    799002 B 48 hrs AGARTALA
    799003 B 48 hrs AGARTALA
    799004 B 48 hrs AGARTALA
    799005 B 48 hrs AGARTALA
    799006 B 48 hrs AGARTALA
    799014 B 48 hrs AGARTALA
    796001 B 120 hrs AIZAWL
    796005 B 120 hrs AIZAWL
    Huge List with more then 2000 Pincodes........................!!!!!!

    Aramex List :

    Pin Code OLD Name of Location State Name Zone
    600001 MAA Chennai Tamil Nadu A
    600002 MAA Chennai Tamil Nadu A
    600003 MAA Chennai Tamil Nadu A
    600004 MAA Chennai Tamil Nadu A
    600005 MAA Chennai Tamil Nadu A
    600006 MAA Chennai Tamil Nadu A
    600007 MAA Chennai Tamil Nadu A
    600008 MAA Chennai Tamil Nadu A
    600009 MAA Chennai Tamil Nadu A
    600010 MAA Chennai Tamil Nadu A
    600011 MAA Chennai Tamil Nadu B
    600012 MAA Chennai Tamil Nadu B
    Huge List with more then 2000 Pincodes........................!!!!!!

    Same is for DTDC also...

    Below is my Label Data File which i download on Daily Basis with List of Orders with Buyers Details including PINCODE, so i have to check in each excel file to find which courier service is available and accordingly i assign the Label with Courier Service Name and Barcode (AirWayBill Number).

    My Label Data File as under :

    Courier AWB Buyer Full name Buyer Pin Code
    Aramex 6218610064 Bamanendra Narang 452011
    Aramex 6218610053 Arunkumar Karunakaran 560016
    Aramex 6218610042 Anand Singh 110062
    DTDC 6218610031 mohd mastan 500040
    DTDC 6218610020 Jacob Varghese 400103
    Aramex 6218610016 dharini jatania 560048
    Aramex 6218610005 Ajay Pillai 400614
    Bluedart 6218609994 YES Bank Limited 400018
    Bluedart 6218609983 Soumik Saha 400093
    Aramex 6218609972 abhishek bahukhandi 110064
    Aramex 6218609961 Logeswari K 600081

    For Example we take first record with pincode "452011".
    What i do right now is copy the pincode click FIND and go in each Courier List excel file and search, if Bluedart has the PIncode in its list i Type "BLUEDART" in COURIER Column in LABELS excel File.
    Hope you got what i meant the first 2 columns in Label excel files are manually typed by me after searching the pincode in each courier excel files.

  4. #4
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Question Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    waiting for any replies.... need help badly plzz.

  5. #5
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Guys had heard lots of positive reviews about the forum but no reply for my query from last 3 days
    is the query soo simple that no one want to reply......... guysss please helpp me out...... i tried Vlookup as well but its not working dynamically...

  6. #6
    Registered User
    Join Date
    03-30-2012
    Location
    Germany
    MS-Off Ver
    Excel 2003/2010
    Posts
    9

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    VLOOKUP should work in your case.

    In the Courier Lists:
    In each row add the Courier of that list:

    Please Login or Register  to view this content.
    In the Label Data File:
    In each row have three VLOOKUP (one for each Courier Service)
    Let it find the pincode and output the just added collumn with the Courier. (Column 6 in my example)
    In the first row of the file just add a WHEN to check which of the 3 VLOOKUPS found sth.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    1. We enjoy answering ALL questions posed.
    2. Instead of typing all those lists into the post itself, you should have copied them into sample workbooks and posted those (sample) workbooks. Since you did not, anyone who wants to help has to copy and paste into their own workbooks, not knowing what should be in each cell.
    2. Note that in your Label file, none of the PIN's appear in the courier lists.
    3. You say that you tried to use VLOOKUP but you did not post the formula.... so... (?)
    Ben Van Johnson

  8. #8
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Here is the sample file which i tried to work with VLOOKUP for 2 courier ARAMEX & BLUEDART.
    FILE IS HERE

    dunno its weird for some pincode it works for some it dont.. somthing it dont work for anything.. weird maybe u guys can solve it

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Your courier sheets must be sorted by PINCODE, in ascending Order. That was the only problem I found, otherwise your VLOOKUP works.

  10. #10
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Thanks for reply but dint work.
    i sorted BLUEDART & ARAMEX sheet in ascending order by filter.
    Then went to first sheet and typed "400003" but nothing happened to Vlookup results.
    god knows what wrong but need help badly on this as manually searched is too boring and time consuming.
    PLEASE HELPPPP

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Make sure you have Calculation set to Automatic. I had no problems.

  12. #12
    Registered User
    Join Date
    03-27-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    Thanks mate issue got solved
    i trfed all data on one sheet and created 3 tables for 3 couriers and used Vlookup to get the data.
    THanks u guys u rock
    Last edited by atthershabbir; 04-02-2012 at 11:22 AM.

  13. #13
    Registered User
    Join Date
    07-01-2013
    Location
    delhi
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    can you plz mail me the location list on [email protected]

  14. #14
    Registered User
    Join Date
    07-01-2013
    Location
    delhi
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Micro for Searching Data from One FILE and perform action if the Data is Found.

    i want the pincode list of DTDC can any one plz help me...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1