sets of data

[JGarza]

I have a listing of approximately 20,000 account numbers in a column in a spreadsheet.

What i need is to combine account numbers into one cell in sets of 1000. How can i do this automatically with a macro or something. They also need to be seperated with a semi-colon for example (138585;385020;3059093;2323;4802084098;9398...)

I'm not VBA savy, so i hope for a simple formula.

Thanks

 

[Sal Paradise]

Max cell length for a cell is 32767 characters, with only 1028 being able to be displayed (at least prior to excel 2007). Your account numbers seem to be up to 10 characters + semicolon * 1000, or 11,000 characters which is a lot (but potentially doable).

However, since you can't use CONCATENATE() or & in array formulas, there will be no easy formula solution. You will likely have to look into VBA or a non-formula solution.

Assuming all your account numbers are in column A from A1:A20000 or so, here's code that will work:

Sub Test()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim strAccounts As String
    
    k = 1
    
    For i = 1 To Worksheets("Sheet1").Range("A1").End(xlDown).Row
        If j = 1000 Then
            j = 0
            Cells(k, 2).Value = strAccounts
            strAccounts = ""
            k = k + 1
        Else
            strAccounts = strAccounts & Cells(i, 1).Value & ";"
            j = j + 1
        End If
    Next i
    
End Sub