Excel

Forum for posts that are not hockey-related.

Moderators: Three Stars, dagny, pfim, netwolf

Excel

Postby Idoit40fans on Thu Nov 08, 2012 4:53 pm

I have a list of about 900 accounts that need to be divided up amongst myself and 10 other people. The excel document has them listed along with the zip code they are listed in. Each of us has like 30 or so zips that we cover. Is there a code I can write that will look at the zip code column and then return someone's name based on which zip code the account is under? I really know it can be done, not sure how though. It will take a long time to sort through who these need to be assigned to. This is gonna happen like 5 more times in the near future so being able to run a code would be very helpful. Does anyone know the base format for doing this?
Idoit40fans
NHL Third Liner
NHL Third Liner
 
Posts: 53,195
Joined: Sat Dec 09, 2006 2:42 pm
Location: No Reading, No Research, Just Strong Opinions

Re: Excel

Postby thehockeyguru on Thu Nov 08, 2012 4:55 pm

Idoit40fans wrote:I have a list of about 900 accounts that need to be divided up amongst myself and 10 other people. The excel document has them listed along with the zip code they are listed in. Is there a code I can write that will look at the zip code column and then return someone's name based on which zip code the account is under? I really know it can be done, not sure how though. It will take a long time to sort through who these need to be assigned to.


V-Lookup
thehockeyguru
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 11,277
Joined: Wed Apr 30, 2008 3:43 pm
Location: I'm 30 minutes away, I'll be there in 10.

Re: Excel

Postby AlexPKeaton on Thu Nov 08, 2012 4:58 pm

Export to .csv then import it into Python haha.
AlexPKeaton
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 12,708
Joined: Thu Jul 26, 2007 9:46 am
Location: Malkinite Compound

Re: Excel

Postby stinky on Thu Nov 08, 2012 5:06 pm

VLOOKUP would be correct and learn to use it and its tricks, it is in the top 5 of tools in my world.
stinky
Junior 'A'
Junior 'A'
 
Posts: 180
Joined: Tue Dec 15, 2009 1:49 pm

Re: Excel

Postby mac5155 on Thu Nov 08, 2012 5:29 pm

yep VLOOKUP. my world was changed forever once I discovered how to use it.
mac5155
NHL Third Liner
NHL Third Liner
 
Posts: 47,740
Joined: Wed Dec 20, 2006 9:06 pm
Location: governor of Fayettenam

Re: Excel

Postby ulf on Thu Nov 08, 2012 5:34 pm

Hey I think VLOOKUP will work
ulf
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 13,951
Joined: Wed Oct 03, 2007 4:41 pm

Re: Excel

Postby count2infinity on Thu Nov 08, 2012 5:48 pm

did you try WLOOKUP yet?
count2infinity
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 21,692
Joined: Sun Mar 04, 2007 3:03 pm
Location: #isitoctoberyet??? Lololololololol

Re: Excel

Postby PensFanInDC on Thu Nov 08, 2012 5:56 pm

You can select the zip code column and sort it.
PensFanInDC
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 23,913
Joined: Thu Aug 23, 2007 3:28 pm
Location: Fredneck

Re: Excel

Postby JeffDFD on Fri Nov 09, 2012 12:00 am

if you don't know vlookup, you can use nested if statements.

basically it would be IF 'cell reference where zip code is' = 'the zip code Bob covers' THEN, 'what you want if it is true (probably bob's name)' ELSE 'this is where the what you want if it is false goes but you just start a new if statement with the next persons zip code and name.
JeffDFD
AHL All-Star
AHL All-Star
 
Posts: 5,575
Joined: Wed May 21, 2008 2:55 pm
Location: Inside the tumor in Crosby's neck.

Re: Excel

Postby shmenguin on Sat Nov 10, 2012 5:33 pm

I just load everything into SQL Server and query away

/supernerd
shmenguin
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 22,746
Joined: Mon Nov 06, 2006 11:34 pm

Re: Excel

Postby Kraftster on Tue Jan 15, 2013 11:12 am

Please help. I am preparing cap sheets for my fantasy hockey league and I am running into a problem.

This is what the spreadsheet looks like:

Spoiler:
Image


In all of the years to the right of 2012-2013, I have the following: =IF((C9=" ")," ",IF((C9=1)," ",IF((C9>1),(C9-1))))

As you can see, the total cap hit for years after this year is not calculating. I'd rather not display a 0 in all the cells where this is no contract, but is that the only way to avoid this?? I assume it is having a problem adding the years in future years because of the blank cells?

Any suggestions?
Kraftster
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,081
Joined: Mon Apr 09, 2007 9:25 am
Location: Frolik

Re: Excel

Postby Letang Is The Truth on Tue Jan 15, 2013 11:19 am

What is your formula in the total cell?
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,143
Joined: Fri Oct 31, 2008 2:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby Letang Is The Truth on Tue Jan 15, 2013 11:22 am

Do sumif and make your criteria >0
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,143
Joined: Fri Oct 31, 2008 2:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby Kraftster on Tue Jan 15, 2013 11:33 am

