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 farmer » Fri Aug 03, 2018 11:49 am

jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby banquo » Sat Aug 04, 2018 10:29 am

Geoff wrote:never mind I prepared myself

http://s000.tinyupload.com/?file_id=017 ... 7615720818

Just type into D4, it will insert the date and insert a row above, I think this is what you were after right?


just wanted to update that this has auto filled 800+ dates/times and also made that many new rows for me so far. thanks!
User avatar
banquo
 
Posts: 3440
Joined: Tue Dec 15, 2009 8:28 pm

Postby Geoff » Sat Aug 04, 2018 5:36 pm

Woo, jewels would love to help
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby jewels » Sun Aug 05, 2018 10:46 pm

farmer wrote:jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.


I have text in 8 different text boxes, they are over a single jpg map. I want to shrink everything uniformly so the font size changes, the map size changes and everything remains proportionally the same, just 80% smaller.

When I try this, nothing goes right or how I expect it to.

I haven't used this dumb program in a decade and at my new job it seems like I'm going to be fixing up people's presentations and making them prettier.
gold and glass wrote:When you get to heaven, do you get to see a list of which gimmicks belonged to who?
User avatar
jewels
 
Posts: 21584
Joined: Mon Dec 14, 2009 3:33 pm

Postby The Dirty Turtle » Sun Aug 05, 2018 10:59 pm

just be lazy and screencap it and turn the whole thing into an image
User avatar
The Dirty Turtle
ok
 
Posts: 39037
Joined: Tue Dec 15, 2009 4:04 am
Location: Cannot be found

Postby jewels » Mon Aug 06, 2018 8:05 am

The Dirty Turtle wrote:just be lazy and screencap it and turn the whole thing into an image


that's what I did, but I don't know if that's a sustainable solution.
gold and glass wrote:When you get to heaven, do you get to see a list of which gimmicks belonged to who?
User avatar
jewels
 
Posts: 21584
Joined: Mon Dec 14, 2009 3:33 pm

Postby Gooey Bechamel » Mon Aug 06, 2018 11:28 am

farmer wrote:Oh another big one: is there a way to run 2 nearly identical spreadsheets or docs through a program to highlight where they differ?


I'm not sure if you're still looking for a way to do this but Spreadsheet Compare 2016 is a separate program included in our Office suite at work that I've used for that purpose.
User avatar
Gooey Bechamel
 
Posts: 57
Joined: Thu Jul 13, 2017 12:14 pm
Location: deep darkness

Postby farmer » Thu Aug 09, 2018 11:58 am

jewels wrote:
farmer wrote:jewels, what are you trying to accomplish? or an example. your wording in that question is a bit vague.


I have text in 8 different text boxes, they are over a single jpg map. I want to shrink everything uniformly so the font size changes, the map size changes and everything remains proportionally the same, just 80% smaller.

When I try this, nothing goes right or how I expect it to.

I haven't used this dumb program in a decade and at my new job it seems like I'm going to be fixing up people's presentations and making them prettier.


yeah, presentational formatting can be tedious. are they presenting excel things? you can also build tables, etc in powerpoint.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Thu Aug 09, 2018 12:01 pm

ive got a challenge i need some help with. basically, i've got a bunch of data. for each row of data, i need to use the exact date in one column and a name in a different column, and use them to cross reference against a different list of names and dates. but the dates don't matchup exactly-- the second set of data that i need to pull from is a date range-- the beginning in one column, the end in another column-- all subdivided by these names. so normally what i would do to solve a match-things-in-two-different columns problem is to concatenate them together and then just indexmatch or vlookup based on that concatenation. but because the second set of data involves date ranges instead of exact matching, my standard method won't work. ideas?
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby doublethink0 » Thu Aug 09, 2018 12:43 pm

farmer wrote:ive got a challenge i need some help with. basically, i've got a bunch of data. for each row of data, i need to use the exact date in one column and a name in a different column, and use them to cross reference against a different list of names and dates. but the dates don't matchup exactly-- the second set of data that i need to pull from is a date range-- the beginning in one column, the end in another column-- all subdivided by these names. so normally what i would do to solve a match-things-in-two-different columns problem is to concatenate them together and then just indexmatch or vlookup based on that concatenation. but because the second set of data involves date ranges instead of exact matching, my standard method won't work. ideas?


this is a classic data challenge (see Slowly Changing Dimension), but there's gotta be a way of doing this with a date range lookup up like: https://www.extendoffice.com/documents/excel/2702-excel-vlookup-between-date-range.html

i literally just googled "excel date range lookup" and maybe some of the gurus here (or yourself) could figure it out pretty quick, i know i could do it in SQL. good luck!
Image
User avatar
doublethink0
 
Posts: 1715
Joined: Sat Sep 05, 2015 3:17 pm
Location: CLE

Postby farmer » Fri Aug 10, 2018 10:57 am

thanks for looking into that, doublethink.

unfortunately, it seems like its still only doing a vlookup to find the place it fits in the date ranges. this is something i can already do in a couple different ways, the problem is that it relies on a consecutive, non-overlapping date range. but i have to also find the name that matches, because there's massive amounts of overlap of date ranges across the different names in this huge list.

the only thing i've been able to think of is a completely separate table that looks up the start and end rows for each name, and then add a step in the formula that checks what the range is based on the name, and then checks date ranges based on the provided row range with an indirect.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 12:10 pm

hey farmer, can you send me some dummy data for to me to work off for me to find a solution?
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 12:26 pm

so to demonstrate a lil more, basically i have data in two tabs, something like this:

Image

and i wanna add columns to that second table, where based on the date in that second table, each row of those two columns looks up the begin and end values that correspond to that date. generally the dates in table one span a year and are consecutive, but not always.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 12:40 pm

