Combining 2 dynamic lists

[jomathew]

Request the valuable assistance of all the Excel Gurus with this challenge


I have 2 dynamic excel lists in a single sheet

List 1 - Item A, Item B, Item C ...


List 2 - Item P, Q, B, A1..


Some items are common in both the list.


My challenge

On another shhet on the same workbook, I need to create a single column that contains the distinct items in List 1 and List 2. (More like the output of a distinct UNION query in database parlance)


This is a dynamic data set meaning, List 1 and List 2 are pulled from a database based on different criterias and cut pasting/sorting is not an option.


Any idea would be highly appreciated.


Regards

Joseph

 

[Hui]

Joseph


Firstly welcome to the Chandoo.org Forums


I set up 2 lists on Sheet 1

The first value in List 1 was a and in list 2 was z


You can use a SQL query to query the same file

it will be something like

On Sheet 2

I used a Data Connection and an SQL query


SELECT DISTINCT

Sheet1$.a

FROM [code]Sheet1$

Union All

SELECT Sheet1$

.z

FROM Sheet1$[/code]


That put a single list with all the unique values in it

I think you can set them to update every x minutes ?


Hopefully an SQL person will jump in here and help out


I am sure I should be able to use named Ranges but couldn't get them to be recognised

 

[jomathew]

Thank you so much Hui.


I never knew that you coulduse Data Connection to link back to the same page. Let me try this


I am planning to save the file as an Excel template so that when the user open the file, the data gets refreshed.


However, thanks once again for taking time to post a solution.


Regards

Joseph