ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Joining similar rows macro (https://www.excelbanter.com/excel-programming/397003-joining-similar-rows-macro.html)

John Wright
Joining similar rows macro
 
I get an excel sheet from our accounting department that I import into a
database for reporting. The data is straight forward in most cases, but
today I noticed that there are a lot of rows that are duplicated except for
two columns. Is there a macro or a way to run a script that would look at
these rows and compare them and if all the columns in the row match except
for these two, combine the the columns (these are number columns so I would
like to add the numbers) and create a single row? If so this would really
help me get the reports they need. Any help is appreciated. Here is an
example of the rows I would like combined

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 0 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 61.68 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 4210 ENGSVCS
Engineering Services US US - -


This is what I would like to see

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 4271.68
ENGSVCS Engineering Services US US - -



Thanks.

John

joel
Joining similar rows macro
 
It is hard to tell what columns the data is from your posting. Modify the
three constants to match you worksheet. this should work

Sub combinerows()

Const LastCol = "W"
Const CombineCol1 = "R"
Const CombineCol2 = "S"

RowCount = 1
Do While Cells(RowCount + 1, "A") < ""
Match = True
For ColCount = 1 To Cells(1, "W").Column
If (ColCount < Cells(1, CombineCol1).Column) And _
(ColCount < Cells(1, CombineCol2).Column) Then

If Cells(RowCount, ColCount).Value < _
Cells(RowCount + 1, ColCount).Value Then

Match = False
Exit For
End If

End If
Next ColCount

If Match = True Then
Cells(RowCount, CombineCol1) = _
Cells(RowCount, CombineCol1) + _
Cells(RowCount + 1, CombineCol1)
Cells(RowCount, CombineCol2) = _
Cells(RowCount, CombineCol2) + _
Cells(RowCount + 1, CombineCol2)
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop


End Sub


"John Wright" wrote:

I get an excel sheet from our accounting department that I import into a
database for reporting. The data is straight forward in most cases, but
today I noticed that there are a lot of rows that are duplicated except for
two columns. Is there a macro or a way to run a script that would look at
these rows and compare them and if all the columns in the row match except
for these two, combine the the columns (these are number columns so I would
like to add the numbers) and create a single row? If so this would really
help me get the reports they need. Any help is appreciated. Here is an
example of the rows I would like combined

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 0 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 61.68 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 4210 ENGSVCS
Engineering Services US US - -


This is what I would like to see

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 4271.68
ENGSVCS Engineering Services US US - -



Thanks.

John

John Wright
Joining similar rows macro
 
Sorry about the confusion let me clear it up. If I have the following rows of data (made a comma seperated string):

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 6028, 0, ENGSVCS,Engineering Services US, US,-,-

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 0,61.68, ENGSVCS,Engineering Services US, US,-,-

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 0,4271.68, ENGSVCS,Engineering Services US, US,-,-

I want to get the following:

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 6028, 4271.68, ENGSVCS,Engineering Services US, US,-,-


So I want to check 17 columns for a match. If all 17 columns match, then I want to combine the rows and add the numberic values (see the bold items). I am not an excel guru so how would I run the script below for the page to get a new scrubbed worksheet?

John




"Joel" wrote in message ...
It is hard to tell what columns the data is from your posting. Modify the
three constants to match you worksheet. this should work

Sub combinerows()

Const LastCol = "W"
Const CombineCol1 = "R"
Const CombineCol2 = "S"

RowCount = 1
Do While Cells(RowCount + 1, "A") < ""
Match = True
For ColCount = 1 To Cells(1, "W").Column
If (ColCount < Cells(1, CombineCol1).Column) And _
(ColCount < Cells(1, CombineCol2).Column) Then

If Cells(RowCount, ColCount).Value < _
Cells(RowCount + 1, ColCount).Value Then

Match = False
Exit For
End If

End If
Next ColCount

If Match = True Then
Cells(RowCount, CombineCol1) = _
Cells(RowCount, CombineCol1) + _
Cells(RowCount + 1, CombineCol1)
Cells(RowCount, CombineCol2) = _
Cells(RowCount, CombineCol2) + _
Cells(RowCount + 1, CombineCol2)
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop


End Sub


"John Wright" wrote:

I get an excel sheet from our accounting department that I import into a
database for reporting. The data is straight forward in most cases, but
today I noticed that there are a lot of rows that are duplicated except for
two columns. Is there a macro or a way to run a script that would look at
these rows and compare them and if all the columns in the row match except
for these two, combine the the columns (these are number columns so I would
like to add the numbers) and create a single row? If so this would really
help me get the reports they need. Any help is appreciated. Here is an
example of the rows I would like combined

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 0 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 61.68 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 4210 ENGSVCS
Engineering Services US US - -


This is what I would like to see

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 4271.68
ENGSVCS Engineering Services US US - -



Thanks.

John

joel
Joining similar rows macro
 

there seems to be a prblem still with the number of columns. I belive your
comma seperate datta has 21 columns, not 17. Doesn't matter the code is
generic. Just change the statements below to fix the problems.

Chage from
Const LastCol = "W"
Const CombineCol1 = "R"
Const CombineCol2 = "S"


to
Const LastCol = "Q"
Const CombineCol1 = "N"
Const CombineCol2 = "O"

"John Wright" wrote:

Sorry about the confusion let me clear it up. If I have the following rows of data (made a comma seperated string):

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 6028, 0, ENGSVCS,Engineering Services US, US,-,-

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 0,61.68, ENGSVCS,Engineering Services US, US,-,-

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 0,4271.68, ENGSVCS,Engineering Services US, US,-,-

I want to get the following:

1-Jul-07, FIRSTENERGY, 605894, 0, Region 21, Fuel, DLGQ 32334, FA_17RFA,602381, MPFIR US MULTIPLE PLANTS, FIRSTENERGY,17x17,STD, ENG, 6028, 4271.68, ENGSVCS,Engineering Services US, US,-,-


So I want to check 17 columns for a match. If all 17 columns match, then I want to combine the rows and add the numberic values (see the bold items). I am not an excel guru so how would I run the script below for the page to get a new scrubbed worksheet?

John




"Joel" wrote in message ...
It is hard to tell what columns the data is from your posting. Modify the
three constants to match you worksheet. this should work

Sub combinerows()

Const LastCol = "W"
Const CombineCol1 = "R"
Const CombineCol2 = "S"

RowCount = 1
Do While Cells(RowCount + 1, "A") < ""
Match = True
For ColCount = 1 To Cells(1, "W").Column
If (ColCount < Cells(1, CombineCol1).Column) And _
(ColCount < Cells(1, CombineCol2).Column) Then

If Cells(RowCount, ColCount).Value < _
Cells(RowCount + 1, ColCount).Value Then

Match = False
Exit For
End If

End If
Next ColCount

If Match = True Then
Cells(RowCount, CombineCol1) = _
Cells(RowCount, CombineCol1) + _
Cells(RowCount + 1, CombineCol1)
Cells(RowCount, CombineCol2) = _
Cells(RowCount, CombineCol2) + _
Cells(RowCount + 1, CombineCol2)
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop


End Sub


"John Wright" wrote:

I get an excel sheet from our accounting department that I import into a
database for reporting. The data is straight forward in most cases, but
today I noticed that there are a lot of rows that are duplicated except for
two columns. Is there a macro or a way to run a script that would look at
these rows and compare them and if all the columns in the row match except
for these two, combine the the columns (these are number columns so I would
like to add the numbers) and create a single row? If so this would really
help me get the reports they need. Any help is appreciated. Here is an
example of the rows I would like combined

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 0 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 61.68 ENGSVCS
Engineering Services US US - -
1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 0 4210 ENGSVCS
Engineering Services US US - -


This is what I would like to see

1-Jul-07 FIRSTENERGY 605894 0 Region 21 Fuel DLGQ 32334 FA_17RFA
602381 MPFIR US MULTIPLE PLANTS FIRSTENERGY 17x17STD ENG 6028 4271.68
ENGSVCS Engineering Services US US - -



Thanks.

John


All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com