Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Hi
I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Sub CombineRows()
RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Thank you for such a quick response. It works.
Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
could you also exclude the duplicates in column B.
Thank you "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
If you change the line in the second if statement that reads:
Range("B" & RowCount) = Data To Range("C" & RowCount) = Data Then your "answer" will be stored in Column C instead of column B. "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Thank you - now I just need to exclude the duplicates
"GB" wrote: If you change the line in the second if statement that reads: Range("B" & RowCount) = Data To Range("C" & RowCount) = Data Then your "answer" will be stored in Column C instead of column B. "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
This could be a tough question to answer without more information from you.
If I assume that you only want to capture the first instance of the data in column B the first time (instead of the last time) that it is used, then within the first if statement, just after the else statement, change this to perform a search of the data from row 1 to the row before rowcount by using something like a selection.find operation to find the value of the current "Range". If the value is not found then run the Data = Data & ";" ...etc. if the data is found then do nothing. So the following will combine the rows into a single cell and maintain your original data integrity, placing the result (data not previously found in column B) in the row that contains data in column A. Option Explicit Public Sub testCombineRows() Dim RowCount As Long Dim Data As String Dim FirstRowData As Long RowCount = 1 Data = "" FirstRowData = RowCount Do While Range("B" & RowCount) < "" If RowCount 1 Then Range("B1:B" & RowCount - 1).Select Else 'At the first row, but still want to add it. End If If RowCount = 1 Then Data = Range("B" & RowCount) ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing And RowCount 1 And Data = "" Then Data = Range("B" & RowCount) ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing Then Data = Data & ";" & Range("B" & RowCount) Else 'The data has been found before End If If Range("A" & (RowCount + 1)) < "" Or Range("B" & (RowCount + 1)) = "" Then Range("C" & FirstRowData) = Data Data = "" RowCount = RowCount + 1 FirstRowData = RowCount Else RowCount = RowCount + 1 'Rows(RowCount).Delete End If Loop Worked for me, hope it does what you wanted. (I commented out the Rows(RowCount).Delete line so that no data would not be deleted. End Sub "nmpb" wrote: could you also exclude the duplicates in column B. Thank you "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
See thread above that asked the question about excluding duplicates.
"nmpb" wrote: Thank you - now I just need to exclude the duplicates "GB" wrote: If you change the line in the second if statement that reads: Range("B" & RowCount) = Data To Range("C" & RowCount) = Data Then your "answer" will be stored in Column C instead of column B. "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Wow! GB - THANKS
I did not see this reply before I posted mine. I will test and let you know the outcome. "GB" wrote: This could be a tough question to answer without more information from you. If I assume that you only want to capture the first instance of the data in column B the first time (instead of the last time) that it is used, then within the first if statement, just after the else statement, change this to perform a search of the data from row 1 to the row before rowcount by using something like a selection.find operation to find the value of the current "Range". If the value is not found then run the Data = Data & ";" ...etc. if the data is found then do nothing. So the following will combine the rows into a single cell and maintain your original data integrity, placing the result (data not previously found in column B) in the row that contains data in column A. Option Explicit Public Sub testCombineRows() Dim RowCount As Long Dim Data As String Dim FirstRowData As Long RowCount = 1 Data = "" FirstRowData = RowCount Do While Range("B" & RowCount) < "" If RowCount 1 Then Range("B1:B" & RowCount - 1).Select Else 'At the first row, but still want to add it. End If If RowCount = 1 Then Data = Range("B" & RowCount) ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing And RowCount 1 And Data = "" Then Data = Range("B" & RowCount) ElseIf Selection.Find(Range("B" & RowCount)) Is Nothing Then Data = Data & ";" & Range("B" & RowCount) Else 'The data has been found before End If If Range("A" & (RowCount + 1)) < "" Or Range("B" & (RowCount + 1)) = "" Then Range("C" & FirstRowData) = Data Data = "" RowCount = RowCount + 1 FirstRowData = RowCount Else RowCount = RowCount + 1 'Rows(RowCount).Delete End If Loop Worked for me, hope it does what you wanted. (I commented out the Rows(RowCount).Delete line so that no data would not be deleted. End Sub "nmpb" wrote: could you also exclude the duplicates in column B. Thank you "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
This code assumes the data in column B is in ascending order.
Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Range("B" & RowCount) If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
This does do want I want, so THANK YOU. I have done some testing and the
prg needs a small adjustment: 1) where there is only only entry, ie column A and B match - the output in C is truncating the leading zeros. Output: A B C 1 000034028105 000034028105 34028105 2 000034061517 000034061517 34061517 3 000034008980 000034008980 34008980 2) could you add the len of the output into column D please. I tried using len, but for the ones that get joined I am not getting the correct answer, it just get the answer 8. 3) It seems to miss the last entry. I have worked around this by adding a 1 to columns A and B. "Joel" wrote: This code assumes the data in column B is in ascending order. Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Range("B" & RowCount) If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
Are the cells shown formated as Text. I made changes below to force
everything the string data and store the data as Text in the worksheet. You are concatenating the number together with a semicolon which will automatically convert these numbers as text. Sub CombineRows() Dim NewData as String RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Format(Range("B" & RowCount).Text,"@") If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount).numberformat = "@" Range("C" & RowCount).Text = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: This does do want I want, so THANK YOU. I have done some testing and the prg needs a small adjustment: 1) where there is only only entry, ie column A and B match - the output in C is truncating the leading zeros. Output: A B C 1 000034028105 000034028105 34028105 2 000034061517 000034061517 34061517 3 000034008980 000034008980 34008980 2) could you add the len of the output into column D please. I tried using len, but for the ones that get joined I am not getting the correct answer, it just get the answer 8. 3) It seems to miss the last entry. I have worked around this by adding a 1 to columns A and B. "Joel" wrote: This code assumes the data in column B is in ascending order. Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Range("B" & RowCount) If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
I only saw the 1st request on your last posting, Missed items 2 & 3. Here
are all the changes Sub CombineRows() Dim NewData As String RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Format(Range("B" & RowCount).Text, "@") If Data = "" Then Data = NewData Else If NewData < LastData Then Data = Data & ";" & NewData End If End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Or _ Range("B" & (RowCount + 1)) = "" Then Range("C" & RowCount).NumberFormat = "@" Range("C" & RowCount).Text = Data Range("D" & RowCount) = Len(Data) Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If- Loop End Sub "Joel" wrote: Are the cells shown formated as Text. I made changes below to force everything the string data and store the data as Text in the worksheet. You are concatenating the number together with a semicolon which will automatically convert these numbers as text. Sub CombineRows() Dim NewData as String RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Format(Range("B" & RowCount).Text,"@") If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount).numberformat = "@" Range("C" & RowCount).Text = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: This does do want I want, so THANK YOU. I have done some testing and the prg needs a small adjustment: 1) where there is only only entry, ie column A and B match - the output in C is truncating the leading zeros. Output: A B C 1 000034028105 000034028105 34028105 2 000034061517 000034061517 34061517 3 000034008980 000034008980 34008980 2) could you add the len of the output into column D please. I tried using len, but for the ones that get joined I am not getting the correct answer, it just get the answer 8. 3) It seems to miss the last entry. I have worked around this by adding a 1 to columns A and B. "Joel" wrote: This code assumes the data in column B is in ascending order. Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Range("B" & RowCount) If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concat rows -
So, nmpb, to restate your original question/request, you wanted to
concatenate all data in column B up to the next instance of data in Column A or the end of Column B placing the result in Column C (and subsequently the length of the concatenated text in Column D) at the row that meets either of the above 2 conditions given the following: 1. All data is sequential in Columns A and B and therefore a duplicate number would only appear immediately after the first instance, and not elsewhere within the data. (FYI, the original dataset did not present itself as sequential, row 6 is less than row 7; however, row 7 is greater than row 8. Therefore if a duplicate did exist between row 6 and row 8 for example, the duplicate would not be excluded by the above code.) 2. Upon concatenation, the only results needed a 2.a The concatenated text in Column C, 2.b The length of the text in Column C to be placed in Column D, 2.c The data that is in Column B is only needed for the last row before the end of the data or the next instance of data in Column A, and 2.d The data in Column A is only needed if there is only one instance of data in Column B associated with the data in Column A. 3. The total number of rows is to be reduced by the efforts of the concatenation. 4. Data in Column A is used to indicate a one or many relationship between Column A and Column B data based on the existence or absence of data in Column A, respectively. "Joel" wrote: I only saw the 1st request on your last posting, Missed items 2 & 3. Here are all the changes Sub CombineRows() Dim NewData As String RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Format(Range("B" & RowCount).Text, "@") If Data = "" Then Data = NewData Else If NewData < LastData Then Data = Data & ";" & NewData End If End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Or _ Range("B" & (RowCount + 1)) = "" Then Range("C" & RowCount).NumberFormat = "@" Range("C" & RowCount).Text = Data Range("D" & RowCount) = Len(Data) Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If- Loop End Sub "Joel" wrote: Are the cells shown formated as Text. I made changes below to force everything the string data and store the data as Text in the worksheet. You are concatenating the number together with a semicolon which will automatically convert these numbers as text. Sub CombineRows() Dim NewData as String RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Format(Range("B" & RowCount).Text,"@") If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount).numberformat = "@" Range("C" & RowCount).Text = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: This does do want I want, so THANK YOU. I have done some testing and the prg needs a small adjustment: 1) where there is only only entry, ie column A and B match - the output in C is truncating the leading zeros. Output: A B C 1 000034028105 000034028105 34028105 2 000034061517 000034061517 34061517 3 000034008980 000034008980 34008980 2) could you add the len of the output into column D please. I tried using len, but for the ones that get joined I am not getting the correct answer, it just get the answer 8. 3) It seems to miss the last entry. I have worked around this by adding a 1 to columns A and B. "Joel" wrote: This code assumes the data in column B is in ascending order. Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" NewData = Range("B" & RowCount) If Data = "" Then Data = NewData Else if NewData < LastData then Data = Data & ";" & NewData End if End If LastData = NewData If Range("A" & (RowCount + 1)) < "" Then Range("C" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Thank you for such a quick response. It works. Can you change it so that the result appears in column C please so it does not overwrite the data in column B. "Joel" wrote: Sub CombineRows() RowCount = 1 Data = "" Do While Range("B" & RowCount) < "" If Data = "" Then Data = Range("B" & RowCount) Else Data = Data & ";" & Range("B" & RowCount) End If If Range("A" & (RowCount + 1)) < "" Then Range("B" & RowCount) = Data Data = "" RowCount = RowCount + 1 Else Rows(RowCount).Delete End If Loop End Sub "nmpb" wrote: Hi I need a program to be able to concate all the rows in B until the cell in column A is not blank. would also want a separator between them. ie row C1 = 000034001570;000034001571;000034001582;00003400158 9 C6 = 000034032303 A B 1 000034001570 000034001570 2 000034001571 3 000034001582 4 000034001589 5 000034001589 6 000034032303 000034032303 7 000034066598 000034066598 8 000034017214 000034017214 9 000034017215 10 000034019302 11 000034019303 The issue is that I have a sheet of over 40,000 rows, also I have noticed that I have duplicates in the column B, which will have to be excluded. Its been a while since I've done any programming so any help appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concat rows | Excel Worksheet Functions | |||
howto: concat (x1:x3) | New Users to Excel | |||
Concat Macro help... | Excel Discussion (Misc queries) | |||
Concat values in two or more rows based on id and eliminate duplicates | Excel Programming | |||
Concat + dates | Excel Programming |