Boards

anyone need any help with anything in excel?

cat_race [Edit] [Delete] 75 replies 14:43 March 11th, 2013

dedicated thread for it

Reply

View NestedLinear

Ok I actually do.

At the moment there’s a *live* document with a number of Vlookups and sumifs that need to correct as they drive some important formulae. However one of the vlookups comes from a different document the name of which changes every day. At the moment someone has to go in and change the vlookup slightly every morning to reflect the new file for the day.

Is there any way of automating this? The new file every day has a standard naming convention, the only part which changes is the date which is in UK date format and all the files are saved in the same place. Is there a way of telling the v lookup to change the file it is looking up from depending on what today’s date is?

Kitchmo | 11 Mar '13, 14:52 | X | ^ This | Reply
- i've never had to do anything like that but i suspect this....
  
  =today()
  
  will fill a cell with today's date. if the lookup into the file name contains a cell that references the date (or the date minus 1 or whatever) that you just put in a hidden tab then that might do the job. unsure though, going to try and test something now
  
  cat_race @Kitchmo | 11 Mar '13, 14:54 | X | ^ This | Reply
  - not sure this works
    
    cat_race @cat_race | 11 Mar '13, 14:57 | X | ^ This | Reply
    - It doesn't. First thing he tried apparently
      
      I did pass your suggestion on though.
      
      Kitchmo @cat_race | 11 Mar '13, 15:02 | X | ^ This | Reply
- write some vba code
  
  that'll do it.
  
  Matt_was_taken @Kitchmo | 11 Mar '13, 14:55 | X | ^ This | Reply
  - I won't be doing anything
    
    I just feel sorry for the person who has to waste his time every morning before I get into the office.
    
    Kitchmo @Matt_was_taken | 11 Mar '13, 15:01 | X | ^ This | Reply
    - woah!!! i thought i was helping you! not some schmo you work with!
      
      cat_race @Kitchmo | 11 Mar '13, 15:02 | X | ^ This | Reply
      - Ola isn't a shmo! He's a fine man.
        
        And I feel sorry for him, so you're indirectly helping me.
        
        Kitchmo @cat_race | 11 Mar '13, 15:04 | X | ^ This | Reply
        
        ok, that's fine
        
        this feels like a small thing that should be doable without vba. will get back to you shortly
        
        cat_race @Kitchmo | 11 Mar '13, 15:06 | X | ^ This | Reply
        
        what's the format of the file name?
        
        i think you can just do it using INDIRECT
        
        cat_race @cat_race | 11 Mar '13, 15:13 | X | ^ This | Reply
        
        you can.
        
        See below.
        
        thewarn @cat_race | 11 Mar '13, 15:13 | X | ^ This | Reply
        
        there we go!
        
        cat_race @thewarn | 11 Mar '13, 15:16 | X | ^ This | Reply
- Get a junior to update the lookups every morning
  
  Balonz @Kitchmo | 11 Mar '13, 15:00 | X | ^ This | Reply
  - That's what happens at the moment, the b/o guy does it
    
    but it is a bit of a waste of his time, he's a busy man.
    
    Kitchmo @Balonz | 11 Mar '13, 15:02 | X | ^ This | Reply
- http://www.excelforum.com/excel-formulas-and-functions/677285-changing-file-names-within-worksheets.html
  
  thewarn @Kitchmo | 11 Mar '13, 15:10 | X | ^ This | Reply
  - Thanks, shall forward on to Ola.
    
    Kitchmo @thewarn | 11 Mar '13, 15:42 | X | ^ This | Reply
- hmmm
  
  I think INDIRECT might work.
  
  Balonz @Kitchmo | 11 Mar '13, 15:14 | X | ^ This | Reply
