Merge Rows of like data

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 


0
9/6/2007 6:33:30 PM
excel 39879 articles. 2 followers. Follow

1 Replies
581 Views

Similar Articles

[PageSpeed] 20

On Sep 7, 4:33 am, "John Wright" <riley_wrig...@hotmail.com> 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

Hi John,

Try this out on a copy of your worksheet...

Option Explicit
Option Base 1
Public Sub MergeRows()
Dim TallyHeadings As Range
Set TallyHeadings = Application.InputBox( _
prompt:= _
"Select the Headings of the Columns with Values to be Added.", _
Title:= _
"Columns to be Added for Duplicate Rows", _
Default:= _
Selection.Address, _
Type:= _
8)
Dim lnHeadingDepth As Long
lnHeadingDepth = TallyHeadings.Rows.Count
Dim lnHeadingTopRow As Long
lnHeadingTopRow = TallyHeadings.Cells(1).Row
Dim TallyColumns() As Long
Dim TallyHeadingCell As Range
Dim lnTallyCol As Long
Dim T As Long
For Each TallyHeadingCell In TallyHeadings.Rows(1).Cells
lnTallyCol = lnTallyCol + 1
ReDim Preserve TallyColumns(lnTallyCol)
TallyColumns(lnTallyCol) = _
TallyHeadings.Cells(lnTallyCol).Column
Next
Dim lnLastCol As Long
Dim lnLastRow As Long
Dim I As Long, J As Long
lnLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
lnLastRow = Cells(Rows.Count, 1).End(xlUp).Row
For I = lnHeadingDepth + lnHeadingTopRow To lnLastRow
For J = lnLastRow To I + 1 Step -1
If Not RowsAreNotDuplicates( _
    TopRange:=Union(Range(Cells(I, 1), _
                          Cells(I, TallyColumns(1) - 1)), _
                          Cells(J, TallyColumns(2) + 1)), _
    BottomRange:=Union(Range(Cells(J, 1), _
                             Cells(J, TallyColumns(1) - 1)), _
                             Cells(J, TallyColumns(2) + 1))) Then
For T = 1 To UBound(TallyColumns)
Cells(I, TallyColumns(T)) = Cells(I, TallyColumns(T)) + _
Cells(J, TallyColumns(T))
Next T
Cells(J, 1).EntireRow.Delete
lnLastRow = lnLastRow - 1
End If
Next J
Next I
End Sub

Public Function RowsAreNotDuplicates(TopRange As Range, _
BottomRange As Range) As Boolean
Dim Cell1 As Range, Cell2 As Range
Dim K As Long, M As Long
For Each Cell1 In TopRange
K = K + 1
For Each Cell2 In BottomRange
M = M + 1
If K = M Then
If Cell1 <> Cell2 Then
RowsAreNotDuplicates = True: Exit For
End If
End If
Next Cell2
M = 0
If RowsAreNotDuplicates Then Exit For
Next Cell1
End Function


I couldn't clearly determine the columns with the numbers to be added
so when the code is run an inputbox pops up asking the user to select
the cells containing the headings of the columns with the values that
you want added for duplicate rows.
The code assumes that these columns are contiguous.

Ken Johnson

0
KenCJohnson (314)
9/7/2007 2:33:09 AM
Reply:

Similar Artilces:

How to save and restore all data
Hi ! I'm using MS Access 2003 and Windows XP On the one hand, there are regular users of our db. They only change the data in the tables. They don't change forms, queries, reports or other things. Once in a while I have to make a change to a form, a query or other non-table-data things. But in the meantime, the regular users may keep changing data in the tables. I would like to get the latest data into my new db version. Is there a way of copying just the tables to the new db version ? Is there a way of copying just the table data to the new db version ? Any helpful suggestions o...

