Ask Geoff About Excel (or Google Sheets)

Health insurance rip off lying FDA big bankers buying
Fake computer crashes dining
Cloning while they're multiplying
Fashion shoots with Beck and Hanson
Courtney Love, and Marilyn Manson
You're all fakes
Run to your mansions
Come around
We'll kick your ass in

Postby Geoff » Thu Jul 12, 2018 4:47 am

I can upload a template, but need to understand which format your call times are currently stored as to make it
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby kid8 » Thu Jul 12, 2018 4:47 am

You do all the work.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby Geoff » Thu Jul 12, 2018 4:49 am

I will do all the work, but to be able to do the work I need to know what format the data is in. Thank you kindly.
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby kid8 » Thu Jul 12, 2018 4:50 am

Look, geoff... I want to be like this call lasted 5 minutes *enter into cell a* and 10 seconds * enter into cell b* and keep doing that all day and then in cell fucking j it tells me my average. There
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby Geoff » Thu Jul 12, 2018 4:51 am

kid8 wrote:Look, geoff... I want to be like this call lasted 5 minutes *enter into cell a* and 10 seconds * enter into cell b* and keep doing that all day and then in cell fucking j it tells me my average. There


Ok got it
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Thu Jul 12, 2018 4:55 am

User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby kid8 » Thu Jul 12, 2018 4:57 am

Is that a spam link?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 4:58 am

Am I gonna go to court for copyright infringement?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 4:58 am

Is it a porn freak?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 4:59 am

Thank you.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:00 am

I hope it fucken works n shit.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:01 am

Geoff, what do you think of me?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:02 am

Be completely honest.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:03 am

You're smart.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby Geoff » Thu Jul 12, 2018 5:06 am

you're a good guy kid8, keep on keeping on!
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby kid8 » Thu Jul 12, 2018 5:08 am

Thank you . Are you always smart?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby Geoff » Thu Jul 12, 2018 5:09 am

depends if the spreadsheet works for you.
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby kid8 » Thu Jul 12, 2018 5:10 am

Omg, i'm involved.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:11 am

Thank you. I'll let you know if it works.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:12 am

But, I'm talking like, worst comes to worst... can you build freeways?
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby identikit » Thu Jul 12, 2018 5:15 am

Geoff wrote:Please feel free to contact me in this thread if you have any excel based questions.

I will help as much as possible. Probably not the most interesting thread, but very functional.


signing up as your daftest pupil yet! (I will read the entire thread eventually, thank you very much for this!)

- purpose: glossary, just 2 languages and some additional rows with references to legal texts
- issue: sorting. some entries don't start with the word that should be sorted, but with an addtion in brackets

[blabla] Federal Act on blaaa


how do I get it to ignore what comes in brackets and start sorting per first letter after the brackets (i.g. here it gets sorted at the beginning but should go under F - federal)
I am guessing I can toy in the sorting function under "add level" (or whatever that says in English) but uuuuuuh
User avatar
identikit
 
Posts: 4003
Joined: Sat Jul 16, 2016 12:38 pm

Postby kid8 » Thu Jul 12, 2018 5:18 am

You're so easy on him.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby kid8 » Thu Jul 12, 2018 5:19 am

He's a mess.
Steve was the only man i loved.
User avatar
kid8
killer
 
Posts: 43959
Joined: Mon Dec 14, 2009 5:08 pm
Location: Normal Heights.

Postby identikit » Thu Jul 12, 2018 5:20 am

don't you dare talk to bebop werewolf and world conqueror Geoff like this. sit down, kid8.
User avatar
identikit
 
Posts: 4003
Joined: Sat Jul 16, 2016 12:38 pm

Postby Geoff » Thu Jul 12, 2018 5:22 am

identikit wrote:
Geoff wrote:Please feel free to contact me in this thread if you have any excel based questions.

I will help as much as possible. Probably not the most interesting thread, but very functional.


signing up as your daftest pupil yet! (I will read the entire thread eventually, thank you very much for this!)

- purpose: glossary, just 2 languages and some additional rows with references to legal texts
- issue: sorting. some entries don't start with the word that should be sorted, but with an addtion in brackets

[blabla] Federal Act on blaaa


how do I get it to ignore what comes in brackets and start sorting per first letter after the brackets (i.g. here it gets sorted at the beginning but should go under F - federal)
I am guessing I can toy in the sorting function under "add level" (or whatever that says in English) but uuuuuuh