- This kind of works:
  
  =LOOKUP(1,INDIRECT("'C:\Users\Phil\Desktop\Datafiles\[" & TEXT(TODAY(),"yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5"),INDIRECT("'C:\Users\Phil\Desktop\Datafiles\[" & TEXT(TODAY(),"yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5"))
  
  However, for some reason it will only work when the lookup file is open.
  
  If you don't mind vba the formula to do this is really simple:
  
  Sub UpdateLookup()
  Range("A6") = "=LOOKUP(1,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5)"
  End Sub
  
  If you change the sub name of the above it will automatically run when you open the workbook:
  
  Private Sub Workbook_Open()
  Range("A6") = "=LOOKUP(1,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$A$1:$A$5,'C:\Users\Phil\Desktop\Datafiles\[" & Format(Now(), "yyyy-mm-dd") & ".xlsx]Sheet1'!$B$1:$B$5)"
  End Sub
  
  You will obviously need to change the paths and date format etc if you want it to work with your files.
  
  Phil9064 @Kitchmo | 11 Mar '13, 19:19 | X | ^ This | Reply
How about this?

Two lists of street names. In columns. Whole streetnames in one cell.

Can I check if a part of a street name that appears in one column appears another?

E.g. There might be a Bedwetter Avenue in one column, and a Bedwetter Crescent in another, and I wanna flag that up somehow.

Even if it is doable, I can envisage the results being cack cos of the duplication of generic parts of streetnames like Avenue and Crescent etc.

Probs best just to copy the two sheets into one so that all the street names are in one single column, and just sort all by name, right?

TheWza | 11 Mar '13, 15:19 | X | ^ This | Reply
- so you'r trying to find any two that have the same first bit? but it doesn't matter what it is?
  
  cat_race @TheWza | 11 Mar '13, 15:21 | X | ^ This | Reply
  - Correct.
    
    TheWza @cat_race | 11 Mar '13, 15:23 | X | ^ This | Reply
    - how big and how varied are the lists you have?
      
      cat_race @TheWza | 11 Mar '13, 15:29 | X | ^ This | Reply
      - less than a five hundred rows.
        
        not that varied. i.e. I'd expect less than ten percent of the entries in either column to return a match in the other column.
        
        TheWza @cat_race | 11 Mar '13, 16:31 | X | ^ This | Reply
        
        *ah, that makes them fairly varied, i guess.
        
        TheWza @TheWza | 11 Mar '13, 16:32 | X | ^ This | Reply
        
        there is apparently a thing which i did not know about called a fuzzylookup
        
        which is some sort of downloadable addon to excel. it apparently can look up similar text strings. something could probably be built with that. i, however, due to my shitty work laptop (on which i can't even change the time - and it's wrong) can't download it to have a play with it. i really want to
        
        cat_race @TheWza | 11 Mar '13, 16:33 | X | ^ This | Reply
        
        sounds interesting. cheers for looking.
        
        but no chance of installing an add-on. my works pc is more locked down than a very locked down thing.
        
        TheWza @cat_race | 11 Mar '13, 23:28 | X | ^ This | Reply
- Ok, imagine one set are in column A and one in column B
  
  in column C row 1 put this, and then drag and drop:
  
  =LEFT(A1,FIND(" ",A1)-1)
  
  It should return "Bedwetter"
  
  In column D (drag and drop):
  
  =LEFT(B1,FIND(" ",B1)-1)
  
  Finally in column E (drag and drop):
  
  =IFERROR(LOOKUP(C1,D:D,B:B),"")
  
  If there is a match the value in column E should be the value from Column B. If there is no match it will be blank.
  
  Hopefully that is what you were after :-)
  
  Phil9064 @TheWza | 11 Mar '13, 19:45 | X | ^ This | Reply
  - i'll have a play with ^this tomorrow.
    
    thanks.
    
    TheWza @Phil9064 | 11 Mar '13, 23:30 | X | ^ This | Reply
Yes on XC2003

I want to turn one set of words into another. BAsically to code them and make a document super secret.
Tell me how.
Go on
TELL ME

inside-outside | 11 Mar '13, 16:47 | X | ^ This | Reply
- are you making a joke?
  
  cat_race @inside-outside | 11 Mar '13, 16:50 | X | ^ This | Reply
  - no
    
    I've made a spreadsheet that scores every woman in the business, and its horrible, like really low down dirty shut, so I want to be able to code it so only I can reveal the true text. But I just can't do it. I can convert everything into Thai currency but not into anything useful
    
    inside-outside @cat_race | 11 Mar '13, 16:52 | X | ^ This | Reply
    - sample formula:
      
      =((((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)+(IF(H7="yes",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)))+((IF(I7="yes",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)))-IF(H7="Ugh",(((SUM(C7:F7)/4)+(SUM(C7:F7)*G7))/5)*0.05)
      
      inside-outside @inside-outside | 11 Mar '13, 16:53 | X | ^ This | Reply
    - ahhaha
      
      cat_race @inside-outside | 11 Mar '13, 16:53 | X | ^ This | Reply
      - =(ahhaha)?
        
        this doesnt work
        
        inside-outside @cat_race | 12 Mar '13, 12:59 | X | ^ This | Reply
        
        come on dude
        
        inside-outside @inside-outside | 15 Mar '13, 13:27 | X | ^ This | Reply
        
        this thread has not delivered
        
        NOT DELIVERED
        
        inside-outside @inside-outside | 19 Mar '13, 13:49 | X | ^ This | Reply
        
        wtf
        
        cat_race @inside-outside | 19 Mar '13, 13:50 | X | ^ This | Reply
        
        well where;s my code help
        
        inside-outside @cat_race | 19 Mar '13, 14:01 | X | ^ This | Reply
I think this could start a row

TheTupperwareMuseum | 11 Mar '13, 23:30 | X | ^ This | Reply
- *column
  
  japes @TheTupperwareMuseum | 11 Mar '13, 23:42 | X | ^ This | Reply
Ho do I stop this stupid cunting piece of shit from

converting "2 / 2" (for example) into "2 Feb". Note: I try formatting the cell differently (general, number, text, or whatever all the options are) and it still does it.

Did I ask the fucking thing to do anything to the text I put in the cells? No, I fucking did not!

robluvsnic | 12 Mar '13, 09:20 | X | ^ This | Reply
- put an apostrophe at the start
  
  Balonz @robluvsnic | 12 Mar '13, 09:25 | X | ^ This | Reply
  - What if I say "no"?
    
    What if I say, "fuck you, you dumb arse piece of shit. Stop being so fucking counter-intuitive and just work the way a normal intelligent person would expect you to work!"
    
    What then, hey?
    
    robluvsnic @Balonz | 12 Mar '13, 09:31 | X | ^ This | Reply
    - then it will put 2 Feb in for you
      
      Balonz @robluvsnic | 12 Mar '13, 09:32 | X | ^ This | Reply
      - What if I take a screwdriver out of my desk drawer
        
        and drive the fucker right through the middle of the offending cell and then piss on its smoking ruins?
        
        robluvsnic @Balonz | 12 Mar '13, 09:36 | X | ^ This | Reply
        
        You will have a broken monitor and within the workings of your computer the cell will contain
        
        2 Feb
        
        Balonz @robluvsnic | 12 Mar '13, 09:38 | X | ^ This | Reply
        
        Ha! That's where your wrong!
        
        I have a iMac and the hard drive, etc, is behind the screen. There's no way that cell will be stupidly showing "2 Feb".
        
        robluvsnic @Balonz | 12 Mar '13, 09:43 | X | ^ This | Reply
        
        *you're wrong
        
        Goddamn fucking fuck.
        
        robluvsnic @robluvsnic | 12 Mar '13, 09:45 | X | ^ This | Reply
- You'd need to set the cell format on the column to text before entering anything
  
  It's too late after it's converted it to a date as it's changed the actual number in the cell by then to 41307.
  
  The_Cosh @robluvsnic | 15 Mar '13, 13:54 | X | ^ This | Reply
  - Nah, I delete what I've entered
    
    then try to set the cell format, and it still converts. I only try setting the format for the cell, though, not for the entire column.
    
    robluvsnic @The_Cosh | 15 Mar '13, 15:05 | X | ^ This | Reply
Cat_race

i have a column with about 30 of the following data:

4841 Jones Keith
14065 Crawford James
14067 Ewart Peter

The numbers to the left are ID numbers that can be anywhere from 3 to 5 digits long. I want a formula (I guess 'MID' would be the base of it) that will pick out the name only with no space/figure to the left.

Jordan_229_2 | 15 Mar '13, 10:56 | X | ^ This | Reply
- my brain hurts today, so here is an AWFUL way to do it
  
  in the next column, use this
  
  =SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"1",""),"2",""), "3",""),"4",""),"5",""), "6",""),"7",""),"8","")
  
  in the column after that use this
  
  =SUBSTITUTE( SUBSTITUTE(B1,"9",""),"0","")
  
  you might end up with some spaces. you can use =TRIM() to tidy that up
  
  cat_race @Jordan_229_2 | 15 Mar '13, 11:21 | X | ^ This | Reply
  - SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A1,"Me","Him"),"Coke","Gin"), "You","my mum")
    
    Shinjers @cat_race | 15 Mar '13, 13:32 | X | ^ This | Reply
