Consolidating Data

[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]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

.... (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

 

[AD]

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\)]

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

 

[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

 

[AndrewJ]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[AD]

Thanks Rick

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