are you using excel or google sheets indentikit?
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby identikit » Thu Jul 12, 2018 5:22 am

Excel, full version. so I have (sorry I use it in German) - sort and filter - customised sorting - add level - sort by - and here I think I should be able to add something. i.g. "ignore [...]" or something like that.

so far I have been sorting the entire glossary and then take all the entries at the beginning with text in brackets and paste it in the right spots per hand. that can't be the most effective way to do it...

(I have all summer to get my excel-shit together btw, if you ever think of something, I'd be more than happy.)
User avatar
identikit
 
Posts: 4003
Joined: Sat Jul 16, 2016 12:38 pm

Postby Geoff » Thu Jul 12, 2018 5:37 am

I can do it all in formulas.

So you don't really have to do anything except copy down a formula or 2, just be aware the alphabetically sorting formula is an array formula so you need to control shift enter the formula to make it work originally.

But basically you just need a helper column that strips the bracketed text [blab], and then from there sort (manually) or through my formula.

Formula to extract out the [] data is =TRIM(RIGHT(A2,LEN(A2)-FIND("]",A2,1))), and then sorting this column (with set range B2:B100, the helper stripped column) would be ={IFERROR(INDEX($B$2:$B$100,
MATCH(0,COUNTIF($B$2:$B$100,"<"&$B$2:$B$100)-
SUM(COUNTIF($B$2:$B$100,"="&B$1:B1)),0)),"")}

Remember this sorting function needs to be in an array and needs to be entered Ctrl+Shift+Enter.

Here is the template you can use.

http://s000.tinyupload.com/index.php?fi ... 9246262634
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby identikit » Thu Jul 12, 2018 5:38 am

woa. gonna make a test-copy and toy with this, thank you so much!

christ. the command is GLÄTTEN; GLÄTTEN, fuck me.

I love you Geoff. thank you!

ruined it twice, third time's a charme, all Federal Acts are indeed under F! mind.blown. hell yes!
Last edited by identikit on Thu Jul 12, 2018 5:51 am, edited 2 times in total.
User avatar
identikit
 
Posts: 4003
Joined: Sat Jul 16, 2016 12:38 pm

Postby Geoff » Thu Jul 12, 2018 5:39 am

identikit wrote:woa. gonna make a test-copy and toy with this, thank you so much!


just remember to extend the array range to fit your data length.
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Thu Jul 12, 2018 5:46 am

identikit wrote:woa. gonna make a test-copy and toy with this, thank you so much!

christ. the command is GLÄTTEN; GLÄTTEN, $%%^$ me.

I love you Geoff. thank you!


What do you mean by the Glatten bit, I'm not sure if my thing works with accents, let me know if it does.
User avatar
Geoff
i like jazz
 
Posts: 5878
Joined: Sat Dec 26, 2009 8:33 am

PreviousNext

Return to Mamma Mia... Here We Go Again....

Who is online

Users browsing this forum: Adult Contemporary, alaska, all day breakfast, anotherdave, banquo, basenjis chortle, blab, blendercore, blinky, bluemovers, brittle, bro tones, carrion, Celiac Cruz, churrokbyme, Clive, cooly, coop, crisp butty, davideotape, deadbass, deep blue meanies, dimetrodons 'r' us, evan, Eyeball Kid, Ezekiel Cletus, Ferrous Bueller, flimsy, fresh salad, galactagogue, gambra, Gehrig Industries, ghostmouth, goldmatt, GonzO))), Google [Bot], Google Adsense [Bot], griffith scannell, guidance, hit record, hologram, hopped, Hugh, incoherent grunting, inmate, Jake SPEED, jalapeño ranch, jewels, Jimmy Firecracker, jon, Kevin McCallister, kirito, light rail coyote, lights, Marcus, Martin Lawrence Olivier, mcwop23, mystery meat, naturemorte, No Good Advice, nosebleeds, number none, odilon redon, Oh! Sweet Nuthin', palmer eldritch, Paul, pissydan, powderfinger, screaming emphysema, shankly, shirts optional, Shotfrog, slime, speakers, Subbacultcha, tawny frogmouth, techno beats, tgk, the upland trout, toots, trampoline, transitive, trouble, uncledoj, various artists, VHGisdead, wario lopez, wildarms, wuk, yungboi, Zardoz