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

The Excel Questions Thread - Page 2

post #16 of 534
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.
post #17 of 534
I should probably learn how to use Excel. I can add and shit, that's about it.
post #18 of 534
This is my new favorite threak /nerd.
post #19 of 534
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.
post #20 of 534
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.
post #21 of 534
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/
post #22 of 534
Quote:
Originally Posted by MetroStyles View Post
Haha, no I already have the model. I was just being a douchebag.

douchebag..
post #23 of 534
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.
post #24 of 534
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.
post #25 of 534
How can I fit an oversized excel table to a 1-page word document?
post #26 of 534
Nerds. I know how to make charts.
post #27 of 534
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.
post #28 of 534
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
post #29 of 534
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.
post #30 of 534
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.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread