Database, Array, Auto filling

[chrubble]

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.

[CK76]

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.

[chrubble]

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

All the best,

chrubble

[CK76]

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.

[chrubble]

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.

[CK76]

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.

[chrubble]

That works. Thanks.

[CK76]

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.