Duplicates and subtotals

Each week we receive a file from our payroll system, 
approx 6000 rows. Contains General Ledger data by 
employee and GL account with amounts. We use this file to 
post to our GL system.  I'd like to find a way in Excel 
to subtotal by row containing unique data.  

Emp   Co   Accnt  date       amount
1234  10   4000  10/17/03     100.00
1234  10   4000  10/17/03       5.00
  I would like to see, instead of two lines of detail, 
1234  10   4000   10/17/03     105.00

Anyone had the opportunity to do this?
Thanks in advance
Deanna
0
12/17/2003 5:21:13 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
129 Views

Similar Articles

[PageSpeed] 16

I'd insert a new column A and use a helper formula:
(assuming headers in row 1 and date in A:E)

=a2&"-"&b2&"-"&c2&"-"&text(d2,"yyyy/mm/dd")

then sort by this column and do Data|Subtotals.
You can use the outlining symbols at the left to hide the details.

==
An alternative would be to use Data|Pivottable.

You could drag the emp, co, accnt, and date to the row field and drag amount to
the data field.

After you see the pivottable, double click on each of the row fields and select
None for subtotals.

Deanna wrote:
> 
> Each week we receive a file from our payroll system,
> approx 6000 rows. Contains General Ledger data by
> employee and GL account with amounts. We use this file to
> post to our GL system.  I'd like to find a way in Excel
> to subtotal by row containing unique data.
> 
> Emp   Co   Accnt  date       amount
> 1234  10   4000  10/17/03     100.00
> 1234  10   4000  10/17/03       5.00
>   I would like to see, instead of two lines of detail,
> 1234  10   4000   10/17/03     105.00
> 
> Anyone had the opportunity to do this?
> Thanks in advance
> Deanna

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/17/2003 5:37:19 PM
Reply:

Similar Artilces:

Outbox duplicates
I don't recall exactly when Outlook Express took priority default over Outlook but I don't want it as default and the standard information given by Microsoft does not give correct instructions on how to remove Outlook Express and put Outlook in primary default. Can any one help me on this? Also there seems to have been a random duplication of outgoing mail put into Outlook from Express. Outlook Outbox has "unsent messages" from weeks ago that I thought were sent from Express. Now I don't if all of the mail I thought I sent actually was sent. If mail was sent fr...

Removing duplicates
I have two email lists - one is a subset of the other. I would like to remove all of the email addresses from the longer list that appear in the shorter list, leaving only the unique email addresses. I've tried using the "Remove Duplicates" function, but it keeps telling me there are no duplicates. I'm not sure what to try next. One way is to paste the longer list below the shorter list, select the column having the combined list and then 'remove duplicates'.. the entries left below the short list will contain the longer list minus those which are on t...

remove duplicates
hi all i have in raw A phone list in raw B we have customer that ask to be removed from call list what can i do to have clean list in raw C If you delete those records, they are gone forever. Maybe you just want to hide them. Put the word 'Hide' in Column C, in the rows of clients that are not supposed to be called, and run this: Sub HideRows() Dim b As Boolean Set r = ActiveSheet.UsedRange nLastRow = r.Rows.Count + r.Row - 1 For rr = 1 To nLastRow b = False For cl = 3 To Columns.Count If Cells(rr, cl).Text = "Hide" Then b = True End If Next If b...

