Results 1 to 14 of 14

Thread: Excel Gurus, I need you!

  1. #1
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Excel Gurus, I need you!

    If anyone wants to flex their spread sheet muscles and help and damsel in distress ( no , not me in a skirt , but angeleyes , whos having an uncharacteristic shy moment ) - I've got a bit of a brain teaser..

    sitting comfortably ? here goes....

    Quote Originally Posted by angeleyes
    I really need some help as i am struggling to come up with a solution for this.

    We have a debtors spreadsheet that looks at those debts over 90 days and then we manually categorise them into what stage they are at eg 14 day legal letter, With Solicitors, Write off, Payment Schedule etc.

    We are looking to automate the process slightly. If something is with the solicitors it tends to stay this way for a few months, so we know that if it was in the solicitors column in July it will still be in the solicitors column in Aug. I've been using the following formula to pick up this data

    =IF(ISBLANK('July'!G6),0,$C5)

    where G6 is the cell for 14 day letter sent and C5 contains August's total.

    The problem that i am getting is that the rows don't always match for example we may have one Debtor that has paid off their Debt or we have a new Debt that has just rolled into 90 days. Ideally i need a formula that will give me the same detail as above (providing a 0 if that column in the previous month was blank and providing the total for the new month if it wasn't), but i need it to be based on looking up the same Customer number in both sheets.

    Is there anyway that i can do this?

    Any help would be greatly appreciated.
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  2. Received thanks from:

    angeleyes (03-09-2010)

  3. #2
    Seething Cauldron of Hatred TheAnimus's Avatar
    Join Date
    Aug 2005
    Posts
    17,164
    Thanks
    803
    Thanked
    2,152 times in 1,408 posts

    Re: Excel Gurus, I need you!

    Sounds like a job for VLOOKUP.

    But without seeing more of the data I'm not sure I understand enough from that spec (plus I have my own work to do!)
    throw new ArgumentException (String, String, Exception)

  4. #3
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: Excel Gurus, I need you!

    you do work ? I thought you just created masterpieces
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  5. #4
    now with added engagement ring (tm)
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    1
    Thanked
    2 times in 2 posts

    Re: Excel Gurus, I need you!

    VLOOKUP won't work as the number i want it to return is based in the same spreadsheet as the lookup value.

    I need it to go to sheet A, have a look for the client number (based on that row on sheet B, then go across the row in sheet A to the split out columns and see if it has a number in it, if it does i need it to the go back into sheet B and return the value in there, if it doesn't then i need it to return a 0.

    What i need is something that is a vlookup up but allows me to but the IF formula in instead of specifying a column.

    Does that make any sense???

  6. #5
    Registered+
    Join Date
    Sep 2006
    Posts
    32
    Thanks
    1
    Thanked
    10 times in 4 posts
    • Wooglie's system
      • Motherboard:
      • Abit P5B
      • CPU:
      • Intel C2d E7200 @ 3.6GHz
      • Memory:
      • 4GB DDr2 G.Skill
      • Storage:
      • 150GB WD Velociraptor
      • Graphics card(s):
      • NVidia GTX 275
      • Case:
      • Hiper Osiris
      • Operating System:
      • Windows 7 Pro 64

    Re: Excel Gurus, I need you!

    Can't you just replace the column with the IF statement?
    EG:
    Code:
    =VLOOKUP(A22,Stats!I60:K65,IF(A23=C23,2,3),FALSE)
    ]
    EdIT: Actually I'm not sure if that is what you want. It should be doable, but it's hard to work out without the sheet in front of me.

  7. Received thanks from:

    Moby-Dick (06-09-2010)

  8. #6
    SiM
    SiM is offline
    Senior Member
    Join Date
    Apr 2006
    Location
    London
    Posts
    7,787
    Thanks
    300
    Thanked
    633 times in 422 posts
    • SiM's system
      • Motherboard:
      • P5K Premium
      • CPU:
      • Q6600
      • Memory:
      • 8GB PC2-6400 OCZ ReaperX + Platinum
      • Storage:
      • 3 x 320gb HD322HJ single platter in Raid 0
      • Graphics card(s):
      • PNY GTX285
      • PSU:
      • Corsair TX650W
      • Case:
      • Antec 1200
      • Monitor(s):
      • 2407-HC

    Re: Excel Gurus, I need you!

    Tldr but try offset and match

  9. #7
    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: Excel Gurus, I need you!

    Sounds similar to a problem I solved some years ago, which ended up needing array formulae. Not done Excel seriously for quite a while though. As others have said, it's hard to work out something like this without the actual spreadsheet in front of you.

    My first thought would be whether you could add a column to sheet A that did some of the work? That might make it possible to do a direct VLookup on sheet A picking up the calculated column as the output...?

    Of course (as some regular Hexites may have realised by now), my usual advice once you're doing this much data manipulation is to put the whole thing in a database, where it actually belongs

  10. #8
    now with added engagement ring (tm)
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    1
    Thanked
    2 times in 2 posts

    Re: Excel Gurus, I need you!

    A database would be nice, but this company seems to be totally adverse to them.

    I've not had a chance to tinker with it today as i seem to have more urgent work coming out of my ears at the moment , hopefully Monday will be a little bit easier and i will attempt to get a screen shot up or something to make it a little easier to understand where i am coming from.

    My boss has devised a couple of work arounds, but they are both quite dirty and manual and i am sure there is an nicer, easier way to do it.

    Thanks for all your suggestions so far.

  11. #9
    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: Excel Gurus, I need you!

    Quote Originally Posted by angeleyes View Post
    A database would be nice, but this company seems to be totally adverse to them.
    Sadly most companies are - the problem is, if you come up with a clever solutions to this in Excel it just persuades people that Excel was the right tool in the first place! Not an easy one to get round.

    You could always just try telling them that it's horribly error-prone in Excel and moving it all to a well-written Access app will increase productivity and reduce the risk of errors... that can work sometimes

  12. #10
    Member
    Join Date
    Nov 2003
    Location
    Salisbury
    Posts
    170
    Thanks
    2
    Thanked
    6 times in 6 posts
    • hibby's system
      • Motherboard:
      • Supermicro C7Z87-OCE
      • CPU:
      • Intel Core i7-4770
      • Memory:
      • 2x8GB Corsair DDR3 Vengeance LP 1600MHz (9-9-9-24)
      • Storage:
      • 256GB Samsung 840 Pro; 120GB OCZ Vertex 3; 1TB Samsung SpinPoint F1 HD103UJ; 250GB Seagate 7200.10
      • Graphics card(s):
      • Sapphire HD 7970 Vapor-X GHz
      • PSU:
      • 620W Corsair HX620
      • Case:
      • Antec P182
      • Operating System:
      • Windows 7 Ultimate 64bit
      • Monitor(s):
      • 24" Dell U2413; 24" Dell 2408WFP; 17" Dell 1704FPT
      • Internet:
      • Slow, rural broadband

    Re: Excel Gurus, I need you!

    Try this (assuming entry into row 5):
    Code:
    =IF(ISERROR(MATCH(A5,July!A:A,0)),$C5,IF(ISBLANK(INDEX(July!G:G,MATCH(A5,July!A:A,0),1)),$C5,0))
    MATCH(A5,July!A:A,0) gets the row number in "July" where the debtor name/ID is the same as the current row in "August" (assuming this is stored in column A).

    The ISERROR checks to see if this can be found, if not the first IF statement results in the value from column C for this row being displayed.

    The second IF statement comes into play if the name/ID already exists - it then uses INDEX to lookup the corresponding value in column G of "July" and where the row number is the same as the MATCH statment. If this is blank, you get the value from column C, otherwise zero.

    Hope that helps!
    If you can't keep up, stick with reality...

  13. Received thanks from:

    Moby-Dick (06-09-2010)

  14. #11
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: Excel Gurus, I need you!

    cheers - I'll make sure angeleyes sees this
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  15. #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: Excel Gurus, I need you!

    I'm not entirely convinced Hibbys solution does what's needed, tbh I need to get my hands on the sheet (with personal details removed of course!) to be sure.

    This is what I hate about Excel, running it as an imagination exercise is *really* tough, but give me a sheet and the formulas, and I can run them backwards, and I tend to make "human friendly" sheets that turn numbers into words.

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


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

  16. #13
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,665
    Thanks
    53
    Thanked
    385 times in 314 posts

    Re: Excel Gurus, I need you!

    I think she's going to put together an anonymised version to help out ( getting zero sleep last night due to 16 month old isn't condusive to a clear head at work )
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  17. #14
    now with added engagement ring (tm)
    Join Date
    Dec 2009
    Posts
    47
    Thanks
    1
    Thanked
    2 times in 2 posts

    Re: Excel Gurus, I need you!

    lack of sleep and the fact that i am in the middle of my year end/month end tasks!! Will attempt to get something up this afternoon once i've finished reviewing my figures.

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
  •