CODEWITHSUNDEEP
excelexcel-formulaoffice365
guanciale
guanciale

Reputation: 53

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

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.

enter image description here

Upvotes: 1

Views: 3291

Answers (3)

Gary's Student
Gary's Student

Reputation: 96773

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

enter image description here

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<>""))

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

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.

enter image description here

with sort

enter image description here

enter image description here

Upvotes: 2

bosco_yip
bosco_yip

Reputation: 3802

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),"")

enter image description here

Upvotes: 0

Related Questions