Condense Information
I need to condense information that is in row 1(invoices) to show only one copy of each, until expanded. Upon being expanded it will show the original full layout of information. The following screenshot is an example of concept.
example.png
Any help is greatly appreciated!
Further notes :
This will span across several thousand rows and a dozen or so columns. I do not want to remove duplicates, simply hide the extra rows until I double click the cell invoice item.
Ok, I know I ran this by you before and you didn't answer but from what you wrote it looks like the subtotal function would work for you.
So I'm attaching an example for you to consider.
If you click on 1 (far upper left of sheet) you get the grand total, click on 2 and you get the sum totals for each item, click on 3 and they reopen to line level.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Thats brilliant! The only problem is, I am not sure how to implement this across my 3000+ rows/12+columns. What steps did you take to have it be sorted that way? I am sorry if I am being difficult, just a bit of a newb with these functions
Not a problem, in 2007 (I can't remember where it is in 2003) but I entered in the numbers down colA and some "fake" data and column headings across B through F and then hit the "subtotal" function. It opened a box that asked me how I wanted to total the data. So I selected "At each change in:" Col A, then "Use function" and I chose sum, then in the box "Add subtotal to: I checked each month then hit ok.
BTW, you're not being difficult I just wondered if you hadn't tried subtotal or didn't know about it or had tried it but it didn't work.
I didn't try it with 3000+ rows and 12 columns but it should work for both.
And you can click on any + (plus sign) beside the row total and it will only open that group.
I tried entering in the formula(so i could copy down along the rest of the rows), and all it does is replace the number that is in the cell already...?
Did you try it with mine or your data? Is there a chance you can upload a sample of your data so I can see? Just make sure no confidential or sensitive data is in it.
Are you trying to subtotal numbers or formulas that show numbers as their results?
Sorry for the late reply, cannot find an attachment uploader besides for pictures, so added here :
http://teamentertainer.com/Book1.xlsx
Thanks
Ok, I worked it out a little more advanced for you (see attached).
I put it in three worksheets. Two contain the data and one my notes.
I suspect if you are trying to subtotal on something with formulas that may be a problem so I noted in the notes sheet that you'll probably need to do a copy - paste special values to get rid of the formulas (again if that is the case).
I did this with 13 columns and 1872 rows. Didn't want to keep going but worked fine at that size.
Hope this helps more.
BTW, I couldn't get to your attachment.
That works with small bits of data, I gave it a whirl and it worked perfectly! However, when I tried on my own worksheet (A-T,1-65536) it gave the following error :
"To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.
If you do not have data in cells that can be shifted off the worksheet, you can reset which cells Excel considers nonblank. To do this, press CTRL+End to locate the last nonblank cell on the worksheet. Delete this cell and all cells between it and the last row and column of your data then save"
I did this, and it still gives the same error?
I may not be able to help you then. You originally noted 3000+ rows but from your last note it sounds like you have a full 65536 rows (a full 2003 worksheet). I wonder if it can handle that many rows because it is essentially a full worksheet, I would think it would difficult to compress since the function itself adds rows for totals at each break and a grand total. The nice thing when you move to excel 2007 or later is you have over a million rows to work with. As long as you're working with 2003 you may have to break the data up.
Hopefully someone else will be able to help you with this then.
I will keep watching though to see what anyone else can come up with.
I copy pasted my data into the worksheet you provided, and ran subtotal- it is doing SOMETHING(loading bar for 5min now) so it was probably an err on my part if it works. I am running excel 2013 btw, I'll let you know what happens
Oh, ok, I was just going by your user info that notes you're using excel 2003.
I have some very large excel files (over 10000kb) and they handle a lot of complex calculations but usually quickly (w/in 30 seconds to a minute) so I would wonder if you have too many things open taking up a lot of memory or not enough free memory for it to work quickly?
It worked! The only exception being that it replaced some of the column data(text) with 0's, and generated its own #'s to replace the invoice #.
Thanks a lot!
Sure, glad it worked. BTW, just redid the example I pasted for you to go to almost 60,000 rows and it only took a couple secs but did it all. It may be a bit unwieldy to use but...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks