Consolidate Data #2

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!

0
ajramos3 (1)
4/9/2008 2:55:47 AM
excel 39879 articles. 2 followers. Follow

3 Replies
534 Views

Similar Articles

[PageSpeed] 46

Try the following:

Public Function consolidate(rngConsolidate As range) As String
   Dim cell As range
   consolidate = ""
   For Each cell In rngConsolidate
      consolidate = consolidate & cell.Value & ";"
   Next
End Function



"AndrewJ" <ajramos3@comcast.net> wrote in message 
news:de90c383-1016-446b-802b-55508f1d7eea@y21g2000hsf.googlegroups.com...
> 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!
> 


0
Gerry3278 (36)
4/9/2008 10:47:18 AM
Check your other post, too.

AndrewJ wrote:
> 
> 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!

-- 

Dave Peterson
0
petersod (12005)
4/9/2008 12:16:16 PM
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" <ajramos3@comcast.net> wrote in message 
news:de90c383-1016-446b-802b-55508f1d7eea@y21g2000hsf.googlegroups.com...
> 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!
> 


0
Gerry3278 (36)
4/9/2008 3:56:13 PM
Reply:

Similar Artilces: