ExcelBanter - combining rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - combining rows (https://www.excelbanter.com/excel-programming/436323-combining-rows.html)

I am loking for a macro that I can put in a command button that will look
through a work sheet in column "A" for identical numerical values. once found
take all rows with identical values and combine the rows into one in a
different worksheet. when combined it must add together the different values
of the rows in columns "f", "g" "h", "i" and "j".

can this be done?

Could using SUMIF do it?

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
I am loking for a macro that I can put in a command button that will look
through a work sheet in column "A" for identical numerical values. once
found
take all rows with identical values and combine the rows into one in a
different worksheet. when combined it must add together the different
values
of the rows in columns "f", "g" "h", "i" and "j".

can this be done?

Thank you Don, I just sent you the information.

Vern

"Don Guillett" wrote:

Could using SUMIF do it?

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
I am loking for a macro that I can put in a command button that will look
through a work sheet in column "A" for identical numerical values. once
found
take all rows with identical values and combine the rows into one in a
different worksheet. when combined it must add together the different
values
of the rows in columns "f", "g" "h", "i" and "j".

can this be done?

.

=IF(A12="","",VLOOKUP(A12,ESTIMATE!$A$9:$B$15,2,0) )
and
=IF($A12="","",SUMIF(ESTIMATE!$A$9:$A$15,$A12,ESTI MATE!K$9:K$15))
copied across and down or a macro

Sub UpdateFromEstimteSAS()
Set ds = Sheets("Budget")
With Sheets("Estimate")
Lr = .Cells(Rows.Count, 1).End(xlUp).Row
With .Range("a8:a" & Lr)
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
..Copy ds.Range("a17")
..AutoFilter
..AutoFilter
End With
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row
ds.Range("b19:b" & dlr).Formula = _
"=IF($A19="""","""",VLOOKUP($A19,ESTIMATE!$A$9:$B$ 15,2,0))"
ds.Range("c19:h" & dlr).Formula = _
"=IF($a19="""","""",SUMIF(ESTIMATE!$A$9:$A$15,$A19 ,ESTIMATE!K$9:K$15))"
ds.Range("b19:h" & dlr).Value = ds.Range("b19:h" & dlr).Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
Thank you Don, I just sent you the information.

Vern

"Don Guillett" wrote:

Could using SUMIF do it?

If desired, send your file to my address below. I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
I am loking for a macro that I can put in a command button that will
look
through a work sheet in column "A" for identical numerical values. once
found
take all rows with identical values and combine the rows into one in a
different worksheet. when combined it must add together the different
values
of the rows in columns "f", "g" "h", "i" and "j".

can this be done?

.

What I sent over was just a small portion, is there a way to have this look
at the entire sheet? there could possibly be up to 500 rows, not just a8 - a19

thank you

"Don Guillett" wrote:

=IF(A12="","",VLOOKUP(A12,ESTIMATE!$A$9:$B$15,2,0) )
and
=IF($A12="","",SUMIF(ESTIMATE!$A$9:$A$15,$A12,ESTI MATE!K$9:K$15))
copied across and down or a macro

Sub UpdateFromEstimteSAS()
Set ds = Sheets("Budget")
With Sheets("Estimate")
Lr = .Cells(Rows.Count, 1).End(xlUp).Row
With .Range("a8:a" & Lr)
..AdvancedFilter Action:=xlFilterInPlace, Unique:=True
..Copy ds.Range("a17")
..AutoFilter
..AutoFilter
End With
dlr = ds.Cells(Rows.Count, 1).End(xlUp).Row
ds.Range("b19:b" & dlr).Formula = _
"=IF($A19="""","""",VLOOKUP($A19,ESTIMATE!$A$9:$B$ 15,2,0))"
ds.Range("c19:h" & dlr).Formula = _
"=IF($a19="""","""",SUMIF(ESTIMATE!$A$9:$A$15,$A19 ,ESTIMATE!K$9:K$15))"
ds.Range("b19:h" & dlr).Value = ds.Range("b19:h" & dlr).Value
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
Thank you Don, I just sent you the information.

Vern

"Don Guillett" wrote:

Could using SUMIF do it?

If desired, send your file to my address below. I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"vern" wrote in message
...
I am loking for a macro that I can put in a command button that will
look
through a work sheet in column "A" for identical numerical values. once
found
take all rows with identical values and combine the rows into one in a
different worksheet. when combined it must add together the different
values
of the rows in columns "f", "g" "h", "i" and "j".

can this be done?

.

.