How to merge two vertical number lists into one long vertical list in Excel?

Question

So I have a few columns with numbers and I'm wondering how to make a dynamic combined list of them all? I know consolidate could theoretically work but my lists change and I don't think consolidating is dynamic. The 'Combines List' on the right is what I hope to accomplish. I want all duplicates but I don't want any spaces because for example Column C could go from having 3 numbers to 5 numbers easily. Also sorting the list would be a super bonus as that's my final goal so if I can go about this another way let me know.

You could use [this answer](https://stackoverflow.com/a/62903013/9245853) and just remove the `UNIQUE`. — BigBen, Dec 31 '20 at 19:22

Ron Rosenfeld · Accepted Answer · 2021-01-01T11:36:54.183

2

Try, with O365:

=FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,TRANSPOSE(numBers)) & "</s></t>","//s")

If you want the list sorted, then:

=SORT(FILTERXML("<t><s>" & TEXTJOIN("</s><s>",TRUE,TRANSPOSE(numBers)) & "</s></t>","//s"))

EDIT If you are running on the MAC, or online Excel, you do not have the FILTERXML function. You can use the following formula instead:

=SORT(--TRIM(MID(TEXTJOIN(REPT(" ",99),TRUE,TRANSPOSE(C10:F29)),(SEQUENCE(COUNT(numBers))-1)*99+1,99)))

This formula will work for a bit more than 300 or so characters (digits), after which you will run into the 32,767 character limit for TEXTJOIN. The first formula will have a limit of a bit more than 3,000 numbers. If you might have more than that, you should look at a VBA solution

numBers is a named range larger than what you need. Blanks will be ignored. In this case, I use c10:f100, but you can increase the size to suit.

with sort

edited Jan 01 '21 at 11:36

answered Dec 31 '20 at 23:26

Ron Rosenfeld

53,870
7
28
60

I can't seem to get this formula working even on my example data. Is there some setting to toggle? I have =FILTERXML("~~" & TEXTJOIN("",TRUE,TRANSPOSE(C10:F29)) & "~~","//s") – guanciale Jan 01 '21 at 00:48
@guanciale What does *"can't seem to get this formula working"* mean? Wrong answer? Error message? (if so, what is it?) Excel freezes? Something else? – Ron Rosenfeld Jan 01 '21 at 01:02
Oh sorry for being vague! I'm getting "#NAME?" error. I tried recreating it in the web version of Excel and also am getting the same error. Perhaps just a version issue? – guanciale Jan 01 '21 at 01:48
@guanciale you must be running on a MAC. No `FILTERXML`. I'll provide a different version of the formula tomorrow. – Ron Rosenfeld Jan 01 '21 at 02:22
@guanciale See my edit for a formula that should work on O365 for the Mac or online Excel – Ron Rosenfeld Jan 01 '21 at 11:37

Gary's Student · Answer 2 · 2021-01-02T16:25:29.723

Since you are using Office 365, try this small User Defined Function:

Public Function stackum(rng As Range)
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction
    Dim arr, i As Long, brr, b
    
    ReDim arr(1 To wf.CountA(rng), 1 To 1)
    
    brr = rng
    i = 1
    For Each b In brr
        If b <> "" Then
            arr(i, 1) = b
            i = i + 1
        End If
    Next b
    
    stackum = arr
End Function

As you see, it spills down dynamically.

If you want a formula rather than a UDF, try:

=LET(z,INDEX($C$10:$F$18,MOD(SEQUENCE(4*9)-1,9)+1,ROUNDUP(SEQUENCE(4*9)/9,0)),FILTER(z,z<>""))

It also spills down. I don't like this formula. It relies on the "magic number" 9 which is the height of the input table.

EDIT#1:

To eliminate both magic numbers (4, 9) we can use:

=LET(tb,$C$10:$F$18,cl,COLUMNS(tb),rw,ROWS(tb),z,INDEX(tb,MOD(SEQUENCE(cl*rw)-1,9)+1,ROUNDUP(SEQUENCE(cl*rw)/rw,0)),FILTER(z,z<>""))

Why not add a 2nd LET entry LET(h,rows($c$10:$F$18),z,...) and use h where you currently have 9? — DS_London, Jan 02 '21 at 15:39
@DS_London Great idea...............we can also add a `Let` to remove the *4* as well. See my **EDIT#1** — Gary's Student, Jan 02 '21 at 16:26

score 0 · Answer 3 · answered Jan 01 '21 at 06:35

Formula solution for all Excel version

1] "Combine List with sort" in H10, formula copied down :

=IFERROR(SMALL($C$10:$F$18,ROW(A1)),"")

2] "Combine List with sort & remove duplicate" in I10, formula copied down :

=IF(ROW(A1)<=SUMPRODUCT(($C$10:$F$18<>"")/COUNTIF($C$10:$F$18,$C$10:$F$18&"")),SMALL($C$10:$F$18,COUNTIF($C$10:$F$18,"<="&I9)+1),"")

How to merge two vertical number lists into one long vertical list in Excel?

3 Answers3