+ Reply to Thread
Results 1 to 8 of 8

Database, Array, Auto filling

  1. #1
    Registered User
    Join Date
    10-24-2019
    Location
    Sheffield, England
    MS-Off Ver
    MS Excel for Mac (16.29.1)
    Posts
    11

    Question Database, Array, Auto filling

    I have a data set with 3 columns. The first column is a non repeating sequential set (1, 2, 3, etc), the second and third columns contain a comma separated set of numbers related to the number in the first column.

    Example:

    COLUMN 1 COLUMN 2 COLUMN 3
    1 1, 3, 5 2, 4, 6, 10
    2 2, 3, 6, 8 3, 6, 9
    3 1, 3, 5, 7 2, 9, 10

    I want to be able to enter a selection of numbers from column 1 into a separate table and have the related numbers from the second and third column populate without duplicating repeated numbers - Example based on data above (Bold numbers in column 1 are entered by the user):

    COLUMN 1 COLUMN 2 COLUMN 3
    1, 2, 3 1, 2, 3, 5, 6, 7, 8 2, 3, 4, 6, 9, 10

    Can anyone recommend how I can make this happen automatically?

    Many thanks in advance.

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

    Re: Database, Array, Auto filling

    Following code is for Windows machine. But you can use "VBA-Dictionary" add-in and replace dictionary references with it.
    I.E. Replace "Dim dic as Object" with "Dim dic as New Dictionary" and remove "Set dic..." line.
    Link: https://github.com/VBA-tools/VBA-Dictionary

    I assumed Sheet1 holds the data.

    Function:
    Please Login or Register  to view this content.
    Then in Worksheet module (ex: Sheet2).
    Please Login or Register  to view this content.
    This returns unsorted list. If you want to sort the result... you'll need to implement additional sort logic.
    0.JPG

    See attached.
    Attached Files Attached Files
    “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
    10-24-2019
    Location
    Sheffield, England
    MS-Off Ver
    MS Excel for Mac (16.29.1)
    Posts
    11

    Re: Database, Array, Auto filling

    That's fantastic! Thanks for your help with this CK76.

    All the best,

    chrubble

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

    Re: Database, Array, Auto filling

    You are welcome

    If you are satisfied with the solution provided, please mark the thread as solved, using thread tools found at top of your initial post.

  5. #5
    Registered User
    Join Date
    10-24-2019
    Location
    Sheffield, England
    MS-Off Ver
    MS Excel for Mac (16.29.1)
    Posts
    11

    Re: Database, Array, Auto filling

    I'm just trying to work out how to get it to run on the Mac - It errors, due to the issue that you highlighted. I'm not sure how to get the VBA-Dictionary into Excel as an add-on and how to run it. But, I'm working on it.

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

    Re: Database, Array, Auto filling

    Download zipped file from the link and unzip. In VBE right click on module folder and select import. Import Dictionary.cls.

    Change function to...
    Please Login or Register  to view this content.
    See attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-24-2019
    Location
    Sheffield, England
    MS-Off Ver
    MS Excel for Mac (16.29.1)
    Posts
    11

    Re: Database, Array, Auto filling

    That works. Thanks.

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

    Re: Database, Array, Auto filling

    You are welcome

    If you are satisfied with the solution provided. Please mark the thread as solved by using thread tools found at top of your initial post.

+ 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. Auto filling with array formulas
    By PHS8100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-15-2015, 11:58 AM
  2. Filling a cell in sheet1 by searching Database
    By amaanak in forum Excel General
    Replies: 5
    Last Post: 09-14-2014, 02:25 PM
  3. Auto-filling from database
    By fenris117 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2012, 08:48 PM
  4. Replies: 2
    Last Post: 07-09-2012, 10:15 AM
  5. Auto Filling Information from Excel or Access database
    By ih8xc in forum Word Formatting & General
    Replies: 1
    Last Post: 12-22-2011, 06:12 AM
  6. Does filling part of an array from a range re-dimension the array?
    By barryleajo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-04-2011, 10:09 AM
  7. Auto Protecting cells & auto filling date
    By ccarmock in forum Excel General
    Replies: 7
    Last Post: 09-30-2005, 05: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