I'm looking to the experts on this one as I'm a very basic VBA user
and not sure where to start. I'm looking for some help in buildig a
macro that could do the following. Take a column of data such as
below
and then Consolidate the data 50 cells at a time with the data
seperated by a semi-colon(no spaces) into 1 cell. For Instance:

A
1 11111
2 11112
3 11113
4 .....


2223 12223


to


A
1 11111;11112;11113...etc
2 11151;11152;11153...etc


I'm sure there's a way to do it. I'll be honest. I have no idea where
to start. If it's not clear what I'm trying to do, please let me
know.
Any help at all will be appreciated.


Thanks!

Hi,

Please clarify where your existing data is, and where you want the new data
to be returned.
It not clear to me what you are trying to do. Are you trying to merge
several columns?
Ariel


"AndrewJ" <(E-Mail Removed)> wrote in message
news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
> I'm looking to the experts on this one as I'm a very basic VBA user
> and not sure where to start. I'm looking for some help in buildig a
> macro that could do the following. Take a column of data such as
> below
> and then Consolidate the data 50 cells at a time with the data
> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>
> A
> 1 11111
> 2 11112
> 3 11113
> 4 .....
>
>
> 2223 12223
>
>
> to
>
>
> A
> 1 11111;11112;11113...etc
> 2 11151;11152;11153...etc
>
>
> I'm sure there's a way to do it. I'll be honest. I have no idea where
> to start. If it's not clear what I'm trying to do, please let me
> know.
> Any help at all will be appreciated.
>
>
> Thanks!

It appears you want to concatenate every 50 rows of data in Column A into
single String values (using a semi-colon as a delimiter) and place them back
into Column A after first clearing all the data in Column A (that is, Column
A's original data structure will be lost). If that is correct, this
subroutine should do what you want (although I'd suggest testing it out on
sample data first to be sure the end result is actually what you want)...

Sub GroupBy50sColumnA()
Dim X As Long, Z As Long
Dim LastRow As Long
Dim CombinedRows As String
Dim CombinedValues() As String
Const GroupCount As Long = 50
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
For X = 1 To LastRow Step GroupCount
CombinedRows = Cells(X, "A").Value
For Z = 1 To GroupCount - 1
If Z + X > LastRow Then Exit For
CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value
Next
CombinedValues(1 + X \ GroupCount) = CombinedRows
Next
Range("A:A").ClearContents
For X = 1 To UBound(CombinedValues)
Cells(X, "A").Value = CombinedValues(X)
Next
End Sub

Rick


"AndrewJ" <(E-Mail Removed)> wrote in message
news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
> I'm looking to the experts on this one as I'm a very basic VBA user
> and not sure where to start. I'm looking for some help in buildig a
> macro that could do the following. Take a column of data such as
> below
> and then Consolidate the data 50 cells at a time with the data
> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>
> A
> 1 11111
> 2 11112
> 3 11113
> 4 .....
>
>
> 2223 12223
>
>
> to
>
>
> A
> 1 11111;11112;11113...etc
> 2 11151;11152;11153...etc
>
>
> I'm sure there's a way to do it. I'll be honest. I have no idea where
> to start. If it's not clear what I'm trying to do, please let me
> know.
> Any help at all will be appreciated.
>
>
> Thanks!

> .... (using a semi-colon as a delimiter)
>
> CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value

And, so of course, I mistyped a colon in place of the semi-colon I meant to
use. In my previously posted code, change the colon to a semi-colon for the
above line of code.

Rick

Hi Rick,

I am trying to learn from this example.
If you wouldn't mind, could you clarify how sizing the array to the mod
returned here works for this. I can't understand it yet, and getting lazy I
suppose.

ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)


(Also, this routine fails when row count is too high. Over about 2700
lines or so) Not sure why, but it goes out of range)

Thanks,

