Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
.... (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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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)" wrote in message ... 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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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" wrote in message ... 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)" wrote in message ... 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" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Consolidating Data
Thanks Rick
"Rick Rothstein (MVP - VB)" wrote in message ... 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" wrote in message ... 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)" wrote in message ... 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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Consolidating data | Excel Discussion (Misc queries) | |||
Consolidating Data | Excel Discussion (Misc queries) | |||
Consolidating Data | Excel Worksheet Functions | |||
Need advice : consolidating data from multiple CSV files in Excel - External data handling | Excel Programming | |||
Consolidating data?? | Excel Worksheet Functions |