I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.
Kraftster
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,081
Joined: Mon Apr 09, 2007 9:25 am
Location: Frolik

Re: Excel

Postby Rylan on Tue Jan 15, 2013 11:35 am

Kraftster wrote:I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.


It means it will add any cells with numbers > 0.
Rylan
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,014
Joined: Sun Aug 30, 2009 12:07 am
Location: Dead and Without Love

Re: Excel

Postby Letang Is The Truth on Tue Jan 15, 2013 11:36 am

Kraftster wrote:I stole that IF formula from Mr Colby's baseball cap sheet, so I don't know what any of what you said means. My total cell is just a simple sum w/ data range.


Do the sumif
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,143
Joined: Fri Oct 31, 2008 2:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby Kraftster on Tue Jan 15, 2013 11:38 am

How is it written? I need to add X5:X25 and subtract X28.
Kraftster
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,081
Joined: Mon Apr 09, 2007 9:25 am
Location: Frolik

Re: Excel

Postby canaan on Tue Jan 15, 2013 11:39 am

sure is dweeby in here
canaan
NHL Third Liner
NHL Third Liner
 
Posts: 38,947
Joined: Tue May 30, 2006 11:13 am
Location: Fritos. On. My. Sub.

Re: Excel

Postby King Sid the Great 87 on Tue Jan 15, 2013 11:51 am

Kraftster wrote:How is it written? I need to add X5:X25 and subtract X28.


=Sum(X5:X25)-X28

If all you are doing is summing a column, SUMIF > 0 does the same thing as SUM, assuming you have no negative values
King Sid the Great 87
AHL'er
AHL'er
 
Posts: 2,938
Joined: Wed Jul 12, 2006 7:41 am

Re: Excel

Postby Kraftster on Tue Jan 15, 2013 11:55 am

That is what I have now. I think I need to introduce this whole if >0 concept but I can't seem to write it correctly.
Kraftster
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,081
Joined: Mon Apr 09, 2007 9:25 am
Location: Frolik

Re: Excel

Postby King Sid the Great 87 on Tue Jan 15, 2013 12:08 pm

=SUMIF(X5:X25,">0")-X28
King Sid the Great 87
AHL'er
AHL'er
 
Posts: 2,938
Joined: Wed Jul 12, 2006 7:41 am

Re: Excel

Postby Kraftster on Tue Jan 15, 2013 12:13 pm

Working now, thanks.
Kraftster
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 16,081
Joined: Mon Apr 09, 2007 9:25 am
Location: Frolik

Re: Excel

Postby Mr. Colby on Thu Feb 14, 2013 12:41 am

Alright this is very advanced Excel, if anyone can help me this I will be infinitely impressed. Can't find the answer anywhere on google.

On my "chart/relevant data" sheet I have 3 drop-down lists (Project, Month, Impact) that refer to columns in my raw data table.

What I want to do is pull the relevant data to the "chart/relevant data" sheet, but filter based on the selections made in the 3 drop downs. So that is to say, I want to only populate the data on that sheet that is relevant to the 3 drop downs (i.e. meets all 3 criteria).

I found one way to do this: I created a hidden sheet that populates based on Project choice, then a 2nd hidden sheet that pulls the list from the first hidden sheet and filters down based on Month choice, then I can populate the sheet of interest using an extensive IF formula based on the 2nd hidden sheet as well as the Impact choice (pulling the twice-filtered data in to the main sheet and filtering on the Impact choice).

My question: Is there VBA to write that allows this to be done via a Macro? My problem is that with the 2 hidden sheets and the extensive amounts of data I have, it makes the sheet too big and slow.

The reason I want to do this is so I don't have to select from the 3 drop-downs and then manually filter 3 times, and re-filter every single time I change my drop-down selection.

HELP!
Mr. Colby
AHL Hall of Famer
AHL Hall of Famer
 
Posts: 8,733
Joined: Sun Dec 09, 2007 1:33 pm
Location: Born and Raised in Kent Manderville

Re: Excel

Postby Juice on Thu Feb 14, 2013 12:51 am

I bet if we did a Vcardlookup of the posters in this thread true would get returned more than false boosh suck it nerds
Juice
AHL'er
AHL'er
 
Posts: 4,761
Joined: Wed Oct 25, 2006 10:23 pm
Location: beyondauction [16:54:33] you are scammer

Re: Excel

Postby columbia on Thu Feb 14, 2013 12:56 am

Juice wrote:I bet if we did a Vcardlookup of the posters in this thread true would get returned more than false boosh suck it nerds


This is why I load data into MySQL and just write queries.
Macros and Excel are for wusses.
columbia
NHL Third Liner
NHL Third Liner
 
Posts: 47,253
Joined: Wed Feb 20, 2008 12:13 am
Location: If you don't have a seat at the table, you're probably on the menu.

Next

Return to NHR

Who is online

Users browsing this forum: No registered users and 4 guests


e-mail