Combing multiple rows of data from one column into one cell

[telewats]

Hello everyone,

I'm attempting to combine multiple cells of data (all from one column) into a single cell. The thing is, I have over 1k rows, so it's not feasible to use the Contatenate formula. I've done this before, in a simple macro, I believe, but I can't figure it out this time. Here's an example of what I'm trying to contatenate, assuming these are all in, say, Column A, Rows 1,2,3,etc:

319<cr>550<cr>IAIAET<cr>
319<cr>553<cr>IAIAET<cr>
507<cr>287<cr>MNMNET<cr>
507<cr>289<cr>MNMNET<cr>
515<cr>419<cr>IAIAET<cr>

I'd like combine all the data into Column B, Row 1, so that it would look like this:
319<cr>550<cr>IAIAET<cr>319<cr>553<cr>IAIAET<cr>507<cr>287<cr>MNMNET<cr>507<cr>289<cr>MNMNET<cr>515<cr>419<cr>IAIAET<cr>

Does anyone have any ideas? And, thanks, in advance, for the help!

Oh, and I'm not sure why, (and this probably doesn't matter for the purpose of this thread) but the data I posted in is not displaying correctly when I submitted the thread. Each bit of data, 319, 550, IAIAET, etc. has a carriage return (cr) between them, and each "cr" is surrounded by <>.

 

[jmthompson]

so do you want the values in A from all 100 rows in the same cell?

If not, what determines which rows are combined and where you want them combined?

 

[telewats]

Unfortunately, no, I can't have all cells in the row concatenated at once. I'm using the combined data as a macro for another software program and it only allows 20 lines of data to be entered at once.

 

[RSXchin]

try this and let me know how it works for you. This puts everything from column A into cell A1

Sub all_of_a()
    HugeText = Nothing
    Cells.Replace what:=Chr(10), replacement:="", lookat:= _
        xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
        ReplaceFormat:=False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        HugeText = HugeText & Trim(Range("a" & i))
    Next i
    Range("a1") = HugeText
End Sub

 

[telewats]

Thank you for the suggestion. Unfortunately, I'm not able to get it to work. I'm getting an error that the named argument isn't found. Maybe I'm not doing something correctly?
Also, though, if possible, instead of moving the entire column at once, I'd rather be able to highlight the cells (20 at a time) that I want to concatenate and then run the macro on just those... is that possible?

 

[RSXchin]

this is for the entire column. Fixed that. can't think of how to do just a selected range

Sub all_of_a()
    HugeText = ""
    Cells.Replace what:=Chr(10), replacement:="", lookat:= _
        xlPart, searchorder:=xlByRows, MatchCase:=False, searchformat:=False, _
        ReplaceFormat:=False
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        HugeText = HugeText & Trim(Range("a" & i))
    Next i
    Range("a1") = HugeText
End Sub

 

[irwina]

Hi all,

This is a great solution, however wanted to ask assistance to concatenate based on a range.

I have a list of page numbers with several values for each. I wanted to combine all values for that page in a single cell with a separator.
Value (1-5), Page (1-2) and desired result (1, 2) is shown below:
1 1 1, 2
2 1 1, 2
3 2 3, 4, 5
4 2 3, 4, 5
5 2 3, 4, 5
etc for around 1,000 rows

I will then remove any duplicates in the next part of the macro to obtain a single row for each page.

Apologies, I cannot format this correctly.

Does anyone know how this can be done?

Thanks in advance for your help.

 

[irwina]

Bump.

Does anyone have a thought on how I can concatenate based on a particular variable (ie page number?)

Thanks again for your help