- if there is always a space after the numbers and it's the first space, this should do it:
  
  =RIGHT(A1,LEN(A1)-FIND(" ",A1))
  
  pieces_of_reece @Jordan_229_2 | 15 Mar '13, 13:13 | X | ^ This | Reply
  - that is nice
    
    cat_race @pieces_of_reece | 15 Mar '13, 13:18 | X | ^ This | Reply
  - beautiful
    
    now to switch the two names around?
    
    Jordan_229_2 @pieces_of_reece | 15 Mar '13, 13:56 | X | ^ This | Reply
    - =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1, FIND(" ",A1))),FIND(" ",A1, FIND(" ",A1)))&" "&RIGHT(A1,LEN(A1)-FIND(" ",A1, FIND(" ",A1)+1))
      
      pieces_of_reece @Jordan_229_2 | 15 Mar '13, 14:06 | X | ^ This | Reply
      - almost there...
        
        Jordan_229_2 @pieces_of_reece | 15 Mar '13, 14:07 | X | ^ This | Reply
      - wait it doesn't work
        
        Jordan_229_2 @pieces_of_reece | 15 Mar '13, 14:17 | X | ^ This | Reply
        
        ooops i put it together the rwong way round
        
        =RIGHT(A2,LEN(A2)-FIND(" ",A2, FIND(" ",A2)+1))&" "&LEFT(RIGHT(A2,LEN(A2)-FIND(" ",A2, FIND(" ",A2))),FIND(" ",A2, FIND(" ",A2)))
        
        pieces_of_reece @Jordan_229_2 | 15 Mar '13, 15:00 | X | ^ This | Reply
