+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 18

Match Item with Invoice and Replace any missing Item AUTO

  1. #1
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Match Item with Invoice and Replace any missing Item AUTO

    Hi all,
    kindly tell me vlookup can replace item if item is differ in invoice data. kindly check attached data to see export in two different system. one is original (main data ) and other from sales tax Data. in this i apply vlookup to find KIP No from main data sheet.
    most of time vlookup give N#A its mean single item did not match to main data sheet based on invoice no.
    first to check it by invoice then match all item, if any differ to replace by main data sheet auto and find KIP No. ( original Data).
    this is sample, original file is more then 20MB. i want to upload but failed.
    any body help me?
    thanks.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,168

    Re: Match Item with Invoice and Replace any missing Item AUTO

    The number in A4 0010033MD-NCEUP3020 does not match with anything on the main data sheet, so is returning N/A. What are you expectiung it to match to?
    Last edited by AliGW; 01-09-2019 at 02:09 AM.
    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.

  3. #3
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Hi aliAW,
    i knew that, but you have to check within invoice DATA main sheet 0010033, actual item code is 0010033MD-NCEUP3012. so it will be replaced then pick KIP No.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,168

    Re: Match Item with Invoice and Replace any missing Item AUTO

    So, answer my question: what are you expecting the result to be? How is Excel meant to know that the two codes are linked? I am not a mind reader.

    Sorry - I have run out of time now. Hopefully someone else will step in.
    Last edited by AliGW; 01-09-2019 at 02:33 AM.

  5. #5
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    i want replace auto by this 0010033MD-NCEUP3012 then formula pick KIP-No. other wise i do it manually copy from main data sheet and paste inv data sheet. it is system generated report problem.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    28,168

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Yes, but HOW is Excel to know that 0010033MD-NCEUP3020 should match with 0010033MD-NCEUP3012? Where in the workbook is a relationship between the two numbers defined? Excel cannot make a link between the two unless one exists in your data. So please tell us how you know that the two are linked.

  7. #7
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,472

    Re: Match Item with Invoice and Replace any missing Item AUTO

    First change sheet name with _ (Under Score) between two word.
    Try in Inv_Data Sheet in "G3"
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Ensure press SHIFT+CTRL+ENTER
    Copy down.
    Note Invoice no. "0010041" : Item code not match due to different item code.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  8. #8
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Hi AVK, i apply your formula but there is a problem. please see the pic.
    invoice = 0010055 there is no item 001005MD-SESRO1 formula pick KIP No. Why? actual item on main data is 0010055MD-IHP055150 and no KIP No on main data sheet. same for other invoice no 0010038.
    main item shows two items 0010038MD-5433A0000 with no KIP No.
    - 0010038MD-5433V0000 -KIP-A00423
    Thanks.
    Attached Images Attached Images
    Attached Files Attached Files

  9. #9
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,472

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Refer attach file.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    this is an same problem. please review file
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Hi AVK,
    first i have to check item then PICK KIP. KIP No will be search easily when item is correct. Inv Data sheet wrong item are in and i have to set and check item based on main data sheet. you can check main data sheet invoiced based items to check inv data sheet. if any change in item replace first by main data sheet then pick KIP No.
    this is actual my question.
    first set item on inv data sheet based on main data sheet.

  12. #12
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Hi AVK,
    i worked on this sheet. i apply IF condition to find out where condition false. "*"&A3&"*" is not apply in IF condition
    when apply vlookup so it will OK.
    in this working yellow highlighted cell to be replaced to each other when you consider based on invoice and Qty match.
    rest of other items will be settled by remove 1 or 2 items character. you can see pic. if you remove last 1 or 2 digit delete the formula will work.
    if it is possible so please help me.
    for more detail regarding in this so please ask.
    thanks.
    Attached Images Attached Images
    Attached Files Attached Files

  13. #13
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,472

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Are you check file as per my Post #10

  14. #14
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Yes, i give some problem in it. KIP no found by formula but actual is no KIP No.

  15. #15
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2010
    Posts
    336

    Re: Match Item with Invoice and Replace any missing Item AUTO

    Hi AVK,
    is it possible to this? please see the attached file sheet1. if i work only one invoice.
    Thanks
    Attached Files Attached Files

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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