Results 1 to 13 of 13

Thread: Anyone good at Excel?

  1. #1
    IBM
    IBM is offline
    there but for the grace of God, go I IBM's Avatar
    Join Date
    Dec 2003
    Location
    West London
    Posts
    4,187
    Thanks
    149
    Thanked
    244 times in 145 posts
    • IBM's system
      • Motherboard:
      • Asus P5K Deluxe
      • CPU:
      • Intel E6600 Core2Duo 2.40GHz
      • Memory:
      • 2x2GB kit (1GBx2), Ballistix 240-pin DIMM, DDR2 PC2-6400
      • Storage:
      • 150G WD SATA 10k RAPTOR, 500GB WD SATA Enterprise
      • Graphics card(s):
      • Leadtek NVIDIA GeForce PX8800GTS 640MB
      • PSU:
      • CORSAIR HX 620W MODULAR PSU
      • Case:
      • Antec P182 Black Case
      • Monitor(s):
      • Dell 2407WPF A04
      • Internet:
      • domestic zoom

    Question Anyone good at Excel?

    Anyone good at Excel?

    It puzzles me.

    Can anyone recommend any good sites/tutorials for getting your head around some of the novice level stuff?

    One example of something I'm looking to do;

    I have a sheet with user data (UserID, firstname, lastname, age) and would like to produce a new list of lastnames where the age value exceeds 25. A VLookup does most of what I need, but how do I include a 'greater than' parameter, and how do I produce a list rather than a single value?

    There's just so much crap about previous versions of excel online...very difficult to find what you need.

    Thanks!
    sig removed by Zak33

  2. #2
    Member
    Join Date
    Feb 2009
    Posts
    103
    Thanks
    3
    Thanked
    5 times in 5 posts
    • Leggie's system
      • Motherboard:
      • Asus M4A785TD-V EVO
      • CPU:
      • AMD Athlon X4 620
      • Memory:
      • 2 x 2GiB G.Skill Ripjaws DDR3 PC3-12800C7
      • PSU:
      • QTechnology QT-03400G 400W
      • Operating System:
      • Windows 7 Home Premium x64
      • Monitor(s):
      • Dell 2709W

    Re: Anyone good at Excel?

    You could use a filter (select your column header row, then go to Data -> Filter). Then click the triangle at the top of your "age" column, choose "Number Filters", and then "Greater Than..."

    This was in Excel 2007, Excel 2010 should be similar.

    I agree with you that it's difficult to track down Excel help online - same applies to all Office programs, I suspect.

  3. #3
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Anyone good at Excel?

    Honest opinion?

    Get that data out of Excel and into Access. What you are trying to do is classic database territory, and Excel is not the right tool for the job. It's relatively straightforward to import the data, write a query, then export back to Excel if you need the data in Excel for some arcane reason. But really this is what Access is designed for.

  4. #4
    Pork & Beans Powerup Phage's Avatar
    Join Date
    May 2009
    Location
    Kent
    Posts
    6,260
    Thanks
    1,618
    Thanked
    608 times in 518 posts
    • Phage's system
      • Motherboard:
      • Asus Crosshair VIII
      • CPU:
      • 3800x
      • Memory:
      • 16Gb @ 3600Mhz
      • Storage:
      • Samsung 960 512Gb + 2Tb Samsung 860
      • Graphics card(s):
      • EVGA 1080ti
      • PSU:
      • BeQuiet 850w
      • Case:
      • Fractal Define 7
      • Operating System:
      • W10 64
      • Monitor(s):
      • Iiyama GB3461WQSU-B1

    Re: Anyone good at Excel?

    The most commonly used db in the world is Excel. I am currently working on a nice little pivot with 32k lines/records.
    If I knew Access and it's tools I probably would.
    Society's to blame,
    Or possibly Atari.

  5. #5
    IBM
    IBM is offline
    there but for the grace of God, go I IBM's Avatar
    Join Date
    Dec 2003
    Location
    West London
    Posts
    4,187
    Thanks
    149
    Thanked
    244 times in 145 posts
    • IBM's system
      • Motherboard:
      • Asus P5K Deluxe
      • CPU:
      • Intel E6600 Core2Duo 2.40GHz
      • Memory:
      • 2x2GB kit (1GBx2), Ballistix 240-pin DIMM, DDR2 PC2-6400
      • Storage:
      • 150G WD SATA 10k RAPTOR, 500GB WD SATA Enterprise
      • Graphics card(s):
      • Leadtek NVIDIA GeForce PX8800GTS 640MB
      • PSU:
      • CORSAIR HX 620W MODULAR PSU
      • Case:
      • Antec P182 Black Case
      • Monitor(s):
      • Dell 2407WPF A04
      • Internet:
      • domestic zoom

    Re: Anyone good at Excel?

    Thanks for the responses.

    To be honest, part of the reason I find this so frustrating is that I've worked in Access & SQL for such a long time that I know how easy it would be - however, the stuff I want to do seems so logically part of what Excel should be able to deal with that my head wants to explode.

    Leggie - the need here is driven from being able to build a worksheet which just prepopulates depending on data contained within other worksheets. I don't want to have to manually rebuild selections every time I generate the report.

    This is also why dumping the data into a real db (yes, I jokingly referred to Access as a 'real db') just isn't feasible.

    Phage - Pivot tables might be a way to do it, but I've never been able to get effective filters to differentiate by a value range - any idea how to do it?
    sig removed by Zak33

  6. #6
    Not a good person scaryjim's Avatar
    Join Date
    Jan 2009
    Location
    Gateshead
    Posts
    15,196
    Thanks
    1,230
    Thanked
    2,291 times in 1,874 posts
    • scaryjim's system
      • Motherboard:
      • Dell Inspiron
      • CPU:
      • Core i5 8250U
      • Memory:
      • 2x 4GB DDR4 2666
      • Storage:
      • 128GB M.2 SSD + 1TB HDD
      • Graphics card(s):
      • Radeon R5 230
      • PSU:
      • Battery/Dell brick
      • Case:
      • Dell Inspiron 5570
      • Operating System:
      • Windows 10
      • Monitor(s):
      • 15" 1080p laptop panel

    Re: Anyone good at Excel?

    One of our clients does this on a large scale basis for some of their monthly and quarterly reports.

    ... using a mess of VBA macros.

    Excel is not a data querying tool, it's a financial calculation package. MS have added some snazzy functionality into it on the side, but it essentially remains a tool for manipulating numerical data financially / statistically. There's no reason for any of that to include record-based selection and manipulation, which is what you're trying to do.

    Is there any way to move it out of Excel at all? What's the target audience? Could it be converted to an internal web app with Excel export functionality?

    If you can't get it out of Excel, look into "array Formulae" - I found them a godsend last time I was asked to do database-type manipulations in Excel (although that was many years ago in Excel 2003)
    Last edited by scaryjim; 02-02-2011 at 05:51 PM.

  7. #7
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    Re: Anyone good at Excel?

    Stick with excel. Once you have it sussed you will find that you can convert all kinds of data into various useful formats in no time at all. Very handy indeed.
    To err is human. To really foul things up ... you need a computer.

  8. Received thanks from:

    Zak33 (04-02-2011)

  9. #8
    Welcome to stampytown! Salazaar's Avatar
    Join Date
    Dec 2004
    Location
    Oxford-ish
    Posts
    4,459
    Thanks
    505
    Thanked
    353 times in 254 posts
    • Salazaar's system
      • Motherboard:
      • Asrock B450m Steel Legend
      • CPU:
      • Ryzen 5 3600
      • Graphics card(s):
      • 5700 XT

    Re: Anyone good at Excel?

    Why not add an extra data field which calculates whether the respective age field is over 25 or not and returns a 1 or 0 and then run a pivot table based on that result?
    ____
    (='.'=)
    (")_(")

  10. #9
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Anyone good at Excel?

    Quote Originally Posted by yamangman View Post
    Stick with excel. Once you have it sussed you will find that you can convert all kinds of data into various useful formats in no time at all. Very handy indeed.
    that... exactly...

    8 months ago I had zero idea and now... I'm quite alot better

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  11. #10
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Anyone good at Excel?

    Quote Originally Posted by IBM View Post
    I have a sheet with user data (UserID, firstname, lastname, age) and would like to produce a new list of lastnames where the age value exceeds 25. A VLookup does most of what I need, but how do I include a 'greater than' parameter, and how do I produce a list rather than a single value?
    there's about 1000 ways to do this.

    I'm gonna presume you're a total novice here:

    If you click the top left corner box, not a cell, but the top left of them all.. the corner of the page..... to make sure you have the whole doc, then click Data at the top and then the filter button (looks like a plastic cone) so that all the columns are filterable.

    then click the age column at the top to filter it, and on the drop down option list choose "numbers filter" and then choose "Greater Than" and put your age minimum in te top box (also useable for "In between" etc)
    Now that you have only the people over a certain age..... if you just want that result as a document and no one to see the stuff under that age...

    At the bottom of the doc, you'll see the tabs for the sheets... right click on the one you'e using and choose "Move or Copy".

    in the resulting box, in the top option click the drop down and choose (new book), and this will allow you to make a new excel doc just with this data.

    then in the very bottom, tick the Copy box, so you still have the original data in this first filtered doc.

    When you click OK, you'll get the new "Book" (ie Excel doc) with the new stuff and none of the rest.

    Now the cunning bit.

    Top left.. click the corner of the new excel doc, so you have the entire doc. Then right it click and COPY.

    then, on the top left cell, right click and choose PASTE SPECIAL. and on the next option, choose VALUES.

    What this does is takes ALL the numbers you can see, but removes all links, V lookups, and any other calculations. It's just the numbers , like you typed them in.

    Leaving you a virginal doc with no other attachements so send to who ever needs it and only it.


    So.. in short... right click the entire doc, copy, then paste it back over itself, using Paste Special and Value only..

    that trick is worth gold... over and over.

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  12. #11
    HEXUS.timelord. Zak33's Avatar
    Join Date
    Jul 2003
    Location
    I'm a Jessie
    Posts
    35,157
    Thanks
    3,105
    Thanked
    3,138 times in 1,916 posts
    • Zak33's system
      • Storage:
      • Kingston HyperX SSD, Hitachi 1Tb
      • Graphics card(s):
      • Nvidia 1050
      • PSU:
      • Coolermaster 800w
      • Case:
      • Silverstone Fortress FT01
      • Operating System:
      • Win10
      • Internet:
      • Zen FTC uber speedy

    Re: Anyone good at Excel?

    othher pieces of GOLD.

    Control and H

    allows you to search for words, numbers, combos of them, etc.. and replace with something else

    Genius for me, when wanting to remove the word Metallic from a Colouur of paint from a database export.

    Another gem is LEFT or MID or RIGHT

    If you have a long numer and want just the last 5 numbers (as an example)

    click in the cel you want the result

    type LEFT(a,b) where
    a= the cell
    b= the number of numbers you want it to display from the left

    so a chassis number looks like this

    VF3XYZ7890654300 as an example

    to get the left 4 numbers you'd type

    =LEFT(cell reference,4)

    to see the last 8 you'd type

    =RIGHT(cell reference,8)

    (he cell reference is just the cell you ave the original stuff in,type it OR click on it.....)

    and to grab stuff from the middle, eg the 3rd digit to the 7th you'd type

    =MID(cell reference,3,7) and you'd get it.

    and it works for words etc

    Quote Originally Posted by Advice Trinity by Knoxville
    "The second you aren't paying attention to the tool you're using, it will take your fingers from you. It does not know sympathy." |
    "If you don't gaffer it, it will gaffer you" | "Belt and braces"

  13. #12
    Mostly Me Lucio's Avatar
    Join Date
    Mar 2007
    Location
    Tring
    Posts
    5,163
    Thanks
    443
    Thanked
    448 times in 351 posts
    • Lucio's system
      • Motherboard:
      • Gigabyte GA-970A-UD3P
      • CPU:
      • AMD FX-6350 with Cooler Master Seldon 240
      • Memory:
      • 2x4GB Corsair DDR3 Vengeance
      • Storage:
      • 128GB Toshiba, 2.5" SSD, 1TB WD Blue WD10EZEX, 500GB Seagate Baracuda 7200.11
      • Graphics card(s):
      • Sapphire R9 270X 4GB
      • PSU:
      • 600W Silverstone Strider SST-ST60F
      • Case:
      • Cooler Master HAF XB
      • Operating System:
      • Windows 8.1 64Bit
      • Monitor(s):
      • Samsung 2032BW, 1680 x 1050
      • Internet:
      • 16Mb Plusnet

    Re: Anyone good at Excel?

    Also useful tricks for data manipulation are

    LEN(a1): this gets the length of a string, combined with MID and a magic number, you can slice off a section of a variable length string. For example Stripping out spaces at the end of a number stored as text.

    &, Lets you join cells together, useful for creating human friendly statuses. E.g. "order more, only "& a1 &" of "& b1 &"remaining". Think of it like using a . in php

    Also investigate array formulas,

    (\___/) (\___/) (\___/) (\___/) (\___/) (\___/) (\___/)
    (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=) (='.'=)
    (")_(") (")_(") (")_(") (")_(") (")_(") (")_(") (")_(")


    This is bunny and friends. He is fed up waiting for everyone to help him out, and decided to help himself instead!

  14. #13
    Large Member
    Join Date
    Apr 2004
    Posts
    3,720
    Thanks
    47
    Thanked
    99 times in 64 posts

    Re: Anyone good at Excel?

    As zak has hinted an iterative approach is useful to eventually bash your bad data into shape. This may span several sheets before it becomes clear, but it's a nice way to extrapolate your data.
    To err is human. To really foul things up ... you need a computer.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Vlookup not working (sort of)
    By piggeh in forum Help! Quick Relief From Tech Headaches
    Replies: 9
    Last Post: 03-09-2010, 04:35 PM
  2. Replies: 0
    Last Post: 10-10-2008, 12:37 PM
  3. .NET Reading Multiple Excel Files
    By Shadow_101 in forum Software
    Replies: 5
    Last Post: 23-11-2007, 03:22 PM
  4. Excel + Database + Automated
    By Kezzer in forum Software
    Replies: 4
    Last Post: 29-03-2007, 04:54 PM
  5. Replies: 6
    Last Post: 12-08-2005, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •