Duplicate Data -Adding information to another column in row & dele

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
0
JackyD (5)
7/26/2005 3:55:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
559 Views

Similar Articles

[PageSpeed] 45

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
0
petersod (12005)
7/26/2005 5:09:15 PM
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
> 
0
JackyD (5)
7/26/2005 8:11:04 PM
David McRitchie has lots of links at:
http://www.mvps.org/dmcritchie/excel/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/microsoft.public.excel.setup
news://msnews.microsoft.com/microsoft.public.excel.misc
news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions
news://msnews.microsoft.com/microsoft.public.excel.newusers
news://msnews.microsoft.com/microsoft.public.excel.programming

(and a few more for MSWord)
news://msnews.microsoft.com/microsoft.public.word.docmanagement
news://msnews.microsoft.com/microsoft.public.word.word97vba
news://msnews.microsoft.com/microsoft.public.word.newusers
news://msnews.microsoft.com/microsoft.public.word.pagelayout
news://msnews.microsoft.com/microsoft.public.word.vba.addins
news://msnews.microsoft.com/microsoft.public.word.vba.beginners
news://msnews.microsoft.com/microsoft.public.word.vba.customization
news://msnews.microsoft.com/microsoft.public.word.vba.general
news://msnews.microsoft.com/microsoft.public.word.vba.userforms
news://msnews.microsoft.com/microsoft.public.word.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_tutorials/oe_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_group_search?q=group:*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
0
petersod (12005)
7/27/2005 12:00:03 AM
Reply:

Similar Artilces:

migration shut down the information store 5.5
Hi all I have two 5.5 and two 2003 sp1 exchange server all in the same admin group and site. on one of the 5.5 server, i have all the mailboxes moved over to one of my 2003 servers. I would like to turn off the information store on the 5.5 server that has all the production mailboxes moved over to the 2003 server, this way if anyone complains that they cant get into email, then i know that the mailbox needs to get moved. My questions are, by tunrning off the information store service on the 5.5 server, will this negatively effect the ADC replication? I have removed the IMC off of this...

Excel, how do I change the column headings from letters to number
I have a spreadsheet that has numbered columns as opposed to the standard letters. How can I change this back to letters? Go to the Tools menu, choose Options, then the General tab. There, uncheck the R1C1 reference style setting. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "lazybee" <lazybee@discussions.microsoft.com> wrote in message news:030962A3-A111-4780-93C0-1D28003F1F20@microsoft.com... >I have a spreadsheet that has numbered columns as opposed to the >standard > letters. How can I change this ...

Public information store
Exchange 2K-Under the Properties for a new public folder I have created, the Directory Rights tab is grayed out. Any input would be appreciated -- Joe M On Tue, 12 Jul 2005 12:01:03 -0700, "Joe M" <JoeM@discussions.microsoft.com> wrote: >Exchange 2K-Under the Properties for a new public folder I have created, the >Directory Rights tab is grayed out. > >Any input would be appreciated Its grayed out until you mail-enable it. ...

Hiding empty rows and columns
Does anyone know the code for hiding all blank rows and columns in a worksheet. Thanks -- Message posted from http://www.ExcelForum.com Hi try the following (adapted from: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows) Public Sub HideBlankRows() Dim R As Long Dim C As Range Dim Rng As Range On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = ActiveSheet.UsedRange.Rows End If For R = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFuncti...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

Excel moving horizontal columns to vertical
I am trying to find a simple way of moving data in horizontal columns (referencing the key in column 1) to vertical colums (still referenceing the same key). This would mean automatically repeating the values in column one for every entry in the moved columns. I can do this through the pivot table, but this is a long drawn out process. Any ideas I'm not sure if this will work for you, but you can try this: - Highlight & copy on the horizontal value you want to make vertical - Now highlight the vertical area you want these value to got - Right Click when highlighted & choose...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

Yet another duplicate record dilemma
I have a table with records where one field are duplicates. I'm able to query to find duplicates and delete them, however what I need to do is find the duplicates, produce a total from another field, delete the duplicates and update the record field with the new total. Use the Find duplicates wizard, the build an Update query and either add to the field: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = MyField + Query1.MyField or just update it: Update MyTable Inner Join Querty1.ID On MyTable.ID Set MyField = Query1.MyField Then delete the duplicate data. -- Ar...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Accessing my information offline
I have updated my Outlook to 2002 on my notebook computer and when it is connected on the Lan at my office(new ethernet connection) I can see my calendar, addressbook, e- mails etc. However, when I disconnect and bring my notebook home, and try to access calendar, etc, it says I must sychronize while I am still connected to the Lan. My previous version of outlook didn't have this problem. I synched it on the Lan and it worked. This one won't. Any ideas???? How do you have your folders marked for Offline use under tools->Send/Receive settings? --� Milly Staples [MVP - Outlo...

Information Store Corrupted
Guys,, I think I'm in very deep trouble. I have two Exchange 5.5, one for outgoing mail and one for incoming, the incoming mail sever is a W2K and is the Domain Controller. My DNS server is a separated dedicated server. I had a big power failure last thursday night. The three server sudently shut off and restart again after the AC came back. On friday morning I realized we didn't have email service, I checked the outgoing server and it was OK, but on the incoming server the Information Store Service was not starting, I went to the web and found this article about how to reco...

Fill Down Excluding First Row
Greetings, I have a spreadsheet where the first row (1) consists of labels that describe the column located below each label. For example, I have calories, fat, carbs, etc. It is desirable to have some of these columns filled in automatically. For example one row might be "calories from fat," "calories from carbs," etc, and so I'm doing a "fill down" on those columns. Unfortunately, the fill downs are completing the columns with the labels. So I'm wondering if it's possible to: (a) Exclude the label row (row 1) from the fill down or, better yet;...

Find and mark duplicates
If I have two columns with data, how do I compare these two columns to find all duplicates and put an X on a third colum next to all duplicates? Thanks. =if(a1=b1,"X","") "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com... > If I have two columns with data, how do I compare these two columns to > find > all duplicates and put an X on a third colum next to all duplicates? > Thanks. The problem is, I don't know which two cells to compare, I need to know if any o...

Is there a way to sort a column without invoking a macro?
I am trying to get a feel for some probabilistic properties. For example, I am trying to look at the statistics of the intervals between randomly occurring pulses. I have no problem getting a column of uniformly distributed pulse times, but they are unsorted, Every time I recalculate with cmd-=, I get a new unsorted set of pulse times because of the use of the RAND function. Is there a way of taking this unsorted column and pasting it into another colun that would be sorted without invoking a macro? To simplify and be more specific consider the following. 1. In A1:A100 I have a set...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Duplicate Public folders
I am struggling to get all my public folders replicated on both of my servers. The Status seems to be either "local Modified" or "both modified" I then discovered that MOST of my public folders viewed when I clicked on "View System Folders" did not have a listed replica on the other server. So I added a replica for each System Folder. Now I seem to have duplicates of some of these folder listed in the Public folders list of one of my servers. Can someone tell me where I can find a lits of folders that _should_ be replicated and which should not? Many tha...