onelist from two

  • Thread starter Thread starter Sean Bartleet
  • Start date Start date
S

Sean Bartleet

Hi,

I have two dynamic lists of numbers that I wish to combing into one list
using formulas. The lengths of the sourse and resulting lists can change,
duplicate numbers should not be listed twice and the resulting list should
be in numerical order.

For example, if I have list one in cells A1:A5 (1,2,3,4,5) and list 2 in
cells C1:C4 (2,4,6,8), I wish to have the resulting list in cells E1:E7
(1,2,3,4,5,6,8).

The formulas need to be dynamic, for example if I were to insert two cells
in between A3 and A4 an move the cells down and enter values 10 and 11 into
the gap. List 1 should now read (1,2,3,10,11,4,5) I would like the resulting
list in cells E1:E9 (1,2,3,4,5,6,8,10,11).

Any suggestions will be appreciated. At present I am having to do this
manually by copying the two lists below each other, sorting and running a
macro to delete duplicate entries. This is a pain and runs the risk of the
third list getting out of date if lists 1 or 2 change and I forget to
rebuild list 3.

I have shown this example on one sheet, in reality all three lists are on
different sheets.

Thanks.

Sean
 
rng as list1
rng1 as list2

Insert in E1:
=IF(CELL("row",A1)<=ROWS(rng),SMALL(rng,CELL("row",A1)),SMALL(rng1,CELL("row
",A1)-ROWS(rng)))
 
Hi,

Thanks for the formula. This gives me one list with all the values in which
is somewhat helpful. The formula you propose will still yield duplicate
numbers if they exist in one or both of the list. Your formula does sort the
two lists, but it sorts each list separately

What I am really looking for is a sorted list of all unique values to be
found from the union of two lists.

I amended this formula to set the ranges of the lists as absolute and copied
it down in column E as far as I needed it. The formula in cell E1 is
=IF(CELL("row",A1)<=ROWS($A$1:$A$5),SMALL($A$1:$A$5,CELL("row",A1)),SMALL($C
$1:$C$4,CELL("row",A1)-ROWS($A$1:$A$5)))

With 5,4,3,2,2 in rng (A1:A5) and 2,4,6,8 in rng1 (C1:C4) the resulting list
in E1:E9 was 2,2,3,4,5,2,4,6,8. Note that the number 2 was repeated three
times. I really want the resulting list to be 2,3,4,5,6,8. All unique
numbers and in numeric order.

Any other ideas?

Thanks.

Sean
 
Back
Top