How to almalgamate a long list of numbers with commas separated in between in SAS or Excel

hantran99
hantran99 used Ask the Experts™
on
Hi everyone,

Just wondering if anyone has a quick way of almalgamating a long list of numbers (over 3,000 records) separated by commas using SAS or Excel

eg 12345, 4567,8999, 69876

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You want 3000 rows in a single line?
Are the 3000 rows in a flat file?
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
In Excel, paste the data in column A.
Or open flat file and use import wizard.

In B1, enter this     =A1
In B2, enter this     =B1&","&A2
At the bottom right corner of B2 is a slight black dot - double click on it.
In column B, go to the bottom (In B1, press Ctrl-Down).
Copy the cell and paste into notepad.

Author

Commented:
Dear cyberkiwi,

Thanks for your assistance. The numbers I am talking about are product numbers, listed in Excel - 3000 of them. I need to put this into a query, but the query requires that I list the numbers in a long list, separated by commas. I followed the same trick you suggested in your solution, but it only does 170 product numbers at a time. Is there a way of doing the 3,000 numbers altogether as quick as possible.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
In B1 only, =IF(ROW(B1)>2970,"",B31)&","&A1
Fill down.
Copy first 30 rows to notepad, delete the first comma.

Query tools like Sql Server allow for multi-row data.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
If even that is too long, then

=IF(ROW(B1)>2900,"",B101)&","&A1
Copy first 100 rows, each containing 30.

Bear in mind this and the previous one does NOT maintain the order of the items...

Commented:
This code is almost done but I am getting an "over flow 6 error."  I do not know the nature of this error.  I do not work with strings as often as others.  I have been working with the following line of numbers in cell A1.  The code runs 3 times correctly and messes up on the 4th iteration of the loop.  If anyone can help it would be much appreciated.  It copies the number at the beginning of the string and pastes it in a vertical list.
12345, 4567, 8999, 69876, 2020, 2020, 20202020202, 1823828

Set pasteRange = Range("A3")
pasteRange.Activate

Do While numberList <> ""
    commaLocation = InStr(1, numberList, ",", vbTextCompare)
    
    If commaLocation <> 0 Then
        currentNumber = CDbl(Left(numberList, commaLocation - 1))
        numberList = Right(numberList, Len(numberList) - commaLocation - 1)
    Else
        currentNumber = CDbl(numberList)
    End If

    ActiveCell.Value = currentNumber
    ActiveCell.Offset(1, 0).Activate
Loop

End Sub

Open in new window

Commented:
I made one change to the else block, I set numberList = ""
Sub unwindList()

Dim numberList As String
Dim commaLocation, currentNumber As Integer
Dim pasteRange As Range

numberList = Range("A1").Value
Set pasteRange = Range("A3")
pasteRange.Activate

Do While numberList <> ""
    commaLocation = InStr(1, numberList, ",", vbTextCompare)
    
    If commaLocation <> 0 Then
        currentNumber = CDbl(Left(numberList, commaLocation - 1))
        numberList = Right(numberList, Len(numberList) - commaLocation - 1)
    Else
        currentNumber = CDbl(numberList)
        numberList = ""
    End If

    ActiveCell.Value = currentNumber
    ActiveCell.Offset(1, 0).Activate
Loop

Set pasteRange = Nothing

End Sub

Open in new window

Top Expert 2010

