The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. dirknibleck

    dirknibleck Member

    Messages:
    8
    Likes Received:
    0
    Joined:
    Apr 15, 2009
    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.
     


  2. Connemara

    Connemara [URL='http://www.youtube.com/watch?v=6jST2Sv63WQ']

    Messages:
    39,489
    Likes Received:
    1,724
    Joined:
    Mar 9, 2006
    I should probably learn how to use Excel. I can add and shit, that's about it. [​IMG]
     


  3. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    This is my new favorite threak /nerd.
     


  4. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    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.
     


  5. Dewey

    Dewey Senior member

    Messages:
    3,485
    Likes Received:
    28
    Joined:
    Jul 19, 2007
    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.
     


  6. haganah

    haganah Senior member

    Messages:
    6,351
    Likes Received:
    27
    Joined:
    Nov 24, 2007
    Location:
    New York, NY
    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/
     


  7. gnatty8

    gnatty8 Senior member

    Messages:
    9,480
    Likes Received:
    1,450
    Joined:
    Nov 12, 2006
    Location:
    Not in Atlanta, GA
    Haha, no I already have the model. I was just being a douchebag.

    douchebag..
     


  8. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    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.
     


  9. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York


  10. AntiHero84

    AntiHero84 Senior member

    Messages:
    2,430
    Likes Received:
    46
    Joined:
    Aug 28, 2008
    Location:
    Long Island
    How can I fit an oversized excel table to a 1-page word document?
     


  11. teddieriley

    teddieriley Senior member

    Messages:
    8,532
    Likes Received:
    264
    Joined:
    Apr 8, 2006
    Location:
    Wait, you smell that?
    Nerds. I know how to make charts.
     


  12. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    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.
     


  13. gdl203

    gdl203 Affiliate Vendor Dubiously Honored Affiliate Vendor

    Messages:
    36,857
    Likes Received:
    17,407
    Joined:
    Jun 9, 2005
    Location:
    New York
    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? [​IMG]

    That indirect fn does the trick without the additional labor/time - which I assume was the question to begin with
     


  14. dirknibleck

    dirknibleck Member

    Messages:
    8
    Likes Received:
    0
    Joined:
    Apr 15, 2009
    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.
     


  15. MetroStyles

    MetroStyles Senior member

    Messages:
    15,831
    Likes Received:
    30
    Joined:
    May 4, 2006
    Location:
    New York Shitty
    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? [​IMG] 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.
     


Share This Page

Styleforum is proudly sponsored by