Ask Geoff About Excel (or Google Sheets)

Let's talk Aguachile Alley

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: 40363
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: 29158
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: 62
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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?
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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!
User avatar

doublethink0
 
Posts: 3293
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
Joined: Sat Dec 26, 2009 8:33 am

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

User avatar

Geoff
i like jazz
 
Posts: 12594
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: 12594
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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
Joined: Sat Dec 26, 2009 8:33 am

Postby Pokemon Blue » 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 Blue
 
Posts: 4612
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: 12594
Joined: Sat Dec 26, 2009 8:33 am

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

Excellent, thank you!
User avatar

Pokemon Blue
 
Posts: 4612
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: 12594
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?
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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: 12594
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: 12594
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
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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

could you upload with that date
User avatar

Geoff
i like jazz
 
Posts: 12594
Joined: Sat Dec 26, 2009 8:33 am

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

User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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

if you strip out the first bit
and just start with the index it works, but will throw up false positives in case were the start date exceeds any of those that are in the mapping
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))
User avatar

Geoff
i like jazz
 
Posts: 12594
Joined: Sat Dec 26, 2009 8:33 am

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

ok great ill test that out. i would really like a method of detecting times when the date being checked actually falls outside of any of the acceptable ranges, though. in some ways that's the most important part, because it means there's errors in my table that need to be addressed.
User avatar

farmer
 
Posts: 17777
Joined: Mon Dec 14, 2009 6:42 pm
Location: screaming inside my heart

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

of course, i need to fix it that the blank initial bit was supposed to blank it out if it was outside any defined range, but it didn't work properly i need to rethink to solve it.
User avatar

Geoff
i like jazz
 
Posts: 12594
Joined: Sat Dec 26, 2009 8:33 am

PreviousNext

Return to Aguachile Alley

Who is online

Users browsing this forum: ahungbunny, Albatross, brittle, chewy, Christmas Ape, Eyeball Kid, Giuseppe Castiglione, Google [Bot], hbb, jalapeño ranch, jubjub, Littlelulusfanclub, mcwop23, murderhorn, Rainbow Battle Kid, sunrise, trampoline and 221 guests