collapsing rows in Excel

Hello:

I exported a spreadsheet from a SmartList that I created in SmartList 
Builder.  The SmartList is similar to the defaul Financial Transaction object 
in SmartList that shows open year journal entries from subledgers such as 
payables and receivables.

The spreadsheet contains the column called "Series".  The rows in this 
column are of course designated by such phrases as "Sales", "Purchasing", and 
"Financial".  

For "Sales" rows, I want to have Excel automatically remove data from three 
columns and "collapse" the data in the other columns.  When I say 
"collapse", I mean eliminate the duplicate data in each row so that only one 
row contains the data.  

In effect, "Sales" rows will be summarized while "Purchasing" and 
"Financial" rows will contain the detail.

This is hard to explain and take in, I know.  But, if anyone has any ideas 
on what sort of macro can be created to summarize data for just certain rows 
within a spreadsheet, I would love to hear them.

Thanks!

childofthe1980s

0
9/10/2008 3:56:06 PM
greatplains 29623 articles. 6 followers. Follow

0 Replies
791 Views

Similar Articles

[PageSpeed] 45

Reply:

Similar Artilces:

Move C5 to C10 in Excell (no cut/ copy/ paste)?
How do I move C5 to C10 (without using the cut/copy/paste commands)? You can "drag" it with the mouse. Put the cursor on the border of the cell, so that the cursor changes to a cross. Press and hold the right mouse button, drag to the other location and release the button. -- Kind Regards, Niek Otten Microsoft MVP - Excel "kandieclaire" <kandieclaire@discussions.microsoft.com> wrote in message news:7084DD23-28F1-4B51-B4F8-3B44D05B929B@microsoft.com... > How do I move C5 to C10 (without using the cut/copy/paste commands)? ...

Polar graph in excel
Hello there. I strongly believe that MS Office 2007 should be able to graph polar. Excel is great at everything, except when it comes to graphing polar. The closest thing i can get to a polar graph is via the radar graph. I think that by having the ability to make polar graphs, it would help me and millions of other students out there ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link ...

can you date time stamp entries in excel
is it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Private Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, "dd mmm yyy hh:mm") End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate ...

I have a 8MB Excel file that is sooo slow to open and save. Any .
There is one column that has about 15 rows that are added to the next corresponding 15, that are added to the next corresponding 15, etc. 9 times. This is the only unusual thing about this spreadsheet. Hi see: http://www.contextures.com/xlfaqApp.html#Unused -- Regards Frank Kabel Frankfurt, Germany "Kim" <Kim@discussions.microsoft.com> schrieb im Newsbeitrag news:46745637-8CE6-4C17-B6B2-6F191F104619@microsoft.com... > There is one column that has about 15 rows that are added to the next > corresponding 15, that are added to the next corresponding 15, etc. 9 times. &...

Excel word-wrap format
I have encountered this problem with both single and merged cells: With a lot of text in one cell (over 100 words) the word wrap format works for the majority of the text but the last portion of the text does not wrap -- the text continues off to the right and cannot be seen in regular view, print preview, or on the hard copy print out. All text is visible in the edit field. To get all of the text to appear on a printout I must put in hard returns (Alt-Enter) throughout the last portion of the text, experimenting with line brakes to get all of the text to appear. Has anyone else ...

Search and Replace, or Add New Row
I am using Excel 2003. I mistakenly posted this originally under General Questions. Worksheet1 contains columns (A) Invoice (B) Name (C) Address (D) P.O.# ... and others. I need to perform a search of column A using an invoice number and if it IS FOUND, then replace the entire row with new data. If the invoice number IS NOT FOUND, then I need to add a new row. Is there a simple way to do this? Try Sub Macro() Dim lngNewRow As Long Dim varFound As Variant Dim varSearch As Variant varSearch = InputBox("Enter Invoice Number") Set varFound = Columns...

default template in Excel 2007
I know that I can save a template in 2003 with the name "Book.xlt" in the XLStart folder and it will become the default workbook when starting Excel. This does not seem to work in 2007. Does anyone know how to do this? Hi Trey In 2007 it is Book xltx or xltm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Trey Chandler" <tchandler@cteh.com> wrote in message news:OsQ2YAnaHHA.4808@TK2MSFTNGP04.phx.gbl... >I know that I can save a template in 2003 with the name "Book.xlt" in the > XLStart folder and it will become the default wo...