- Only 30?
  
  Just go through and pick them out manually, you lazy fuck!
  
  robluvsnic @Jordan_229_2 | 15 Mar '13, 14:16 | X | ^ This | Reply
  - or save it in a text file and import it using space delimited
    
    pieces_of_reece @robluvsnic | 15 Mar '13, 15:01 | X | ^ This | Reply
    - text to columns
      
      Matt_was_taken @pieces_of_reece | 15 Mar '13, 15:07 | X | ^ This | Reply
      - yeah, or that
        
        i always forget about that, that would have saved at least half of this thread happening!
        
        pieces_of_reece @Matt_was_taken | 15 Mar '13, 15:11 | X | ^ This | Reply
Dont want to step on anyone's toes

but I'd normally split this sort of thing into a couple of columns: makes it easier to identify errors and stops you unnecessarily repeating FIND calls. This also allows for the inevitable situations when there are more or fewer spaces than you expect.

Assuming your data starts in A1, then:
B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
- Get the number of spaces in A1; use this as a control to prevent errors in later finds (not included)
C1: =FIND(" ",A1)
- Get position of first space.
D1: =FIND("|",SUBSTITUTE(A1," ","|",B1))
- Get position of last space.
E1: =RIGHT(A1,LEN(A1)-D1)&MID(A1,C1,D1-C1)
- Take all characters following the last space then concatenate all characters between the first and last spaces.

