Move data from 3 columns to 1 column w/o transfer of blanks

davidryoung said:

That did it. I wish I had asked this question two weeks ago instead of trying to all those IF statements that never quite worked...

Click to expand...

I actually worked out a formula system this morning for this recurring question, that being the reason for my question about duplicates.

For my particular set of worksheets there are no duplicates. Each entry is unique.

Thanks again for the assistance!!!!

davidryoung said:

For my particular set of worksheets there are no duplicates. Each entry is unique.

Thanks again for the assistance!!!!

Click to expand...

Just because it might be of interest, what follows depicts a formula system for how to merge multiple lists of consisting of text values into a single list with no blanks (and with or no duplicates if any).

Let A3:A13 house the first list, B3:B22 the second, and C3:C21 the third.

E1: 3

which is the number of lists to merge.

E2 must house a 0.

E3, copied down:

=IF(ROW()-ROW(E$3)+1<=$F$1,LOOKUP(9.99999999999999E+307,$E$2:E2)+(MOD(COUNTA($E$2:E2)-1,3)=0),"")

F1:

=MAX(COUNTA(A3:A21),COUNTA(B3:B22),COUNTA(C3:C21))*E1

F2: Merge

which is just a label.

F3, copied down:

=IF(N(E3),INDEX($A$3:$C$22,E3,IF((E2<>0)*(E3<>E4),3,IF(E3=E2,2,1))),"")

This formula brings together all lists with everything into a single range.

G2 must house a 0.

=IF((T(F3)<>"")*ISNA(MATCH(F3,$F$2:F2,0)),LOOKUP(9.99999999999999E+307,$G$2:G2)+1,"")

If the source lists don't house any duplicates or duplicates should not be eliminated, use:

=IF(T(F3)<>"",LOOKUP(9.99999999999999E+307,$G$2:G2)+1,"")

instead.

H1:

=LOOKUP(9.99999999999999E+307,G:G)

H2: Single List

which is just a label.

H3, copied down:

=IF(ROW()-ROW(H$3)+1<=$H$1,LOOKUP(ROW()-ROW(H$3)+1,G:G,F:F),"")

The range in H now houses the desired single list.