Launch Excel XP opens multiple docments. . .
Upon launching Excel XP, it automatically opens multiple documents and display errors such as, "table of contents.doc:file format is not valid". Some of the files are not in excel format. They are in Word or Wordperfect documents. What is causing this? Somebody Help. . . . Thanks. Excel will try to open any files it finds in either the XLSTART folder or "Alternate startup file location" or "at startup, open file in". This could be the source of your problem. You may have either of these set as the default file location. First thing I would try is to s...

Strange Excel Behavior
I have a sitution that is truly strange. I can open an excel document, make changes close the document. Then if I need to open it again in the same windows session and make changes, it will open, but I can not make any changes without rebooting? I've already gone through and reloaded Windows, but the strange behavior came back. The only thing loaded on the machine is WinXp Pro, SP1, Office XP with all patches and service packs applied, Mcafee 7.1 antivirus, and quicken 2003 deluxe. Why would this happen. You may wish to check the version that you saved the file in. Ensure you s...

How to move entire row?
Hello, I am using visual basic and Excel automation. How can I programmatically move entire row to the end and shift all other rows one row up? Jack Assuming that you want to select the row to be moved by selecting a cell in it and that Column A entried in it to the end of the data then try something like this: Sub MoveIt() Dim EndOfData As Long Dim Acr As Long EndOfData = Cells(Rows.Count, 1).End(xlUp).Row + 1 Acr = ActiveCell.Row Rows(Acr).Cut Destination:=Rows(EndOfData) Rows(Acr).Delete Shift:=xlUp End Sub -- HTH Sandy In Perth, the ancient...

Excel 2000 crashes when downloading from MS Template Gallery
I've been trying to download excel templates from the template gallery but excel crashes with a program error "a log will be created". The log shows a error code of c0000005 access violation. Anybody have any suggestions. Thanks... Renee ...

Excel Charts #3
I have some charts that are linked to a summary page. If I change the summary page the charts change accordingly. Each chart has a heading with different names on it. I set up the charts so that when I change the date on the first chart it will change the date on all the other charts. The line under the date is used to show whether or not the chart shows something under or over depending on the formula that I have entered in each chart. Somehow, when I go to any chart and change the term "Under" or "Over" it will change all the charts as well as show up in the heading on my...

How do I do a Mail Merge in Excel?
I'm trying to create individual sales invoices for multiple recipients. How would I go about doing a mail merge in Excel, or is there a better way to get the same results? Hi Shauna See http://www.mvps.org/dmcritchie/excel/mailmerg.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Shauna" <Shauna@discussions.microsoft.com> wrote in message news:77CD5C06-E0FB-4BCA-A623-B6A253BD4E75@microsoft.com... > I'm trying to create individual sales invoices for multiple recipients. > How > would I go about doing a mail merge in Excel, or is there a better w...

word object in excel reverts to original
Why would one computer on the network not take information entered into an embedded Word object in an excel ss? We have one computer on our network that will permit you to type info into the word field but as soon as you click anywhere else on the ss, the information typed in the word field reverts back to the original. ...

Write an excel 'sum' formula for a certain # of columns
I am trying to build a rolling spreadsheet based on days. I want to have a total for the trailing 30 days, but keep all the information that is older than 30 days. If I lock the formula, and add new days it keeps the original 30 day calculation, instead of adding on 1 new day and dropping off the oldest day of the 30. On Sat, 30 Jan 2010 13:11:02 -0800, ctante <ctante@discussions.microsoft.com> wrote: >I am trying to build a rolling spreadsheet based on days. I want to have a >total for the trailing 30 days, but keep all the information that is older >than ...

Days and dates in Excel
I'm tired of entering slashes to tell Excel that the numbers I'm entering are dates and colons to tell Excel that it's dealing with times. I asked a similar question here several weeks back and one of the responses gave me a link which took me to a site where a software guru had posted a formula to do this. Unfortunately, no one told me where to put this in Excel when I cut and paste it from the website. Then I got to thinking that Excel has under soecial formats formats to allow fast entry of SSN's and ZIP codes. When you enter nine numbers in a cell formated for ...

Damaged Hyperlink in Excel 2003 after saving an Excel Sheet
Hello, I've got a problem with hyperlinks in Excel 2003. Until I save the document they work fine - after saving a view of the hyperlinks are corrupted. There is a connection between the server which is named in the hyperlink and the setting under options-general called "standard save place" in german. I've got a mounted drive H which is located on \\filer\user\andy (Server is Windows 2000) and the entry H:\ on the option-general-tab. H:\ is connected with AD for the user (profil-"basic-folder") There's a further drive on this Fileserver for general using. It&...

