Combining lists

Hi everyone,

I have 5 separate text lists in E34:E50, J34:J50, N34:N50, Q34:Q50 and T34:T39.

A number of items are common across the lists and some are unique.

I need to have a 6th master List (E55:E80 approx) that contains all the entries contained in the 5 lists with no duplicates and which will update when the sub-lists are amended.

Grateful for any assistance with this one.

TIA

Ed

Can you not just copy them all into one column? then advanced autofilter it unique records only?

Then copy those to where you want them

un Filter

Paste over and delete the excess?

the tools, options, custom lists ... ?

Hi Samprince,

Unfortunately it can't be done this way. The lists are used in a series of dependant drop downs on a shared workbook, where results are summarised in another sheet and the lists may change over time, being added to by users who do not have the skills to make it all neat and tidy.

I wanted to make the workbook 'self managing' in this respect.

If anyone can say that idea this is not feasible then I will just have to live with it but, having experienced the high level of expertise and ingenuity of the forum users, I feel that someone can come up with a solution.

Regards

Ed

Just thought I let you know this problem is solved. I have to thank Excelenator who posted a reply to another problem yesterday and I used his formula as below.

First, in column A I referenced each value in each list e.g. A1=N34, A2=N34..... A70=Q34. This created a list of about 80 entries with many duplicates. In particular the original lists had room for expansion and the expansion cells had the word 'spare' in them to indicate data could be entered.

In B1=A1
in B2=IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,INDIRECT("$A$1:$A$"&COUNTA(A:A))&""), 0)),"",INDEX(IF(ISBLANK(INDIRECT("$A$1:$A$"&COUNTA(A:A))),"",INDIRECT("$A$1:$A$"&COUNTA(A:A))),MATCH( 0,COUNTIF(B$1:B1,INDIRECT("$A$1:$A$"&COUNTA(A:A))&""),0)))

entered as an array.

Copied down to the extent of col A entries

This gives a list of all entries, eliminating duplicates.

This data is then fed through to my sunmmary tables.

Thanks!

Ed