Consolidate Data


A

AndrewJ

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!
 
Ad

Advertisements

G

GerryGerry

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
 
D

Dave Peterson

Check your other post, too.
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!
 
Ad

Advertisements

G

GerryGerry

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top