+ Reply to Thread
Results 1 to 18 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
    2013-365
    Posts
    500

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    2013-365
    Posts
    500

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    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
    Microsoft Office 2013
    Posts
    3,215

    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
    2013-365
    Posts
    500

    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
    Microsoft Office 2013
    Posts
    3,215

    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
    2013-365
    Posts
    500

    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
    2013-365
    Posts
    500

    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
    2013-365
    Posts
    500

    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
    Microsoft Office 2013
    Posts
    3,215

    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
    2013-365
    Posts
    500

    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
    2013-365
    Posts
    500

    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

  16. #16
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

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

    there is no any possibility in excel?

  17. #17
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

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

    Hi,
    can is it possible to highlight items are not match to each other AUTO. it is easy to find out. in attached file,
    =IF(MATCH(A3,$F$3:$F$21,0)>0,A3,REPLACE(A3,1,11,F3)) shee1
    return #N/A why not replace when condition false.
    Thanks.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    12-08-2018
    Location
    pakistan
    MS-Off Ver
    2013-365
    Posts
    500

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

    Hi, can anybody tell me when condition false in last previous post, it would AUTO replace.
    =IF(MATCH(A3,$F$3:$F$21,0)>0,A3,REPLACE(A3,1,11,F3)) shee1 i use in K2.
    Thanks.

+ 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. [SOLVED] Find item(s) in a cell and match to item(s) in a list
    By seleseped in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-11-2017, 12:06 PM
  2. [SOLVED] Vba to match item description and copy/paste item code to other sheet
    By sintek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2017, 11:29 AM
  3. Auto decrease quantity of an item when the item is sold.
    By acidust in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2016, 07:46 PM
  4. [SOLVED] need to flag item when doesnt match the item above it
    By mcarp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2014, 08:40 AM
  5. Replies: 12
    Last Post: 03-17-2014, 06:55 PM
  6. Replies: 9
    Last Post: 05-21-2011, 12:14 AM
  7. [SOLVED] Auto assigning sale item invoice number against stock list
    By Dave855 in forum Excel General
    Replies: 0
    Last Post: 10-25-2010, 09:41 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