+ Reply to Thread
Results 1 to 30 of 30

Need help in mapping pincode

  1. #1
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Need help in mapping pincode

    Dear All,

    I have been trying to use vlookup and partial match the pin codes for routing issue. I have attached the excel sheet with the data and the formula am using "= Vlookup(left(A2,4)&"*",b2:b1335,1,0)". Am only able to map a few but for rest am getting #N/A. Need help in solving the problem.

    Thanks in Advance,

    Aditya
    Attached Files Attached Files

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,031

    Re: Need help in mapping pincode

    Where is your formula? Where should the results come?
    Your use of VLOOKUP is incorrect or at least doubtful

  3. #3
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Vlookup formula is right. But I haven't inserted it in the sample sheet attached. I want the data in the third column (C).

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    The VLOOKUP formula is NOT right, otherwise it would work, and as already mentioned, the syntax is wrong.

    Fill in 10-20 rows of expected results MANUALLY and then post the workbook again.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Hi Ali,

    My mistake, in the formula I had to convert number to text, now I have changed the data from numeric to text but I still don't get the desired result can you please help. attached the corrected sheet where results for the formula have been inserted.
    Attached Files Attached Files

  6. #6
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,031

    Re: Need help in mapping pincode

    It is still not clear what you are trying to do
    Your "unorthodox " use of VLOOKUP will return the first value in col D starting with the 4 first numbers of the cell in col C
    BUT in col D there is more than one value that satisfies this condition ( e.g. for E2 ther are 10 values starting with the string 6033)?
    So, explain in words what you want to achieve

  7. #7
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    thanks Pepe.. as you have explained after searching for the 4 characters in the string, I would want excel to populate the nearest match for C from D in E column. example 600053 is present in both C and D columns yet excel mapped 600053 in column C to 600004 in column and provided the result which is incorrect. Another example u have 620018 is present in both C and D yet excel hasn't mapped anything against 620018 in column E.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    Try anchoring the range:

    =VLOOKUP(LEFT(C2,4)&"*",$D$2:$D$1335,1,0)

  9. #9
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Hi ali,

    This isn't working as well as the values aren't yet right.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    Isn't it? So are you going to give us any clue at all about what is wrong with it?

    Apply my version to your file, and then highlight some incorrect answers and type manually to their right what you are expecting, then post the workbook again.

    NOBODY likes guessing games.

  11. #11
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Here you go I have attached the file highlighting the desired result.
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    You haven't applied the formula I gave you. I'm not going to waste time going round in circles - sorry. Good luck!

  13. #13
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Thanks for the response. However in the new sheet I have applied your formula and still there isn't any go.
    Attached Files Attached Files

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    You have been asked to explain IN WORDS what you are trying to achieve - you still haven't done this. We do not have infinite time (or, in my case, the patience) to prise the necessary information out of you. If you think I'm going to spend my free time trying to work out (guess) what you mean by 'partial match' when you can't even be bothered to explain it yourself, then you've got another think coming. Sorry, I won't be helping any further.

  15. #15
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: Need help in mapping pincode

    Ok, it is better to start from beginning
    In newest sample, is the yelow area expected results?
    If yes, why
    Pincode
    603309
    603109
    600053
    603310
    600045
    then
    Mapping Pincode
    600001
    600002
    600004
    600003
    600005
    and
    Result:
    603309
    603109
    600053
    603309
    600045

    How to determind the results?

  16. #16
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Hi Bebo,

    Yes yellow highlighted is the expected result. Pincode is the desired pincode to be shipped and mapping pincode is the pincode where my logistics partner is able to ship. So, am mapping my logistics partners pin codes to my pin codes.

    So, now am using vlookup so as to map it accordingly.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    Still no attempt to explain the logic ...

  18. #18
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Ali,

    Am trying to use VLOOKUP partial match logic as some pin codes mentioned under pin code heading cannot entirely be mapped to mapping pin code. If it were a straight match there is no need to use any logic just a plain matching. But while doing a partial match, the problem am facing is pin codes that matched completely are not getting mapped properly and its missing the logic of mapping to the nearest match. This is the reason am asking help and the desired result that I want has been highlighted in yellow. This mapping is being done to get routing map to deliver our products through our logistics partner.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    All of that we know. What is the LOGIC? That's what you haven't explained. We don't know the logic that you are applying and we are not mind readers. There is little point repeating the same thing - emboldening the text doesn't make it any more informative!!! And it still doesn't tell us what we need to know.

    So, for the last time: what is the logic that is needed to find a partial match???

    its missing the logic of mapping to the nearest match
    That's because you have not yet explained what the logic is. Get it?

    'Nearest match' could be interpreted many ways - what is YOUR interpretation of it?
    Last edited by AliGW; 04-05-2021 at 06:38 AM. Reason: Typo

  20. #20
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,908

    Re: Need help in mapping pincode

    Please Login or Register  to view this content.
    Maybe somthing like this

    "# "& this part is to identify if it is an exact match or not.
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  21. #21
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    popipo

    The formula worked partially, while where it isn't a match it has taken the first result it has caught while sorting. It needs to capture the nearest pin code. so we may require further correction in this formula as well. Attached the resultant sheet
    Attached Files Attached Files

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    It needs to capture the nearest pin code
    You will need this:

    =IFNA(VLOOKUP(C2,$D$2:$D$1335,1,0),formula_to_find_nearest_match)

    BUT you STILL haven't told us what you mean by 'nearest match'.

    Explain how that works. Nearest match above or below? What happens if you have one that is one above and one that is one below - which takes priority?

    This is the LOGIC that I have been trying to get you to explain for THREE HOURS now.
    Last edited by AliGW; 04-05-2021 at 07:05 AM.

  23. #23
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Sorry that I couldn't get that point. Nearest match would be match below that takes priority as it would belong to the same region. While nearest match above will move to the next region most of the times so, we need to consider the value that is below.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    At last! So, to clarify: ALWAYS an exact match or the nearest below? Yes?

    The problem is that a VLOOKUP inexact match looks at the next one UP, which is why it's not working for you.

    Please confirm and we'll take it from here.

  25. #25
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    Yes that's right. And when I say nearest its not the first option that it gets needs to be displayed but the right nearest one. Example for 603310 the nearest would be 603309 not 603301. that needs to be displayed.
    Last edited by swaaditya; 04-05-2021 at 07:12 AM.

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    Try this:

    =IFNA(VLOOKUP(--C2,--$D$2:$D$1335,1,0),VLOOKUP(--LEFT(C2,5)*10,--$D$2:$D$1335,1,1))

  27. #27
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    It is working but still as informed above for 603313 its still showing 603309 but not 603312 that is present

  28. #28
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    Try this:

    =IFNA(VLOOKUP(--C2,--$D$2:$D$1335,1,0),VLOOKUP(--C2,--$D$2:$D$1335,1,1))

  29. #29
    Registered User
    Join Date
    02-04-2021
    Location
    Chennai India
    MS-Off Ver
    2016
    Posts
    18

    Re: Need help in mapping pincode

    yes it has worked. Thanks a lot. Sorry for not providing the exact information upfront.

  30. #30
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,489

    Re: Need help in mapping pincode

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. As you are new, I'll provide it for you today: https://www.mrexcel.com/board/thread...ncode.1167116/.)


    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of ALL those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. I want to track sales through taluk wise from pincode
    By SHIVATRICHY in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-02-2021, 08:35 AM
  2. How to make a PINCODE that will not exceed to 6-character
    By arvinian.2291 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-25-2017, 09:05 AM
  3. Replies: 2
    Last Post: 08-25-2017, 01:51 AM
  4. Replies: 4
    Last Post: 12-27-2015, 07:22 AM
  5. [SOLVED] XML mapping
    By Sinep D in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-21-2015, 07:43 AM
  6. [SOLVED] mapping add-in
    By colettey29 in forum Excel General
    Replies: 7
    Last Post: 06-14-2006, 05:40 AM
  7. [SOLVED] Xml mapping
    By Fred in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-17-2006, 06:20 AM

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