identify duplicate data in excel spreadsheet
Please help me to identify duplicate data from excel spreadsheet. I have to enter series of five digit numbers in spreadsheet as per work completion.....and some time it so happens that the same number is entered twice......I want that the excel should prompt that the number is already eneterd. For example: 35851 35933 36009 35851 (out here this number is duplicate as the same is already entered above and I want excel should prompt duplicate here) Assuming that the 2nd entry in your list is at A3, put this formula in B3 and copy down to suit. =IF(ISNA(MATCH($A3,$A$2:$A2,0)),""...

Records are duplicated in 1.2
Some contacts records are duplicated in the contacts records view, however when you clic in the duplicated record the record opened is different. ...

Master Number Duplication
Has anyone else ever had the problem with the Master Number being duplicated? We are on GP 9 and this had happened with earlier versions and then didn't seem to be an issue, and now it is happening again. We'll enter an order, and the master number might be 74,802 and then the next order entered might get a master number of 74,749. This is most puzzling to us and does appear to be causing some problems. (Like when you look up an order to find the trail and find two ORDERS connected that are not related). thank you. I have had this problem before. However, I wrote the follow...

Duplicate Dump from email server for Outlook 2007
This is becoming very frustrating. For about the fourth time in six months, my server downloads all it emails to Outlook 2007, even though it had previously downloaded them. Has this been solved? Did you move or restore your PST? Run scanpst or do anything to your email account? This can cause outlook to reset the counter it uses to keep track of what was downloaded. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lis...

Error in Subtotal Wizard (Grouping and Outlining)
I'm having trouble with Excel 2003 and the Subtotals Wizard. When I perform a three-level grouping on the data listed below, it appears that some groups are missing and outline levels 3, 4, and 5 are messed up. Given the following list of data: Region columnb Office Employee columne Issue NW SEA 123 Salary NW PDX 234 Benefits NW PDX 244 Salary NW PDX 212 Salary NW BOI 232 Benefits SW LAX 673 Salary SW LAX 34 Benefits SW LAX 654 Benefits SW PHX 556 Salary SW DEN 322 Benefits SW DEN 65 Salary ...

Prevent duplicate groups of records in a table
Access 2007 I have a formulas table with the following fields: Auto FormulaID Ingredient Percent 267 127 FlourA 51 268 127 FlourB 46 269 127 ProteinA 2 270 127 FlavorA 1 271 128 FlourA 51 272 128 FlourB 49 273 129 FlourA 25 274 129 ProteinA 25 275 129 FlourB 47 276 129 FlavorA 3 ...

Avoid duplicate records
Is there any way to avoid creation of duplicate records (account, contact, lead) in Microsoft CRM 1.2? Thanks Daniele, There is no built-in duplicate checking in the application. Typically, this is more of a procedural item. However, there are things you can do to add some of this functionality, but it would involve custom development. There is a basic example on MSDN that shows some basic functionality in this area, but it is far from a complete working solution. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Thu, ...

Duplicates being produced
My report produces too many pages.! And the underlying query does not produce duplicates. The report is executed by a button on a form. The code behind the button specifies Criteria, date ranges, employee and the report to open. Only two pages should print (1&2), but it returns 1-20. pages 1&2 keep getting duplicated (2&3, 4&5, 6&7....) are dups of 1&2... My report has a Report Header, Page Header. Detail, Page Footer, Report Footer. Here is the code behind the button to launches the report. Something is producing duplicates when one reviewer is choosen???...

Re-Help finding duplicates
Someone here a few years ago posted the formula/process to find duplicate data so it could be sorted then deleted. Here is my situation: Report a b c d e 1 yellow 5 b 7 2 yellow 6 c 9 3 yellow 5 d 9 4 blue 1 w 8 5 blue 5 m 8 6 yellow 1 m 9 The process that was posted was to add a formula in column e to enter an "X" any time data in column a was a duplicate. So the result would have placed an "X in e2, e3, e5, and e6 - looked like...

Formatting Subtotals #2
When using Data, Subtotals to create subtotals in a detailed spreadsheet, how do I set the format so that all subtotal lines are bolded and are followed by a specified number of blank rows? One option - Collapse the sheet to show only the subtotals, then select visible cells only (F5>Special>Visible Cells only) Then Bold those cells and, instead of adding empty rows, make the subtotal rows taller and format them with the vertical alignment at the top "slh" wrote: > When using Data, Subtotals to create subtotals in a detailed spreadsheet, > how do I set the f...

Access
With a database stored on a network drive, Access 2007 creates a copy each time I close it, and names it database, database1, database2, etc. This does not happen when I have the database stored on my hard drive. How can I stop this from happening? On Wed, 27 Jun 2007 08:34:02 -0700, mfdou <mfdou@discussions.microsoft.com> wrote: >With a database stored on a network drive, Access 2007 creates a copy each >time I close it, and names it database, database1, database2, etc. This does >not happen when I have the database stored on my hard drive. How can I stop >this fro...

duplicate entries #3
In my 'report' of spending by category, I've got the credit card entries and then the credit card payment which are essentially both and does not give me an accurate report. Any suggestions? Michelle In microsoft.public.money, Michelle Bailey wrote: >In my 'report' of spending by category, I've got the credit card entries and >then the credit card payment which are essentially both and does not give me >an accurate report. Any suggestions? Switch to advanced register, and change one of each payment pair to the SPECIAL category Transfer:otherAccount....

Great Plains Duplicate
I am a IT Auditor and we are getting ready to do a great plains duplicate audit. I there any tools or documents out there that would help us get started. At my previous job we used ACL (Audit Command Language) to connect to and Audit Great Plains data. "moyer" wrote: > I am a IT Auditor and we are getting ready to do a great plains duplicate > audit. I there any tools or documents out there that would help us get > started. ...

Duplication still "being published"
Hi, I've upgraded CRM to 4.0; the 3 default duplicate detection rules for account, contact and lead have the status "Being published"; this is so for 3 days now. I cannot use the rules because CRM tells me that there are no rules published. What can do about this? I've created a new rule and published it but it has the same status and is not being changed in "Published". Regards, -- if it all works without errors then where is the challange? Hi Philippe Please check whether MS CRM Asynchronous service is working properly or not. "Philippe" wrot...

eConnect duplicate key message
Hi, I am running eConnect 8.0 on Great Plains 8.0. I have an integration to a warehouse application and the integration creates inventory receipts. Occasionally I get a strange error - "Violation of PRIMARY KEY onstraint 'PK_eConnect_Out_temp'. Cannot insert duplicate key in object 'eConnectOutTemp'. The statement has been terminated". I can enter manually the receipt in Great Plains with no error and the receipt does not appear in any of the POP tables. Does anyone know what the problem with eConnect is here? Thank you for your help. Peter PeterH: Sou...

Outlook2002
Everytime I log on to download my emails it downloads all emails still on the server (they are generally left on for 5 days) not just the ones that have previously not been downloaded. I have tried repairing and reinstalling without success. Is there a fix for this ? ...

Duplicating a workbook only with cell's values
Hello every body, I have a complex workbook. It it is becaming a little bit difficul to work into it. It is possible to do a copy of this workbook but thi copy should have only cell�s values not cell�s formulas. As I said thi workbook is complex to replecate sheet by sheet to another workbook an do the paste especial process. thank you in advanc -- Message posted from http://www.ExcelForum.com Hi Adrix There is no option to save it like this Try this macro on a copy of your workbook Sub test() Worksheets.Select Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Worksheets(1).S...

duplicate Admin mailboxes
had to restore several users mailboxes, while that was done, I was going to attempt to restore the public folders when I noticed that I have 2 administrator mailboxes when I go into outlook. I have been in ESM, ran cleanup agent, did not find any duplicate accounts. do not have 2 accounts. this is one win2003 DC / exchange 2003. any ideas ? If you only have one account in ESM and 2 in Outlook, I'd say that's a profile problem. Bin the Outlook profile and recreate. <fxkeough@hotmail.com> wrote in message news:1141886201.798471.57420@i40g2000cwc.googlegroups.com... > h...

Duplicating PivotChart / Changing PivotChart data range
I have a worksheet with a pivottable and an associated pivotchart, all nicely formatted. I want to make a duplicate of this so I can configure the data selection differently, but still retain the formatting. In Excel 2003, I just duplicated the worksheet and the new pivotchart would be associated with the new pivottable. In Excel 2007, I find the new pivotchart is *still* tied back to the original pivottable. Is there a way around this? If I select the PivotChart Tools, I see 'Select Data', but the section for "Chart data range" is grayed out and will not allow edits. I t...

Comparing columns for duplicates/differences
I have a spreadsheet that contains 2 columns of information containing numbers. It was created by the query function from my AS400, saved in an excel spreadsheet, sent to a 3rd party for verification and then returned in a comma delimited format with the updated information (the first column being the info I provided, the second being the info that was returned). I then saved the worksheet in an excel format before running the test. I've tried comparing the information for discrepancies using the 'Worksheet Formula' method without success. I have also determined that i...

Duplicates #4
I have a column of Numbers in column B. I need to a function or something that will make those numbers that are duplicates stand out, like highlight them in Red or put a mark next to them in the next column (c). I need to be able to see which ones are duplciates easily instead of sorting them and picking them out manually. I also need the originals highlighted too. If there are two number six's, I need both of them highlighted or marked. Thanks -Mat Chip Pearson has a bunch of techniques for working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Matt in AZ wrote: >...

Duplicate an existing smartlist
Bonjour! Is it possible to copy a system smartlist or base a smartlist from another one already existing from GP. Joel It depends! Are you talking about regular SmartLists or SmartLists built with SmartList Builder? -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Joel" wrote: > Bonjour! > Is it possible to copy a system smartlist or base a smartlist from another > one already existing from GP. > > Joel Regular ones, because a lot of times, the smartlist is close to satisfy my client but their is a l...