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 -
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 |
#6
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 |
#7
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 |
#8
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 |
#9
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 |
#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 |
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 |