Ariel

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> It appears you want to concatenate every 50 rows of data in Column A into
> single String values (using a semi-colon as a delimiter) and place them
> back into Column A after first clearing all the data in Column A (that is,
> Column A's original data structure will be lost). If that is correct, this
> subroutine should do what you want (although I'd suggest testing it out on
> sample data first to be sure the end result is actually what you want)...
>
> Sub GroupBy50sColumnA()
> Dim X As Long, Z As Long
> Dim LastRow As Long
> Dim CombinedRows As String
> Dim CombinedValues() As String
> Const GroupCount As Long = 50
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
> For X = 1 To LastRow Step GroupCount
> CombinedRows = Cells(X, "A").Value
> For Z = 1 To GroupCount - 1
> If Z + X > LastRow Then Exit For
> CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value
> Next
> CombinedValues(1 + X \ GroupCount) = CombinedRows
> Next
> Range("A:A").ClearContents
> For X = 1 To UBound(CombinedValues)
> Cells(X, "A").Value = CombinedValues(X)
> Next
> End Sub
>
> Rick
>
>
> "AndrewJ" <(E-Mail Removed)> wrote in message
> news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
>> I'm looking to the experts on this one as I'm a very basic VBA user
>> and not sure where to start. I'm looking for some help in buildig a
>> macro that could do the following. Take a column of data such as
>> below
>> and then Consolidate the data 50 cells at a time with the data
>> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>>
>> A
>> 1 11111
>> 2 11112
>> 3 11113
>> 4 .....
>>
>>
>> 2223 12223
>>
>>
>> to
>>
>>
>> A
>> 1 11111;11112;11113...etc
>> 2 11151;11152;11153...etc
>>
>>
>> I'm sure there's a way to do it. I'll be honest. I have no idea where
>> to start. If it's not clear what I'm trying to do, please let me
>> know.
>> Any help at all will be appreciated.
>>
>>
>> Thanks!
>

Well, stop trying to learn from it... the use of the Mod operator is
completely wrong! Apparently, through a lucky (I guess actually, unlucky)
accident, the sample data I created for my test worked using it; but it IS
wrong. I did get it right later on in this statement...

CombinedValues(1 + X \ GroupCount) = CombinedRows

which appears after the nested For-Next loop. The integer division I used in
the above statement is what I should have used in my ReDim statement. The
correct ReDim statement to use in my code is this...

ReDim CombinedValues(1 To 1 + LastRow \ GroupCount)

I'm sorry for any confusion my error may have caused. Here is the code, with
the correction, repeated here for your convenience...

Sub GroupBy50sColumnA()
Dim X As Long, Z As Long
Dim LastRow As Long
Dim CombinedRows As String
Dim CombinedValues() As String
Const GroupCount As Long = 50
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
ReDim CombinedValues(1 To 1 + LastRow \ GroupCount)
For X = 1 To LastRow Step GroupCount
CombinedRows = Cells(X, "A").Value
For Z = 1 To GroupCount - 1
If Z + X > LastRow Then Exit For
CombinedRows = CombinedRows & ";" & Cells(Z + X, "A").Value
Next
CombinedValues(1 + X \ GroupCount) = CombinedRows
Next
Range("A:A").ClearContents
For X = 1 To UBound(CombinedValues)
Cells(X, "A").Value = CombinedValues(X)
Next
End Sub

Rick



"AD" <(E-Mail Removed)> wrote in message
news:u%23$(E-Mail Removed)...
> Hi Rick,
>
> I am trying to learn from this example.
> If you wouldn't mind, could you clarify how sizing the array to the mod
> returned here works for this. I can't understand it yet, and getting lazy
> I suppose.
>
> ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
>
>
> (Also, this routine fails when row count is too high. Over about 2700
> lines or so) Not sure why, but it goes out of range)
>
> Thanks,
>
> Ariel
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:%(E-Mail Removed)...
>> It appears you want to concatenate every 50 rows of data in Column A into
>> single String values (using a semi-colon as a delimiter) and place them
>> back into Column A after first clearing all the data in Column A (that
>> is, Column A's original data structure will be lost). If that is correct,
>> this subroutine should do what you want (although I'd suggest testing it
>> out on sample data first to be sure the end result is actually what you
>> want)...
>>
>> Sub GroupBy50sColumnA()
>> Dim X As Long, Z As Long
>> Dim LastRow As Long
>> Dim CombinedRows As String
>> Dim CombinedValues() As String
>> Const GroupCount As Long = 50
>> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
>> ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
>> For X = 1 To LastRow Step GroupCount
>> CombinedRows = Cells(X, "A").Value
>> For Z = 1 To GroupCount - 1
>> If Z + X > LastRow Then Exit For
>> CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value
>> Next
>> CombinedValues(1 + X \ GroupCount) = CombinedRows
>> Next
>> Range("A:A").ClearContents
>> For X = 1 To UBound(CombinedValues)
>> Cells(X, "A").Value = CombinedValues(X)
>> Next
>> End Sub
>>
>> Rick
>>
>>
>> "AndrewJ" <(E-Mail Removed)> wrote in message
>> news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
>>> I'm looking to the experts on this one as I'm a very basic VBA user
>>> and not sure where to start. I'm looking for some help in buildig a
>>> macro that could do the following. Take a column of data such as
>>> below
>>> and then Consolidate the data 50 cells at a time with the data
>>> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>>>
>>> A
>>> 1 11111
>>> 2 11112
>>> 3 11113
>>> 4 .....
>>>
>>>
>>> 2223 12223
>>>
>>>
>>> to
>>>
>>>
>>> A
>>> 1 11111;11112;11113...etc
>>> 2 11151;11152;11153...etc
>>>
>>>
>>> I'm sure there's a way to do it. I'll be honest. I have no idea where
>>> to start. If it's not clear what I'm trying to do, please let me
>>> know.
>>> Any help at all will be appreciated.
>>>
>>>
>>> Thanks!
>>
>
>

Assuming the data is always in column A and the first blank cell is where
you want it to stop, the following code should do it

Public Sub consolidate()
Dim intRowMarker As Integer, intCurrentRow As Integer, strConsolidation
As String, intCount As Integer
intRowMarker = 1
intCurrentRow = 1
strConsolidation = ""
Do
For intCount = 1 To 50
If (range("A" & intCurrentRow).Value = "") Then Exit For
strConsolidation = strConsolidation & range("A" &
intCurrentRow).Value & ";"
intCurrentRow = intCurrentRow + 1
Next intCount
range("A" & intRowMarker).Value = strConsolidation
strConsolidation = ""
intRowMarker = intRowMarker + 1
Loop While (range("A" & intCurrentRow).Value <> "")
End Sub

"AndrewJ" <(E-Mail Removed)> wrote in message
news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
> I'm looking to the experts on this one as I'm a very basic VBA user
> and not sure where to start. I'm looking for some help in buildig a
> macro that could do the following. Take a column of data such as
> below
> and then Consolidate the data 50 cells at a time with the data
> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>
> A
> 1 11111
> 2 11112
> 3 11113
> 4 .....
>
>
> 2223 12223
>
>
> to
>
>
> A
> 1 11111;11112;11113...etc
> 2 11151;11152;11153...etc
>
>
> I'm sure there's a way to do it. I'll be honest. I have no idea where
> to start. If it's not clear what I'm trying to do, please let me
> know.
> Any help at all will be appreciated.
>
>
> Thanks!

Thanks Rick, your code worked beautifully and was exactly what I was
looking for. I still need to build out for what I want to accomplish,
but this particular part I didn't know where to start. Thanks again.

Andrew

You are welcome. I'd like to suggest the next time you ask a question on the
newsgroups... don't simply the question and/or examples for us... ask the
actual question for the actual situation you have and give us the actual
setup you have... the solution to a simplified setup usually doesn't
translate linearly to the solution required for the actual condition causing
the question.

Rick

"AndrewJ" <(E-Mail Removed)> wrote in message
news:262e506d-1bb1-4375-b29e-(E-Mail Removed)...
> Thanks Rick, your code worked beautifully and was exactly what I was
> looking for. I still need to build out for what I want to accomplish,
> but this particular part I didn't know where to start. Thanks again.
>
> Andrew
>

Thanks Rick


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> Well, stop trying to learn from it... the use of the Mod operator is
> completely wrong! Apparently, through a lucky (I guess actually, unlucky)
> accident, the sample data I created for my test worked using it; but it IS
> wrong. I did get it right later on in this statement...
>
> CombinedValues(1 + X \ GroupCount) = CombinedRows
>
> which appears after the nested For-Next loop. The integer division I used
> in the above statement is what I should have used in my ReDim statement.
> The correct ReDim statement to use in my code is this...
>
> ReDim CombinedValues(1 To 1 + LastRow \ GroupCount)
>
> I'm sorry for any confusion my error may have caused. Here is the code,
> with the correction, repeated here for your convenience...
>
> Sub GroupBy50sColumnA()
> Dim X As Long, Z As Long
> Dim LastRow As Long
> Dim CombinedRows As String
> Dim CombinedValues() As String
> Const GroupCount As Long = 50
> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
> ReDim CombinedValues(1 To 1 + LastRow \ GroupCount)
> For X = 1 To LastRow Step GroupCount
> CombinedRows = Cells(X, "A").Value
> For Z = 1 To GroupCount - 1
> If Z + X > LastRow Then Exit For
> CombinedRows = CombinedRows & ";" & Cells(Z + X, "A").Value
> Next
> CombinedValues(1 + X \ GroupCount) = CombinedRows
> Next
> Range("A:A").ClearContents
> For X = 1 To UBound(CombinedValues)
> Cells(X, "A").Value = CombinedValues(X)
> Next
> End Sub
>
> Rick
>
>
>
> "AD" <(E-Mail Removed)> wrote in message
> news:u%23$(E-Mail Removed)...
>> Hi Rick,
>>
>> I am trying to learn from this example.
>> If you wouldn't mind, could you clarify how sizing the array to the mod
>> returned here works for this. I can't understand it yet, and getting
>> lazy I suppose.
>>
>> ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
>>
>>
>> (Also, this routine fails when row count is too high. Over about 2700
>> lines or so) Not sure why, but it goes out of range)
>>
>> Thanks,
>>
>> Ariel
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote
>> in message news:%(E-Mail Removed)...
>>> It appears you want to concatenate every 50 rows of data in Column A
>>> into single String values (using a semi-colon as a delimiter) and place
>>> them back into Column A after first clearing all the data in Column A
>>> (that is, Column A's original data structure will be lost). If that is
>>> correct, this subroutine should do what you want (although I'd suggest
>>> testing it out on sample data first to be sure the end result is
>>> actually what you want)...
>>>
>>> Sub GroupBy50sColumnA()
>>> Dim X As Long, Z As Long
>>> Dim LastRow As Long
>>> Dim CombinedRows As String
>>> Dim CombinedValues() As String
>>> Const GroupCount As Long = 50
>>> LastRow = Cells(Rows.Count, "A").End(xlUp).Row
>>> ReDim CombinedValues(1 To 1 + LastRow Mod GroupCount)
>>> For X = 1 To LastRow Step GroupCount
>>> CombinedRows = Cells(X, "A").Value
>>> For Z = 1 To GroupCount - 1
>>> If Z + X > LastRow Then Exit For
>>> CombinedRows = CombinedRows & ":" & Cells(Z + X, "A").Value
>>> Next
>>> CombinedValues(1 + X \ GroupCount) = CombinedRows
>>> Next
>>> Range("A:A").ClearContents
>>> For X = 1 To UBound(CombinedValues)
>>> Cells(X, "A").Value = CombinedValues(X)
>>> Next
>>> End Sub
>>>
>>> Rick
>>>
>>>
>>> "AndrewJ" <(E-Mail Removed)> wrote in message
>>> news:cb05bf46-6f03-40a4-9707-(E-Mail Removed)...
>>>> I'm looking to the experts on this one as I'm a very basic VBA user
>>>> and not sure where to start. I'm looking for some help in buildig a
>>>> macro that could do the following. Take a column of data such as
>>>> below
>>>> and then Consolidate the data 50 cells at a time with the data
>>>> seperated by a semi-colon(no spaces) into 1 cell. For Instance:
>>>>
>>>> A
>>>> 1 11111
>>>> 2 11112
>>>> 3 11113
>>>> 4 .....
>>>>
>>>>
>>>> 2223 12223
>>>>
>>>>
>>>> to
>>>>
>>>>
>>>> A
>>>> 1 11111;11112;11113...etc
>>>> 2 11151;11152;11153...etc
>>>>
>>>>
>>>> I'm sure there's a way to do it. I'll be honest. I have no idea where
>>>> to start. If it's not clear what I'm trying to do, please let me
>>>> know.
>>>> Any help at all will be appreciated.
>>>>
>>>>
>>>> Thanks!
>>>
>>
>>
>