Commented:
The function below can take an arbitrary range and concatenate the elements with a specified delimiter.  Granted, 3000 items is rather a lot to process, but you could try it.The syntax would be something like:=ConcRange(A1:A3000,",")If you want to skip blanks (empty cells, cells with zero length string, cells with only spaces):=ConcRange(A1:A3000,",",,TRUE)
Function ConcRange(Substrings As Range, Optional Delim As String = "", _
    Optional AsDisplayed As Boolean = False, Optional SkipBlanks As Boolean = False)
    
    ' Function by Patrick Matthews, Matt Vidas, and rberke
 
    ' Concatenates a range of cells, using an optional delimiter.  The concatenated
    ' strings may be either actual values (AsDisplayed=False) or displayed values.
    ' If NoBlanks=True, blanks cells or cells that evaluate to a zero-length string
    ' are skipped in the concatenation
    
    ' Substrings: the range of cells whose values/text you want to concatenate.  May be
    ' from a row, a column, or a "rectangular" range (1+ rows, 1+ columns)
    
    ' Delimiter: the optional separator you want inserted between each item to be
    ' concatenated.  By default, the function will use a zero-length string as the
    ' delimiter (which is what Excel's CONCATENATE function does), but you can specify
    ' your own character(s).  (The Delimiter can be more than one character)
    
    ' AsDisplayed: for numeric values (includes currency but not dates), this controls
    ' whether the real value of the cell is used for concatenation, or the formatted
    ' displayed value.  Note for how dates are handled: if AsDisplayed is FALSE or omitted,
    ' dates will show up using whatever format you have selected in your regional settings
    ' for displaying dates.  If AsDisplayed=TRUE, dates will use the formatted displayed
    ' value
    
    ' SkipBlanks: Indicates whether the function should ignore blank cells (or cells with
    ' nothing but spaces) in the Substrings range when it performs the concatenation.
    ' If NoBlanks=FALSE or is omitted, the function includes blank cells in the
    ' concatenation.  In the examples above, where NoBlanks=False, you will see "extra"
    ' delimiters in cases where the Substrings range has blank cells (or cells with only
    ' spaces)
    
    Dim CLL As Range
    
    For Each CLL In Substrings.Cells
        If Not (SkipBlanks And Trim(CLL) = "") Then
            ConcRange = ConcRange & Delim & IIf(AsDisplayed, Trim(CLL.Text), Trim(CLL.Value))
        End If
    Next CLL
    
    ConcRange = Mid$(ConcRange, Len(Delim) + 1)
    
End Function

Open in new window

Commented:
my code from before would have worked but I was getting an overflow error.  I changed the integer variables to double and now it works.
The list of numbers should be in A1.  Look to the spreadsheet for the example string of numbers and example output.

Sub unwindList()

Dim numberList As String
Dim commaLocation, currentNumber As Double
Dim pasteRange As Range

numberList = Range("A1").Value
Set pasteRange = Range("A3")
pasteRange.Activate

Do While numberList <> ""
    commaLocation = InStr(1, numberList, ",", vbTextCompare)
    
    If commaLocation <> 0 Then
        currentNumber = CDbl(Left(numberList, commaLocation - 1))
        numberList = Right(numberList, Len(numberList) - commaLocation - 1)
    Else
        currentNumber = CDbl(numberList)
        numberList = ""
    End If

    ActiveCell.Value = currentNumber
    ActiveCell.Offset(1, 0).Activate
Loop

Set pasteRange = Nothing

End Sub

Open in new window

unwindListofNumbers.xlsm
Top Expert 2010

Commented:
msheskeyA bit of friendly advice:1) >>Dim commaLocation, currentNumber As DoubleIn the above line, you are actually declaring commLocation as Variant.  To make both variables double you need:Dim commaLocation As Double, currentNumber As Double2) Just don't ever use Integer.  The moment you hit 32768, you get an overflow.  I see this often with variables that hold row numbers.  The code goes boom once you hit Row 32768 :)  Use Long instead.  15 years ago there may have been an advantage to using the smallest data type you could; nowadays it is just asking for trouble.:)Patrick

Commented:
matthewspatrick,
Thanks, I had no idea the first variable was actually being declared as variant.  Is this true in VB.NET as well?  I use the same syntax with Option Strict set to On and no errors are thrown.  I believe Option Strict does not allow the variant declaration.
Top Expert 2010

Commented:
>>Is this true in VB.NET as well?No idea :)
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Patrick,

Doesn't Excel 2003 have a limit on how much text can be put in a cell, even using VBA?
Top Expert 2010

Commented:
cyberkiwi,Yes, there is.A cell can hold up to 32,767 characters as a constant (ie, not a formula) value.  I am not sure what limit may apply to a string returned to a formula.Normally Excel will only display the first 1,000 or so characters, but you can trick Excel into displaying more by using ANSI 10 as a line break.I am skeptical, to say the least, about concatenating 3,000 items :)Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial