1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Database Lookup at new import

Discussion in 'Business Applications' started by Enehasd, May 26, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    Hello Tech Guy!

    I have recently received some of your expertise, and I was more than pleased, so now I'm going to ask you once again :)

    I am working on making a database for customers. Example in the sheet "Database". Now the idea is, that every few months we will import a new list of all of our clients from our accounting system and import this list into Excel. This list will have the same data in the corresponding columns. However, some clients may have switched name, date, address, att., or anything else, except for ID. New might also have been added, or old might have been removed.

    Now what I need for this workbook to do, is the opportunity to click a button after copying in new data in sheet "New Import" (this button could be in the "Results"-sheet), and then it will run a search and give a third sheet with results, where you'd be able to see 2 rows with the same ID which doesn't have corresponding data in all columns, where the old data will be coloured, but both represented in the result sheet. Also make a list in the result page of both new ID's and removed ID's, so that you can add them to the list.

    I'd like for a button to appear next to each of these lines saying "Behold" which would import that line of data into the database, and delete the other entries for the same ID.


    See "Customer Database" as an example.
    "Database" contains the old data.
    "New Import" contains the newly imported data.
    "Results" contains the resultpage, also an example of how I'd like it to look (excluding the buttons)


    Looking forward to your assistance :)

    //Enehasd
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi,
    I've downloaded your file and will take a look to see if I really understand what you require before trying to offer solutions.
    I'll get back to you if I have any questions or need extra information.
    This is just to let you know somebody has picked this up.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay, I've reread your question and looked at the data you attached and I understand what you require (correct me if I am wrong):

    1. You import new data which will always be placed in a worksheet named 'New Import'
    2. The worksheet 'Database' will contain a complete list of all the existing clients.
    3. You want a macro to be run after the New Data is imported and cross-check it against the Database and have all modified, deleted and new records placed in a new sheet named 'Results'
    4. After this you want to have buttons placed in the lines with data (why?) so that one of the following actions will be carried out:
    - if record is modified the selected record will be copied to the Database record updating an existing record (same ID) and remove the records from the Results sheet
    - if record is removed, delete it from the Database and remove it from the results sheet
    - if record is NEW add this record to the Database and remove it from the results sheet.
    The buttons part will be a little complicated but why not just a right-click action an that record, will make it much simpler.

    After these actions a the comparison macro could be run again and no data should appear in the results worksheet, is this correct?

    Please let me know before I start:)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    First run to compare and fill the Results sheet

    The results Sample sheet is a copy

    Press the shape on the Results sheet ...
     

    Attached Files:

  5. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    The button to Keep the line (You have understood what the intention with the button was) was an idea, I don't know what is possible and what isn't :)

    And yes, the results sheet and the new import sheet should be cleared, if possible.

    I thank you very much for your time, I'll have a look at your first draft.
     
  6. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    Runs smoothly, just as I imagined :)!


    I was wonder if it would it be possible to add additional columns if required in the database? I don't have the excact amount of columns extracted from the main system yet.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You will have to take those into account and change the vba code accordingly.
    I'll see if I can modify the code to care of this automatically so that the code can remain unchanged.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I made some small modifications and it will now allow any number of columns
    The second part to update has not been done yet.
    Am quite busy and will try and see if I can complete it later today for you.
    The Update of the changed records will be the challenging one :)
     

    Attached Files:

  9. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    I really appreciate your help and hard work :)
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    This is the preparation for the next step.

    The actual macros are still not written but this would be my way to approach this.

    You will have to tell me what to do. Do you wish to clear the record in new Import once the Add or Remove or Change has been carried out?

    You can try writing the necessary steps yourself, it's a good way to learn VBA
     

    Attached Files:

  11. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    Right now there is a button to compare the new import with the old, and these will appear on the result sheet.

    Then you right click in column A and you're asked if you want to keep or discard the changes. Will it be possible to remove the entry from the result page after each keep/discard? (Maybe a cancel button in the dialogue window)

    So that we have the 3 groups: Changed, Removed and New. You can right click in column A for each Entry (except for the entries which had data that got changed). When doing so, a pop-up will appear with Keep, Discard or Cancel. Keep will import it into the "Database". Discard will delete the entry, and cancel will just close the pop-up with no action. After clicking Keep or Discard, it should remove it from the results sheet. Once the results page is clean, it should wipe the "New Import"-sheet.

    You're telling me to try to write it in steps as a VBA instructory course:
    1) Right click action in column A
    - Pop-up. Keep/Discard/Cancel
    - Keep (Changed) = Overwrite from "New Import" to "Database"
    - Keep (Removed) = No action
    - Keep (New) = Import from "New Import" to "Database"
    - Discard (Changed) = No action
    - Discard (Removed) = Delete entry from "Database"
    - Discard (New) = Do not import from (New Import)
    - Cancel = Closes pop-up
    2) After Keep or Discard is clicked, remove entry from "Results"
    3) Check to see if any entries left in "Results"
    - If no entries left, wipe "New Import"


    I hope this is possible, at least this is my way of thinking. Please do not hesitate if anything is troublesome, can be made smoother, or any suggestions at all :)
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, my internet was down until just now.
    I wrote the macros before I read your message
    The optiosn to 'Discard' to be implemented but rigth click does some thing now.

    What has been done so far:

    - Keep (Changed) = Overwrite from "New Import" to "Database" (done)
    - Keep (Removed) = No action (not done)
    - Keep (New) = Import from "New Import" to "Database" (done)
    - Discard (Changed) = No action (not done)
    - Discard (Removed) = Delete entry from "Database" (done)
    - Discard (New) = Do not import from (New Import) (not done)
    - Cancel = Closes pop-up (done)
    2) After Keep or Discard is clicked, remove entry from "Results" (not done)
    3) Check to see if any entries left in "Results" (done on-the-fly)
    - If no entries left, wipe "New Import" (not done but you get a message when there is nothing more to process)

    Check and let me know.
     

    Attached Files:

  13. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    Looks great! on second thought, it is not needed with the option to Keep a removed entry, Discard a change, Discard a new. It functions really well how it is. it also clears the entries on the fly while they are imported or deleted.

    Runs as I imagined, I appreciate it :)

    I assume I can change the text in the boxes, as long as I don't change the name of the sheets? and that I can delete the "Results-Sample" sheet?
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    You may delete the Results-Sample sheet, no problem there.
    The rest you may change at will, if you 'wreck' something you'll have to start over :)

    I'll keep a copy.

    Good luck and happy coding.
     
  15. Enehasd

    Enehasd Thread Starter

    Joined:
    Jan 14, 2014
    Messages:
    38
    Thank you :)
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1148853

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice