Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 2

post #16 of 527
5/4/09 at 3:14pm
The easiest way to dedupe a list is to use Data->Filter->advanced filter

Select the copy to option. Indicate where you want the resulting deduped list to appear, and then check the unique values box.
Reply
Reply

Featured Stories on Styleforum

Derek Rose at Pitti Uomo 83
Fedora Lounge on Aero Leather jackets
Random fashion thoughts - aka community ramblings
post #17 of 527
5/4/09 at 3:15pm
I should probably learn how to use Excel. I can add and shit, that's about it.
Reply
Reply
post #18 of 527
5/4/09 at 3:17pm
This is my new favorite threak /nerd.
Reply
Reply
post #19 of 527
5/4/09 at 3:21pm
Thread Starter 
Quote:
Originally Posted by dirknibleck View Post
The easiest way to dedupe a list is to use Data->Filter->advanced filter

Select the copy to option. Indicate where you want the resulting deduped list to appear, and then check the unique values box.

The point of all this, I think, is to use formulas for everything. If you do all this filtering and copy/paste, your filtered list becomes obsolete if the original changes. Also, the programming behind the built-in functions in excel is very slick; by that I mean they execute much faster than anything you can come up with in VBA.
Reply
Reply
post #20 of 527
5/4/09 at 3:25pm
How do I create a chart that automatically updates itself? Let's say I have two columns of information -- date and, I don't know, money spent on shoes. And then I want to chart a 30-day average for money spent on shoes. Every day I update the money-spent-on-shoes column as I change the zeros to $money$$. Is there a way to make the line chart to update itself every day? It should grow horizontally day after day.
Reply
Reply
post #21 of 527
5/4/09 at 3:29pm
Quote:
Originally Posted by Dewey View Post
How do I create a chart that automatically updates itself? Let's say I have two columns of information -- date and, I don't know, money spent on shoes. And then I want to chart a 30-day average for money spent on shoes. Every day I update the money-spent-on-shoes column as I change the zeros to $money$$. Is there a way to make the line chart to update itself every day? It should grow horizontally day after day.
http://spreadsheetpage.com/index.php...nter_new_data/
Reply
Reply
post #22 of 527
5/4/09 at 3:35pm
Quote:
Originally Posted by MetroStyles View Post
Haha, no I already have the model. I was just being a douchebag.

douchebag..
Reply
Reply
post #23 of 527
5/4/09 at 3:38pm
Thread Starter 
Quote:
Originally Posted by Dewey View Post
How do I create a chart that automatically updates itself?

Let's say I have two columns of information -- date and, I don't know, money spent on shoes. And then I want to chart a 30-day average for money spent on shoes. Every day I update the money-spent-on-shoes column as I change the zeros to $money$$. Is there a way to make the line chart to update itself every day? It should grow horizontally day after day.

There's an easy way... and a macro way. The easy way is just to keep the empty rows hidden. The chart will condense itself to only show the unhidden days. You will have to unhide the rows to do your updates, and then rehide the same rows (minus the new one that is no longer empty), and your chart will also update.

If you're okay with VBA it's not a huge chore. The reason you have to use VBA to do this seamlessly is that the formulas the dictate the ranges of a data series in a chart don't accept functions (EDIT: But they DO accept named ranges!!!).

For instance, the following is not valid:

=SERIES(,Sheet1!$A$2:$A$23,INDIRECT("Sheet1!$B$2:$B$23"),1)

...even though I think it should be. Maybe Excel 2007 is different, but I haven't used it.
Reply
Reply
post #24 of 527
5/4/09 at 3:40pm
Thread Starter 
Quote:
Originally Posted by haganah View Post

Fuck, very nice. Hadn't thought of using a named range. Ignore my idiot post above. You win.
Reply
Reply
post #25 of 527
5/4/09 at 3:42pm
How can I fit an oversized excel table to a 1-page word document?
Reply
Reply
post #26 of 527
5/4/09 at 3:44pm
Nerds. I know how to make charts.
Reply
Reply
post #27 of 527
5/4/09 at 3:44pm
Thread Starter 
Quote:
Originally Posted by AntiHero84 View Post
How can I fit an oversized excel table to a 1-page word document?

Do you want to embed it or are you okay with just having the image? Excel to word/ppt is a pain and I'm no good at it.
Reply
Reply
post #28 of 527
5/4/09 at 3:47pm
Quote:
Originally Posted by MetroStyles View Post
Even if you needed it ordered in a specific way, you could just add ordered numbering in an adjacent column and re-order to the old order after the sort.

I don't know if you were being facetious but how would adding a, b and c on the side be faster than removing the rows?

That indirect fn does the trick without the additional labor/time - which I assume was the question to begin with
Reply
Reply
post #29 of 527
5/4/09 at 3:51pm
Quote:
Originally Posted by CunningSmeagol View Post
The point of all this, I think, is to use formulas for everything. If you do all this filtering and copy/paste, your filtered list becomes obsolete if the original changes. Also, the programming behind the built-in functions in excel is very slick; by that I mean they execute much faster than anything you can come up with in VBA.

You're right, but if the requirement is only to produce the unique list once, an array formula is overkill, and generally beyond the comprehension of everyday users. I find that frequently people forget that there are simple ways to do things in Excel, and instead use more complicated features - ie. Macros, and sometimes Array formulas, because they have used these tools in the past to manage a more difficult problem.
Reply
Reply
post #30 of 527
5/4/09 at 3:56pm
Quote:
Originally Posted by gdl203 View Post
I don't know if you were being facetious but how would adding a, b and c on the side be faster than removing the rows? That indirect fn does the trick without the additional labor/time - which I assume was the question to begin with
The indirect is much better than my proposed solution. What I meant was type 1 on the top row in the adjoining column. Drag down to the end of the column, which gives you ordinal numbering of all entries. Sort by name. Delete blanks at bottom. Sort by numbers. The end. Yeah, it sucks, but it's a lot faster than manually deleting.
Reply
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat

Featured Stories on Styleforum

Derek Rose at Pitti Uomo 83
Fedora Lounge on Aero Leather jackets
Random fashion thoughts - aka community ramblings
Styleforum › Forums › General › General Chat › The Excel Questions Thread