How do I combine Data in Excell

[Snookless7]

Hello all-
This one I can not figure out...

Column A has 1 zip code per row. I have 39K rows. I need to get all of them in one cell separated by a comma.

How can this be accomplished.

Much appreciated your help is!!

Snookless

 

[dhookom]

You might want to use a simple vba function. You can create a new module and then paste the function into the module. You then use this function like you might use any other function.

Function RangeConcatenate(rngIn As Range, _
        Optional strDelimiter As String = ", ")
[green]    ' Usage
    '  A1 = Red
    '  A2 = White
    '  A3 = Blue
    ' =RangeConcatenate(A1:A3) = Red, White, Blue
    ' =RangeConcatenate(A1:A3," - ") = Red - White - Blue
[/green]    
    Dim rngTemp As Range              [green]'each cell[/green]  
    Dim strTemp As String
    Application.Volatile              [green]'autoupdates[/green]  
    For Each rngTemp In rngIn         [green]'loop through each cell[/green] 
        If Len(rngTemp.Text) > 0 Then [green]'ignore blank cells [/green]  
            strTemp = strTemp & rngTemp & strDelimiter
        End If
    Next
    If Len(strDelimiter) > 0 Then     [green]'remove the final delimiter[/green]  
        strTemp = Left(strTemp, Len(strTemp) - Len(strDelimiter))
    End If
    RangeConcatenate = strTemp
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Snookless7]

Well unfortunately my ignorance prohibits me from using that.......LOL

That is above my knowledge skill....don't know where to start on that.

I put the file in my dropbox....not sure if anyone can get to it.

 

[Snookless7]

Well the dropbox idea will not work. :-(

 

[Snookless7]

The attached link should work

 

[zelgar]

Your link doesn't work. Also, you're going to have problems trying to get that much data into a cell. I'd suggest you copy the column of data from Excel to Word and then convert the Table to Text.

 

[dhookom]

Maybe it's time to learn something new ;-)
[ol 1]
[li]Press [Alt]+[F11] to open the Visual Basic window (a whole new world awaits you)[/li]
[li]From the menu select Insert->Module to open a new module that looks a bit like notepad[/li]
[li]Copy the code I provided (from the Function to the End Function lines) into the module[/li]
[li]From the menu select Debug->Compile VBA Project to make sure there are no errors in the code[/li]
[li]Close the Visual Basic editor window[/li]
[li]Select the cell in your worksheet where you want the result and type
=RangeConcatenate(...Your Range Here...)[/li]
[li]Save your work[/li]
[/ol]
You are now a programmer since 95% of the code I write is copy and pasted from other sources.



Duane
Hook'D on Access
MS Access MVP

 

[Snookless7]

Thank you Dhookom..
I did exactly what you said....for #6 i typed =RangeConcatenate(A1:A39366)......then saved....I got #value" in that cell.

Any Ideas.


And thanks for the lesson...that was easy.

 

[Snookless7]

got it......I have to break it up...Marry Me?? LOL
Much appreciated.

 

[Snookless7]

Well.....I was in a rush with my last reply..


TO dhookom....Thank you very much. Your Solution worked like a champ. Excel has limits as we all know so I could only run 4650 records at a time.

Thanks again for your Help.

 

[dhookom]

Glad to hear you got this working but I am already happily married for 36 years ;-)

Duane
Hook'D on Access
MS Access MVP

 

[Deniall]

dhookom,

A very minor query on your UDF, on an aspect I do not know very much about.
Does it actually require the [tt]Application.Volatile[/tt] statement?

 

[dhookom]

Deniall,
I don't recall why I included that line in the code. I expect there was a reason but it escapes me. I know I wouldn't have put it in there without some googling around to find the cause of an error.

Duane
Hook'D on Access
MS Access MVP

 

[Deniall]

You've got me thinking about this again, and reminded me that my knowledge is a bit rusty.

The following URL describes when you should use [tt]Application.Volatile[/tt] and when you should not use it. It also describes why if possible you should design your UDFs to avoid the statement's use. (I know you cannot / should not believe stuff on the web without a bit of cross-checking, but FWIW the comments in the URL are pretty much in accord with my recollections. They are also expressed much more clearly and eloquently that I would be capable of doing here.)

http://www.dailydoseofexcel.com/archives/2004/06/22/volatile-functions/

 

[xlhelp]

Sorry to join in late in this discussion.

For the code challenged, this might work just as well:

Save the file in CSV format. Open it in Word. Use replace command to replace ^p with a comma and a space (if desired).

Copy all and paste it where you need it.

If saving in CSV format is not possible owing to other data, copy column A; paste the TEXT in Word and use replace as above.

BTW, I too have been (happily) married for 41 years.


Avoid Hangovers. Stay drunk.

 

[dhookom]

I think I added the volatile so that if any values in the cells in the range changed, the result would be recaclc'd. Deniall, thanks for the link.

Duane
Hook'D on Access
MS Access MVP

 

[lionelhill]

... but looking at it, if any cell in the range changes, it should be recalculated anyway. At risk of hijacking a thread, the thing I don't understand about "volatile" is when the function gets recalculated relative to any other calculations going on. Normally there's a natural chain of events, calculating any cell after cells it refers to have changed. If otherwise-untriggered volatile calculations are done at the start, and the function also depends on other cells, then the first time it's calculated, the answer will be wrong and it'll have to be done again after the cells it refers to change - so it's just going to make things slower. If volatile calculations are done at the end, and other cells depend on the result, then they will have to be calculated again.

It strikes me that volatile really only makes sense for functions that derive some (or preferably all) of their information from a source that's not in the current worksheet, and therefore unable to trigger calculation (e.g. the NOW() function).

 

[dhookom]

Could be correct.

Duane
Hook'D on Access
MS Access MVP