How do I print labels from an excels spreadsheet?
Data in Excel Merge and Print in Word -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "missmeliss2" <missmeliss2@discussions.microsoft.com> wrote in message news:0C6C5B5B-4A07-4573-8DDB-48021C02FF1C@microsoft.com... > To add to Bernard's For help on Word mail merge using Excel as the data source have a look here http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge/CreateAMailMerge.htm http://www.mvps.org/word/FAQs/MailMerge/CreateADataSource.htm -- Paul B Always backup your data before trying some...

All Office 2000 and 2007 Excel and Word files open very slowly
If I open a Excel or Word file from a folder it takes a good 15 seconds for the file to open. If I start Word or Excel first and then do a file, open, the file will open instantly. I dowloaded the trial version of Office 2007 after this started with my Office 2000 and 2007 does it as well. I have removed and reinstalled Office 2000 and re-downloaded all SP's and updates. Have you cleared your TIF folder and Temp folders lately? Gord Dibben MS Excel MVP On Sun, 14 Oct 2007 09:04:01 -0700, thecloak <thecloak@discussions.microsoft.com> wrote: >If I open a Excel or Word file...

After concatenating data, how do I get Excel to recognise date?
I have used the "concatenate" function to put together data from 4 separate columns to form a date & time entry in the next column (the original 4 columns came from a .cvs file) on the basis of dd/mm/yyyy hh:mm format. I have defined the destimation column for 'Custom' Cell Format using the dd/mm/yyyy hh:mm choice. But Excel does not recognise the information as date & time, BUT, if I press the Return Key on each individual cell, then Excel does recognise the contents as a date & time. Then I can go on to chart my results on a timeline (the whole purpose o...

Excel error message
What am I doing wrong!!? Attempts to modify the range of my character counting formula, results in the value error message. Example: =MAX(LEN((U4:U840))) ,results are fine. However, add a zero to the range and receive the #VALUE! error message. Looks like an array formula to me. This means when you change it, yo must reenter it using the Ctrl-Shift-Enter sequence. Select the cell hit F2 and then hit Ctrl-Shift-Enter. -- Message posted from http://www.ExcelForum.com Hi, It seems as though either you're not entering the formula as an array formula (ctrl+shift+enter), or somewhe...

Excel VBA Wookbook Routines
Does anybody have the order of running event processes when an Excel Worksheet opens? I have a data collecttion running when the workbook opens and I want to review status of an database refresh after a WorkBook opens and gets the latest data on open. What event process do I use to review data after the woobbook refresh data from the database? I see events under Woobbook like Open, Activate, Sheet Activate, Sync, etc... What is the order of these events in which they activate when you open a sheet? Thank you in advance fro your help!!!! Mike Michael, Take a look here: http://ww...

Excel to publisher import
How can I import an excel spreadsheet to publisher doc.? If you want to insert the actual spreadsheet, you can go to Insert > Object > Create from file and browse to the Excel file. If you want to merge the contents of the cells, you can use the Mail Merge feature in Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Bill" <Bill@discussions.microsoft.com> wrote in message news:80F32E78-1710-40E9-8922-CE4EBD69F5C2@microsoft.com... > How can I imp...

Excel Formula, HELP!!!
Ok, I have two seperate Excel documents, let's call them: RAW ACTUAL In each these are many TABS, but I want to concentrate on only one tab from each Document: In RAW Doc, the DATA Tab In ACTUAL Doc, the FY06 Tab The DATA tab contains many columns filled with accounting data, but I want to pull a financial $ value out of it and paste that into a Cell in FY06 Tab, here are the exact details: In the FY06 Tab, in Cell AV46 is where I want the foluma to go inside of, and then I will copy it down, but basically... In FY06 Tab in column G there is a unique accounting value that identifies ...

Importing excel contacts into Outlook
Getting ODBC Excel Driver Login Failed message saying 'Could not find installable ISAM' when I try to import an excel worksheet with contact details. I have run Microsoft Office Diagnostics and that hasn't helped. In windows/system32 have file msexcl40.dll and C:\Program Files\Common Files\Microsoft Shared\OFFICE12\ACEEXCL.DLL exists. ...