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 10, 2018 3:08 pm

farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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: 5678
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.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

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

ok found a fix, do you want it to show no match or blank for dates out of range of first table?
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

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

i mean i'd be fine with it throwing up YOUFUCKINGMORON because it means i need to fix things.

for context, table 1 is a list of insureds and the dates their policies are active, and table 2 is the current list of claims/losses, so if someone's made a claim outside of their policy period there's an error elsewhere in my work that needs fixin'.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

though it's more likely that the error was made by someone else that preceded me on this project and they just didn't have the wherewithal to try to diagnose their errors and here we are.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

=IF(SUMPRODUCT((A$1:A$11=E2)*(B$1:B$11<=F2)*(C$1:C$11>=F2))=0,"OUCH",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)))

New and improved formula :)

http://s000.tinyupload.com/?file_id=873 ... 4325751751

I just said OUCH (for no match), couldn't call you that thing you suggested
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

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

oh yeah i custom-insult myself all the time in error checking sheets, but it's prob not good practice in case i hand the sheet to someone else who doesnt know about my little jokes.

and thanks! lets see if this works.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

as an aside to learn more about excel through this, what exactly is "A$1:A$11=E2" actually doing? also, what's the aggregate function doing? never used it, but it seems like it could be useful.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

"A$1:A$11=E2" it's matching the name, in addition to the dates.
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

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

how does it differ from the match() function, except that its a boolean return rather than a row number return
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Fri Aug 10, 2018 4:43 pm

farmer wrote:how does it differ from the match() function, except that its a boolean return rather than a row number return


It's mostly for use in array of other checks and yes for this example needed to convert stuff to boolean output
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Fri Aug 10, 2018 4:47 pm

oh sick. okay yeah this is the sort of tips and tricks i need. lesser known functionality that i can combine with others to solve problems i encounter. basically my goal is to eventually get my sheets to where my own tendency toward error is near zero.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby farmer » Fri Aug 10, 2018 4:50 pm

here's a completely unrelated general question about external references (other workbooks that require a filepath as part of the formula if you don't also have it open).

without using vba (ie entirely in excel), is there a way to concatenate things together to call a list of filepaths. like, a particular project will have folders by year, and close to identical file structure within those folders, so i really don't need to even open the file to pull what i need, and i can manually adjust file paths in formulae to pull the numbers i need from a different workbook, but i can't figure out how to use some form of concatenation/indirect, etc to basically have a series of cells that get combined into the file path, that is then used automatically to go pull numbers from a workbook, one year forward. ie i just wanna change a 2017 to a 2018 and have all the numbers i need updated.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

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

farmer wrote:here's a completely unrelated general question about external references (other workbooks that require a filepath as part of the formula if you don't also have it open).

without using vba (ie entirely in excel), is there a way to concatenate things together to call a list of filepaths. like, a particular project will have folders by year, and close to identical file structure within those folders, so i really don't need to even open the file to pull what i need, and i can manually adjust file paths in formulae to pull the numbers i need from a different workbook, but i can't figure out how to use some form of concatenation/indirect, etc to basically have a series of cells that get combined into the file path, that is then used automatically to go pull numbers from a workbook, one year forward. ie i just wanna change a 2017 to a 2018 and have all the numbers i need updated.


excel is terrible for that, and to use an indirect formula to look at alternative workbooks you'd need to open them first for the indirect to work.
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Mon Aug 20, 2018 9:55 pm

oh shit geoff today i finally had a eureka moment on this problem, haven't had a chance yet to look at your most recent file.

basically, you use maxifs() and minifs() to return the start and end dates right before and after the date (for maxifs() for the date before, the latest date in the list thats still lower than the date being checked). you have another condition that checks if the names match.

i need to think to use the 'if' variants more often.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby Geoff » Wed Aug 22, 2018 8:12 am

farmer wrote:oh shit geoff today i finally had a eureka moment on this problem, haven't had a chance yet to look at your most recent file.

basically, you use maxifs() and minifs() to return the start and end dates right before and after the date (for maxifs() for the date before, the latest date in the list thats still lower than the date being checked). you have another condition that checks if the names match.

i need to think to use the 'if' variants more often.


ace, but do check out my final solution file.
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

Postby farmer » Wed Aug 22, 2018 1:24 pm

yeah i def will, just gotta finish up the projects on my plate right now first.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby dr. badvibes » Tue Sep 04, 2018 4:22 pm

i've been patiently waiting for my chance to use this thread and i finally have a question

is there a way i can take an excel file that's a long list of items, then compare it to a pre-existing list and delete/hide all the rows that don't match?

since i don't know how to phrase that clearly, i'll use an example

-- every week i get sent a list of item, alphabetized by the first column, which contains the entire alphabet A to Z
-- for my purposes i only ever need rows A, B, C, D, J, K, L, R, S, and W
-- can i someone shunt those needed rows into every update of the full list to isolate only the rows i want?
User avatar
dr. badvibes
 
Posts: 6346
Joined: Wed May 26, 2010 9:26 pm

Postby tomasu » Tue Sep 04, 2018 8:21 pm

there is a very simple non-excel way to do the first part
same boner, different day
User avatar
tomasu
 
Posts: 1443
Joined: Mon Dec 14, 2009 4:38 pm

Postby farmer » Tue Sep 04, 2018 8:28 pm

dr. badvibes wrote:i've been patiently waiting for my chance to use this thread and i finally have a question

is there a way i can take an excel file that's a long list of items, then compare it to a pre-existing list and delete/hide all the rows that don't match?

since i don't know how to phrase that clearly, i'll use an example

