I have a spreadsheet that I have sorted and contains duplicate cells of
information within the same columns, but has needed information within the
row. I need some help writing a formula that would allow me to do as
discribed below down the entire worksheet:
If Cell B2= B3, Then add value in cell E3 to cell E2, retaining the E2
cell vaue, using sometype of delimiter such as a comma or hard return to
seperate the 2 values, then delete row 3 and move down through the entire
worksheet.
Any help or advice would be appreciated.
Thanks,
Jacky Del Hoyo

Try this against a copy of your worksheet--it will destroy the existing data.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
.Cells(iRow - 1, "E").Value _
= .Cells(iRow - 1, "E").Value _
& vbLf & .Cells(iRow, "E").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

The vblf is the same as the alt-enter (to force a new line within the cell).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jacky D. wrote:

I have a spreadsheet that I have sorted and contains duplicate cells of
information within the same columns, but has needed information within the
row. I need some help writing a formula that would allow me to do as
discribed below down the entire worksheet:
If Cell B2= B3, Then add value in cell E3 to cell E2, retaining the E2
cell vaue, using sometype of delimiter such as a comma or hard return to
seperate the 2 values, then delete row 3 and move down through the entire
worksheet.
Any help or advice would be appreciated.
Thanks,
Jacky Del Hoyo

--

Dave Peterson

Thank you Dave, That worked wonderfully! Can you suggest a site for helping
people to learn how to write macros? The reference you gave was helpful, but
I think I need something a little lower level than that, I have zero
programming experience, but it's always intrigued me.

"Dave Peterson" wrote:

Try this against a copy of your worksheet--it will destroy the existing data.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
.Cells(iRow - 1, "E").Value _
= .Cells(iRow - 1, "E").Value _
& vbLf & .Cells(iRow, "E").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

The vblf is the same as the alt-enter (to force a new line within the cell).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jacky D. wrote:

I have a spreadsheet that I have sorted and contains duplicate cells of
information within the same columns, but has needed information within the
row. I need some help writing a formula that would allow me to do as
discribed below down the entire worksheet:
If Cell B2= B3, Then add value in cell E3 to cell E2, retaining the E2
cell vaue, using sometype of delimiter such as a comma or hard return to
seperate the 2 values, then delete row 3 and move down through the entire
worksheet.
Any help or advice would be appreciated.
Thanks,
Jacky Del Hoyo

--

Dave Peterson

David McRitchie has lots of links at:
http://www.mvps.org/dmcritchie/excel....htm#tutorials
and
http://www.mvps.org/dmcritchie/excel/excel.htm#links

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with. See if you can find them in your
local bookstore and you can choose what one you like best.

===
My favorite resource(s)--the newsgroups and google.

If you just lurk in these newsgroups, you'll see lots of real life problems with
different takes on solutions.

If you spend any time at all working in the newsgroups, you may find it much
easier to connect directly to the MSServers.

Saved from a previous post:

If you have Outlook Express installed, try clicking on these links (or copy and
paste into MSIE).

news://msnews.microsoft.com/microsof...ic.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsof...heet.functions
news://msnews.microsoft.com/microsof...excel.newusers
news://msnews.microsoft.com/microsof...el.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsof....docmanagement
news://msnews.microsoft.com/microsof...word.word97vba
news://msnews.microsoft.com/microsof....word.newusers
news://msnews.microsoft.com/microsof...ord.pagelayout
news://msnews.microsoft.com/microsof...ord.vba.addins
news://msnews.microsoft.com/microsof....vba.beginners
news://msnews.microsoft.com/microsof....customization
news://msnews.microsoft.com/microsof...rd.vba.general
news://msnews.microsoft.com/microsof....vba.userforms
news://msnews.microsoft.com/microsof....word6-7macros

(You can always connect to more later)

Here are some links that explain it better:

Chip Pearson has some notes written by Leonard Meads at:
http://www.cpearson.com/excel/DirectConnect.htm

David McRitchie's notes at:
http://www.mvps.org/dmcritchie/excel/xlnews.htm
http://www.mvps.org/dmcritchie/excel/oe6.htm
http://www.mvps.org/dmcritchie/excel/oe6nws01.htm

Tushar Mehta's notes at:
http://www.tushar-mehta.com/misc_tut...e_ng/index.htm

And if you're looking for old posts:

Or you can use google (maybe a few hours behind) to search for stuff you've
posted (and find the replies, too)

http://groups.google.com/advanced_group_search
http://groups.google.com/advanced_gr...Excel*&num=100

Ron de Bruin has an excel addin that you may like:
http://www.rondebruin.nl/Google.htm

Jacky D. wrote:

Thank you Dave, That worked wonderfully! Can you suggest a site for helping
people to learn how to write macros? The reference you gave was helpful, but
I think I need something a little lower level than that, I have zero
programming experience, but it's always intrigued me.

"Dave Peterson" wrote:

Try this against a copy of your worksheet--it will destroy the existing data.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "B").Value = .Cells(iRow - 1, "B").Value Then
.Cells(iRow - 1, "E").Value _
= .Cells(iRow - 1, "E").Value _
& vbLf & .Cells(iRow, "E").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

The vblf is the same as the alt-enter (to force a new line within the cell).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jacky D. wrote:

I have a spreadsheet that I have sorted and contains duplicate cells of
information within the same columns, but has needed information within the
row. I need some help writing a formula that would allow me to do as
discribed below down the entire worksheet:
If Cell B2= B3, Then add value in cell E3 to cell E2, retaining the E2
cell vaue, using sometype of delimiter such as a comma or hard return to
seperate the 2 values, then delete row 3 and move down through the entire
worksheet.
Any help or advice would be appreciated.
Thanks,
Jacky Del Hoyo

--

Dave Peterson


--

Dave Peterson