The_Cosh | 15 Mar '13, 14:11 | X | ^ This | Reply
- alright billy five columns
  
  Jordan_229_2 @The_Cosh | 15 Mar '13, 14:14 | X | ^ This | Reply
  
  pieces_of_reece and moker this'd this
  - You can fit most things into one formula
    
    but it's harder to debug and more ineffecient as, in something like this, you'd be making the same call repeatedly. Particularly if you want to wrap the FINDs in an ISERROR or something to stop everything fucking up when there aren't enough spaces.
    
    The_Cosh @Jordan_229_2 | 15 Mar '13, 14:20 | X | ^ This | Reply
here goes hope you follow.

Ive got column N, which has data formatted hh:mm ddd.
In column M I need N minus 30 mins.
I have this formula =TEXT((LEFT(N2,5)-TIME(0,30,0)),"hh:mm") & " " & RIGHT(N2,3) which works well until there is a negative time value.
eg if N2 = '00:01 Sat' M2 should equal '11:31 Fri'
any takers?
I've found this bit which will do the time bit
=MOD(N2-TIME(0,30,0),1), but how about going from Sat to Fri?

Matt_was_taken | 19 Mar '13, 13:25 | X | ^ This | Reply
- Correction - eg if N2 = '00:01 Sat' M2 should equal '23:31 Fri'
  
  Matt_was_taken @Matt_was_taken | 19 Mar '13, 13:26 | X | ^ This | Reply
  - aw not today, i think i'm going to be sick
    
    cat_race @Matt_was_taken | 19 Mar '13, 13:49 | X | ^ This | Reply
- Depends what you mean about the data being formatted a certain way
  
  Based on the formulae you're using I assume the data is being entered manually in that format. If so, the sat/fri part is meaningless in terms of trying to manipulate it normally. You could come up with a convoluted IF formula but a slightly better way is to use a basic lookup table.
  
  On a separate worksheet to the one with the data on it, enter Sun, Mon, Tue, Wed, Thu, Fri, Sat, Sun into cells A1:A8. The repetition of Sun is important.
  
  Then you can use this to return the prior day if necessary (change the name of Sheet2 as required):
  =IF(LEFT(N2,5)/1<TIME(0,30,0),INDEX(Sheet2!$A$1:$A$7,MATCH(RIGHT(N2,3),Sheet2!$A$2:$A$8,0)),RIGHT(N2,3))
  
  The alternate way of doing it would be if you can change the data in column N so you're actually entering the full date and time, e.g. 20/03/13 10:50. You can then set the format of the cell/column to display as hh:mm ddd and the only calculation you need in column M is:
  =N2-TIME(0,30,0)
  
  The_Cosh @Matt_was_taken | 20 Mar '13, 12:00 | X | ^ This | Reply
  - thanks for helping man
    
    I came up with your lookup solution yesterday afternoon.
    dunno which berk thought of the format but its a right pain, but the data is imported that way from a csv file
    
    Matt_was_taken @The_Cosh | 20 Mar '13, 12:07 | X | ^ This | Reply
how do i be good at it like nerds?

srs.

chickenbones | 19 Mar '13, 13:47 | X | ^ This | Reply
- use it and keep using it
  
  cat_race @chickenbones | 19 Mar '13, 13:48 | X | ^ This | Reply
  - i do but is simple things
    
    want get good c_r.
    
    chickenbones @cat_race | 19 Mar '13, 13:54 | X | ^ This | Reply

Reply to this thread

View NestedLinear

« Back to Social