+ Reply to Thread
Results 1 to 7 of 7

Using Excel VBA to update a list

  1. #1
    Registered User
    Join Date
    12-10-2018
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    11

    Red face Using Excel VBA to update a list

    Hi! I'm trying to create a macro that updates a list of items purchased - I want it to reference a source file which will have duplicate items in it. So say I bought the following fruits:
    1. Apples
      Banana
      Orange
      Apple
      Berries
      Banana
      Mango

    I have a summary list that includes apples, bananas and oranges. How do I update this list using a macro?(Is that even possible??) I want it to look at the source file and remove duplicate values and check to see is there are any new values and add them to the summary list.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Using Excel VBA to update a list

    I'd recommend uploading sample file. To upload file, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

    Typically I'd use Scripting.Dictionary.

    Steps:
    1. Add your summary list to dictionary, using list items as key. dictionary item can by anything (I typically use 1).
    2. Loop over the source file list and add to dictionary (either explicitly checking for dictionary.Exists, or using implicit add).
    3. Return result to summary list.
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    12-10-2018
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Excel VBA to update a list

    So I can't add my exact data, but I added a screenshot from a sample file I quickly created. The summary list in column C does not include all the values in column A. I want to be able to update this list - my actual data has about 5000 rows of data which is constantly being updated so i wanted a quick and simple way to do this.

    Thanks


    sample.PNG

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Using Excel VBA to update a list

    How about
    Please Login or Register  to view this content.

  5. #5
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Using Excel VBA to update a list

    How about a pivot table and in the row header put Item Number - leave the rest of the pivot table blank? This will get you a list of unique item numbers in alphabetical or numeric order.

    P.S. Make the list of items an Excel Table, then all you have to do is refresh the pivot table as you add or delete data.
    Last edited by dflak; 12-14-2018 at 03:21 PM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  6. #6
    Registered User
    Join Date
    12-10-2018
    Location
    toronto
    MS-Off Ver
    2010
    Posts
    11

    Re: Using Excel VBA to update a list

    Fluff13 Thank you so much, this worked beautifully!!
    Last edited by leenz; 12-14-2018 at 04:32 PM.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Using Excel VBA to update a list

    You're welcome & thanks for the feedback

+ 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. How to update an excel list? Not quite sure...
    By metalslug in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2008, 09:14 PM
  2. Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 PM
  3. [SOLVED] Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  4. Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 07:05 AM
  5. [SOLVED] Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 AM
  6. Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  7. Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. Automatic update of list in Excel
    By Swampy in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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