VBA/Row number/column numer
If I want to specify a cell in VBA but only know it's row NUMBer and not column number, how would I describe this cell in vba? i.e. instead of AA5, i want to be able to talkb about it as being column 27 and row 5... thanks! Mika, Cells(Row, Column) Cells(2, 3) would give you cell C2 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "mika." <conceptualness@hotmail.com> wrote in message news:07ad01c37be7$ffd1b0a0$a101280a@phx.gbl... > If I want to specify a cell in VBA but only know it's row > NUMBer a...

Joining data points on a line chart
I have the following as an example set of data points to be plotted on either a line or scatter chart (doesn't matter which as I am having the same problem with both!) X Y1 Y2 1 2 1.8 2 4 3 6 4.5 4 8 5 10 9 6 12 I get a line drawn between the points for Y1, but because there is not a Y2 value for every point on the X-axis then I just get points and no line, despite a line being selected for in the options! Does anyone know how to get this line added on? Thanks In each empty cell enter =NA(); this will display as #N/A. The line on chart will be continuous. OR Click chart; use T...

Monthly Summary of Data
I am having Daily Sales Figures for the entire year being (Invoice No., Date, Name of Seller, Net Sales Amount, Tax, Gross Sales Amount). Now if i want a Monthly summary from the entire data of say ( Net Sales, Tax, Gross Sales) how do i get it in excel. Hi I would use a Pivot Table to summarise the data. Take a look at Debra Dalgliesh's site for some guidance on how to sue Pivot Tables http://www.contextures.com/tiptech.html -- Regards Roger Govier "proshail" <proshail@discussions.microsoft.com> wrote in message news:C51FE172-7891-468A-A751-E63BEB19CD3C@microsof...

mail merge problem #6
While running my newsletter with Mail Merge, I received the following message: ERROR: limitcheck OFFENDING COMMAND: image OPERAND STACK: OutputProfile r resource/OutputProfile/sharp.icm sharp.icm --no string value-- HELP! I think this is a postscript error, from the way it sounds it could be your images are simply too large. http://www.prepressure.com/ps/dbase/limitcheck.htm This from http://dspace.dial.pipex.com/quite/errors.htm#index limitcheck The printer has reached an internal limit. Some limits are fixed, some depend on memory. In level 2, there are fewer fixed limits. Most common...

Help with importing data
Hi All, I'm currently using Access 2002 Sp3 and I'm having troubles with importing data from a text file. The text file is 750Mb in size. Are there any handy tips or tricks that I ould using tryiong to import this data. Whenever I try to import data Access stops responding. Rgds, Dolphy With a file that size you have to do a line by line import ie write your own routine Pieter "Dolphy" <Kyrpto@gmail.com> wrote in message news:1190847728.817529.138530@g4g2000hsf.googlegroups.com... > Hi All, > > I'm currently using Access 2002 Sp3 and I'm having...

Extact GPS Data from JPG image #2
Hi All I want to extract Exif / GPS Information from the JPG Image . For that I am using Gode Gurus tinter. http://codeguru.earthweb.com/cpp/g-m/gdi/gdi/article.php/c3655/ In this all the tags for GPS are available . My problem is the when i see any image in it do not shows me GPS tags . // ************************************************************************* I want to know how can i extract GPS information . and also want to know can GDI+ is used to extract GPS information . // ************************************************************************* In code their is one line . m_...

SQL Error Message- Row Cannot Be Located...
Hi, we're getting the following error message on a customers machine when she tries to open up Store POS for the first time- "Row cannot be located for updating- some values may have changed since last read". This is a new RMS Store Operations 2.0 installation and it's connecting to HQ. After this particular store was first created, there was an error synchronizing the databases, a collation problem which was immediately fixed and everything synched up fine. Disconnected and reconnected the database, Store Manager opens fine, deleted and recreated the register and ...

Error Event ID 9175 to merge mailbox from recover
Hi: I had change the old server and now we are 5 days off why Exchange in new server not want work. I had a backup of old server with ntbackup and i make an Mailbox store for recovery with Exchange 2003 SP1. I restore the backup ok in Mailbox store for recovery but when I try merge a mailbox to the "normal" Mailbox store I receive a error with event id 9175. It say: Event Type: Error Event Source: MSExchangeSA Event Category: MAPI Session Event ID: 9175 Description: The MAPI call 'OpenMsgStore' failed with the following error: The attempt to log on to the Microsoft Exchan...

Merging workbooks that weren't "shared workbooks"
I've got a problem in that one person in the organization, working independently, has been making data changes in an older version of the workbook (note that this was NOT a shared workbook). He happens to be the boss, so that his changes must be adopted. I have been asked to merge his data changes into the most recent version of the workbook. I fear that Excel's merge function would not work here and that I'll have to resort to the good ole' cut 'n paste technique. Any other suggestions? Hi you may have a look at http://www.cpearson.com/excel/whatsnew.htm look f...

when certain rows are hidden, can I also hide a combobox?
Hello, I have a code that hides certain rows in sheets 3 and 4. Is there a way to make 'Drop Down 3' and 'Drop Down 4' hidden when rows as per Case 2 are hidden? The code I have so far is below: Sub ComboBox1_Change() ' Combobox Value Is changed varData = Range("B27").Value2 ' unhide ranges On Error GoTo 100 Application.ScreenUpdating = False Sheet3.Range("A58:A68").EntireRow.Hidden = False Sheet4.Range("A150:A185").EntireRow.Hidden = False Sheet3.Range("A35:A57, A26").EntireRow.Hidd...

How do I save data on a form to my database?
I am new to Access and was wondering how I would save a record from my forum to the database. I am using an unbound forum. Thanks adamskiii, Why have you created an "unbound" form to operate on a table? If you're new to Access, you've just made your project much more difficult than it needs to be. You'll need to create a button on your form that, when clicked, runs an Append query against your table, using the current values on the open form. -- hth Al Campagna Microsoft Access MVP 2007-2009 http://home.comcast.net/~cccso...

Sharing PST or Outlook Data Files
After posting a note saying how I exported the Outlook data from the desktop PC to a back up PST file then importing that into Outlook on my laptop, people posted a link to a program called "OutlookSync". I downloaded it and installed it on both computers. I then ran it on both computers to initialise the programs. I then ran "Sync" on the PC. It created a database file which I then copied to the directory where the sync prog is installed on the laptop. I ran it and "Sync'ed" it. It seems to be working. I presume that when I work on the laptop that if I ma...

Form does not show recordset's data
Hi everyone and thanks for reading I am trying to set an access form's recordset on an ADODB.Recordset that was fabricated from scratch. This means that I have appended the fields I want and then I added some records. Finally, I am setting the form's recordset property on this recordset but I get #Error in the form's controls. Offcourse, I confirm that the controlsource of each one of the controls matches the corresponding field values of the recordset. (If I set the form's recordset property on a recordset made from an existing source (table or query) the form works f...

Microsoft Dynamics 4.0 Data Migration Manager Sign in Issue
Hi, OK I'm a Syatem Administrator on our companies CRM system. I have installed DMM as recommended on my PC and installed with SQL Express. The problem I have is that the DMM has installed correctly and when I try to sign in it comes up with the error of 'You must have System Administrator security role to use this tool.' I have!!. My colleague who is also a system administrator has installed successfully and has been able to sign in use the DMM. We are both on the same version of Windows (Windows 7 Enterprise) and exactly the same role permissions on the CRM. Has anyone see...

Bulk import of data in HR module
We have the HR module integrated with Payroll but are grossly underutilizing it. One goal is to be able to import a summary .csv file from our health insurance provider in order to populate the HR tables with health ins choices, COBRA info, etc. Integration Manager doesn't seem to see the HR module as a valid destination. Any way to get this (and other) data into Great Plains? Thanks! Doug There are two ways: a. eConnect. This is a programmers tool, but does makes things quite easy as it handles all the business logic. A record gets imported or rejected b. Direct table import. ...

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

How can I tranfer my data to my.swf in MFC program.?
I have my.swf and my.exe (MFC). I want to send to my.swf an data of my.exe. But I don't know it. Please tell me some idea.. (I want just simple flash/MFC code.. just...) On Mar 15, 7:07 am, "keandi" <kea...@lycos.co.kr> wrote: > I have my.swf and my.exe (MFC). > > I want to send to my.swf an data of my.exe. > But I don't know it. > > Please tell me some idea.. > > (I want just simple flash/MFC code.. just...) Post this in a .swf ng instead. See if ShockWave Flash object has an object model that you can use to do this. --- Ajay This mig...

The footnote look like [1]
The footnote look like [1], How to make it? (sr, my english ... bad!) The general principles are described in this article: http://word.mvps.org/faqs/macrosvba/UnSuperscptFnotes.htm. -- Stefan Blom Microsoft Word MVP "tuaans," <tuaans,@discussions.microsoft.com> wrote in message news:60F4FFBB-D1FB-4E6B-99A2-67CF28F4A28C@microsoft.com... > The footnote look like [1], > How to make it? > (sr, my english ... bad!) ...

Pull data (sumif?) from an external workbook
I have a workbook "report". Col A has contains products. The number of products in column A may vary but each product features only once. eg: TOTAL SALES FOR JANUARY ColA ColB ItemX 4 ItemY 5 ItemZ 4 Another workbook "sales" contains the individual sales of these items in one month. So Col A contains the products which may feature once or several times. Col B contains the Qty. eg: SALES FOR JANUARY ColA ColB ItemX 1 ItemX 3 ItemY 5 ItemZ 2 ItemZ 2 Both workbooks reside...

like a Pivot Table
Hello every body If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is existname age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I want John age 20 John Joining Date Jun-90 John Tele 4321251 Iqbal age 30 Iqbal Joining Date Jul-95 Iqbal Tele 6583752 George age 40 George Joining Date Sep-85 George Tele 7843125...

vb code to copy and list selected row to diff sheets
I am working with workbook with 30sheets for each day x each worksheet having same headers and colmuns x I want to copy selected rows to corresponding sheets with different name . eg. date name details amount type 13feb kk ccj/dxb 1300 inv 14feb pv ccj/auh 1200 alh I would like to copy first row to a sheet named inv and second row to sheet names alh. can u adv me the code to be used -- pvkkutty new to discussion group however a freequent reader of discussion group posts Try the below code with all the sheets... Sub CopyRowstoDifferentSheets() Dim wb As Workbook...

Publisher-How do I change calendars 04 to 05 without losing data?
I have a current document 12 calendars with data in each day. I am trying to change the calendars from 04 to 05 with losing the existing data. Is there a way the data can stay in the same spot and the dates change. I also want to keep the same layout I currently have. I have tried using calendar options but cannot get it to work. Any help would be appreciated. Manually... You can try this, Expand your existing calendar by 12 months. On the toolbar, edit, office clipboard, this will give you 24 copied items you can collect. Copy your data, go to the next year, place your cursor where you...

No AFX_DATA data
Hello, I am new to vc and am trying to figure out how to add property pages to a (dragged in) tab control. Since there are no Tab Control code examples I am trying to extract the related sections of the "FIRE" sample. In my newly created scratch project, I do not have a "AFX_DATA" section in my dlg.h file. Apparently AFX_DATA is a list of instance members of items I drag in. Yes? As a consequence, I am unable to InsertItem on my CTabCtrl object because I don't know the name of the CTabCtrl object. How do I get AFX_DATA turned on, or, how do I find out the name of...

Line Graph Data Recognition
I have an array (about 15 x 15) with years 1990-2003 along the top that is missing data in some years, i.e. one series begins at 1990 and goes to 2000 while other series begin in 1998 and goes to 2003. The array content is in number format to the first decimal place, e.g. 14.1. These values were originally created by a formula and were in ratio format, e.g. 0.141, but I converted them by multiplying them by 100 and then removed the fomula by the "copying and paste value" command. When line graphing the array using the ratio values, the image turns out as anticipated, i.e. th...