-- every week i get sent a list of item, alphabetized by the first column, which contains the entire alphabet A to Z
-- for my purposes i only ever need rows A, B, C, D, J, K, L, R, S, and W
-- can i someone shunt those needed rows into every update of the full list to isolate only the rows i want?


whats a rough ballpark on the number of rows in the two lists? does the new list have other information in other columns that you also want, and you just wanna pare it down to the important bits?
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby dr. badvibes » Tue Sep 04, 2018 8:48 pm

the list i receive from the 3rd party is about 225 rows/items, and i need to preserve about 180 of them

answering your question makes me believe i might have over-complicated the description, actually: i don't necessarily have two documents to integrate, rather, i was imagining that the easiest way to pare down the full list of ~225 would be to make a "key" document of my 180 that i can reuse to delete the same stuff from the received docs

maybe there's a simpler way that involves just deleting the same superfluous rows every time i need to?

and yes, i want to preserve all the columns, i just want to remove the rows i never need
User avatar
dr. badvibes
 
Posts: 6346
Joined: Wed May 26, 2010 9:26 pm

Postby farmer » Tue Sep 04, 2018 9:17 pm

off the top of my head, what i'd do if you just need some quick output:

add a sheet to the new list and paste the set of names you want to preserve in column a.

in the tab with the new list, add a column called whatever and put =if(isnumber(match(a2,Sheet1!$a:$a,0)),1,0) in row 2. then double click the bottom right corner of that formula cell when highlighted or grab that corner and drag to the bottom or whatever your solution of choice to filling out a column with a formula is. this assumes your column titles are in row 1 and your name list is in column a and the new tab you put the reference list in is called Sheet1. adjust as necessary.

then highlight the whole table of new data and filter on 1 on your new column. then highlight all the remaining data and paste it into a new sheet and delete your new filter column. data cleaned.

if you are doing this a ton, i would do this a different way, but if it's relatively rare, that's the quick and dirty solution i'd go with.
farmer
 
Posts: 9942
Joined: Mon Dec 14, 2009 6:42 pm

Postby dr. badvibes » Tue Sep 04, 2018 9:28 pm

unfortunately i have to do it three times a week :?

always the same list of 225, and i'm always looking for the same 180
User avatar
dr. badvibes
 
Posts: 6346
Joined: Wed May 26, 2010 9:26 pm

Postby Geoff » Wed Sep 05, 2018 12:50 am

Just landed from a 12 hour flight, I'll have a solution to you as soon as I get over my jetlag
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

Postby Jsn » Sat Sep 15, 2018 5:16 pm

dr. badvibes wrote:i've been patiently waiting for my chance to use this thread and i finally have a question

is there a way i can take an excel file that's a long list of items, then compare it to a pre-existing list and delete/hide all the rows that don't match?

since i don't know how to phrase that clearly, i'll use an example

-- every week i get sent a list of item, alphabetized by the first column, which contains the entire alphabet A to Z
-- for my purposes i only ever need rows A, B, C, D, J, K, L, R, S, and W
-- can i someone shunt those needed rows into every update of the full list to isolate only the rows i want?



Array Formula
Or
Advanced Filter (Macro perhaps)
Or
Filter (w/helper column)

Imo
User avatar
Jsn
 
Posts: 462
Joined: Thu Dec 07, 2017 11:15 pm
Location: Sherman Oaks

Postby banquo » Sun Sep 16, 2018 11:35 am

it sounds like something Knime would be able to do very well - but i'm not skilled enough in it to give the full solution aside from a basic "remove rows and spit out a new xls" - pretty sure it'd be possible to make it do exactly what you want in one click where it saves you a new xls formatted exactly like you've been manually doing each week

curious how Geoff would solve it in excel though
User avatar
banquo
 
Posts: 3316
Joined: Tue Dec 15, 2009 8:28 pm

Postby banquo » Mon Sep 17, 2018 1:53 pm

Google sheets question:

I want to have a trigger on edit, but only when a certain cell is edited. I was able to record a macro and trigger the macro on any edit, but it's not quite ideal since i'm trying to remake a "date this and add a new row if cell c3 gets updated" - except for google sheets instead of excel. The solution in excel before was using visual basic, and I don't think it carries directly over.

Is there a simple way to make an IF statement in the script? Do i use "isblank"? I feel like I'm only a couple lines away from a correct macro script but I keep not getting it correct.
User avatar
banquo
 
Posts: 3316
Joined: Tue Dec 15, 2009 8:28 pm

Postby Geoff » Thu Sep 20, 2018 6:06 am

banquo wrote:it sounds like something Knime would be able to do very well - but i'm not skilled enough in it to give the full solution aside from a basic "remove rows and spit out a new xls" - pretty sure it'd be possible to make it do exactly what you want in one click where it saves you a new xls formatted exactly like you've been manually doing each week

curious how Geoff would solve it in excel though


yes knime would be the best solution.

Otherwise I'd use a helper column, which uses the ROW() formula to return the rows that are required (shows blank if row is not required)

Then use the following formula dragged down and accross that will bring data in a new sheet.

=IFERROR(INDEX(Sheet2!B:B,MATCH(SMALL(Sheet2!$A:$A,ROW(1:1)),Sheet2!$A:$A,0)),"")

Where B:B is the start of the data required, and A:A is where the matched ROW() helper column is.
User avatar
Geoff
i like jazz
 
Posts: 5678
Joined: Sat Dec 26, 2009 8:33 am

PreviousNext

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

Who is online

Users browsing this forum: Birds vs Worms, blinky, bloodlines, Enemy Ace, Google [Bot], Google Adsense [Bot], Hoxha, jimmy fallon, Kaputt, Lucky, mascotte, Melville, MikeS, multipass, Parson Floogle, Prof. Horatio Hufnagel, qaanaaq, trampoline, zach york