i have a solution if you can send me an excel version of that table, I'll add the formula
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 12:52 pm

oh sick, wanna just paste the formula in here? i'll have to make a bunch of adjustments to it anyway since i massively oversimplified the tables down to the salient details.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 1:23 pm

For Start Date:

=IF((A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2)=0,"",INDEX(B:B,AGGREGATE(15,6,ROW($1:$11)/(A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2),1)))

For End Date:

=IF((A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2)=0,"",INDEX(C:C,AGGREGATE(15,6,ROW($1:$11)/(A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2),1)))

Update ranges
Col A Name Table 1
Col B Begin Date Table 1
Col C End Date Table 1
Col E Name Table 2
Col F Date Table 2
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Fri Aug 10, 2018 1:25 pm

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

Postby Geoff » Fri Aug 10, 2018 1:27 pm

let me know if it is what you are after :)
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 2:22 pm

thanks for going to all this work!!

unfortunately, it's not yet working. does it change things if the two tables are in two different tabs? i mean, i know how to add tab references and all. but in trying to diagnose by parts, just the first part "A$1:A$11=E2" throws up a huge number of FALSEs where it shouldn't, and then after the row count of the "E2" slot exceeds the row count of table one, it just throws up #VALUE after that.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 2:31 pm

You need to extend the range try extending to the max limit of your database (both tables) eg maybe 100000 rows or something like that
Last edited by Geoff on Fri Aug 10, 2018 2:41 pm, edited 1 time in total.
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby Pokemon Mastah » Fri Aug 10, 2018 2:38 pm

Hi Geoff

What resources (books/websites) do you recommend for someone trying to improve their Excel skills?

I recently started a new job which requires extensive use of Excel - and a bit more advanced than my current skills which extends about as far as vlookups and pivot tables
User avatar
Pokemon Mastah
 
Posts: 3683
Joined: Thu Dec 17, 2009 2:02 pm

Postby Geoff » Fri Aug 10, 2018 2:41 pm

this has everything you need https://chandoo.org/
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby Pokemon Mastah » Fri Aug 10, 2018 2:43 pm

Excellent, thank you!
User avatar
Pokemon Mastah
 
Posts: 3683
Joined: Thu Dec 17, 2009 2:02 pm

Postby Geoff » Fri Aug 10, 2018 2:48 pm

farmer wrote:thanks for going to all this work!!

unfortunately, it's not yet working. does it change things if the two tables are in two different tabs? i mean, i know how to add tab references and all. but in trying to diagnose by parts, just the first part "A$1:A$11=E2" throws up a huge number of FALSEs where it shouldn't, and then after the row count of the "E2" slot exceeds the row count of table one, it just throws up #VALUE after that.


I created a version that works over 2 sheets and extends the range try this one

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

Postby farmer » Fri Aug 10, 2018 2:52 pm

Geoff wrote:You need to extend the range try extending to the max limit of your database (both tables) eg maybe 100000 rows or something like that


so doing just a test of "A$1:A$11=E2", except for me extending it in this case to table1!$a$1:$a$1511=c3, and then even table1!$a:$a=c3 is still throwing up tons of falses.

perhaps this test isn't checking the whole column for the presence of C3, and instead is just checking table1!a3? and so on down the rows?
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 10, 2018 2:55 pm

so for your example sheets, if i change the sample data for the second oswald in table 2 to 9/12/17, the begin and end go blank, when what it should do is pop 10/1/16 for begin and 9/30/17 for end.
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 2:59 pm

can you upload the test sheet with the issue to tinyupload.com, so i can troubleshoot.
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby Geoff » Fri Aug 10, 2018 3:01 pm

farmer wrote:so for your example sheets, if i change the sample data for the second oswald in table 2 to 9/12/17, the begin and end go blank, when what it should do is pop 10/1/16 for begin and 9/30/17 for end.


maybe it's a date format issue, I use British dates, which is dd/mm/yy vs american which is mm/dd/yy.
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 3:04 pm

ah yeah, fuck, i could see how date formats could fuck with that. but still, if you change that second oswald to 9/6/17, it shouldn't have any british/american date format problems either direction for this example, and it still leaves begin and end blank when you do that
farmer
 
Posts: 10087
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 3:06 pm

could you upload with that date
User avatar
Geoff
i like jazz
 
Posts: 5988
Joined: Sat Dec 26, 2009 8:33 am

PreviousNext

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

Who is online

Users browsing this forum: antoine, aqxmission, ASH NERVE, Bad Craziness, Bartatua, bhramami, Birds vs Worms, blab, brentwurst, brittle, bruceagain, buboclot, building jumper, Celiac Cruz, chad, chowder julius, churrokbyme, Classic Water, clownwig, conductor, Cone, Darlene, deebster, def from above, Destroid, doublethink0, emotional fascism, Eyeball Kid, Ferrous Bueller, Flossed Out, funkfunkfunk, gershon, Google Feedfetcher, haddonfield, hbb, Hideaway Lights, hilbert, hired goon, jalapeño ranch, jarsilver, jon, Kiki, la croix, landspeedrecord, laserblast, lemon rind, light rail coyote, Lucky, mcwop23, mego, mynameisdan, naturemorte, nice pass, nite69, normal finkenstein, oh! it's max!, Oh! Sweet Nuthin', palmer eldritch, Paul, Peter Criss, pink snake, prexy, rex, ScaredGopher, shacky, Shotfrog, sigmonrunner, snuggle, Sobieski, southpaw, spencasaurus, super gas, surly, tea preacher, terminus, tgk, turquoise albino, viachicago, wakeman, wario lopez, warmjets, WeirdJungle, worrywort, Your Turret Has Been Destroyed