Excel

Forum for posts that are not hockey-related.

Moderators: Three Stars, dagny, pfim, netwolf

Re: Excel

Postby Mr. Colby on Wed Feb 13, 2013 11:59 pm

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


:fist:
Mr. Colby
AHL Hall of Famer
AHL Hall of Famer
 
Posts: 8,881
Joined: Sun Dec 09, 2007 12:33 pm
Location: Born and Raised in Kent Manderville

Re: Excel

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

columbia wrote:
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.

I understand some of these words.
=Vcardlookup'columbia' returns "true"
Juice
AHL'er
AHL'er
 
Posts: 4,805
Joined: Wed Oct 25, 2006 9:23 pm
Location: beyondauction [16:54:33] you are scammer

Re: Excel

Postby mac5155 on Fri Mar 01, 2013 1:24 pm

Someone help me make a macro form that collects information but doesnt display it to everyone. I want to send out a shared excel sheet to collect personal info off people but can't let anyone see it. Info such as address, phone number, email address, etc.
mac5155
NHL Third Liner
NHL Third Liner
 
Posts: 48,152
Joined: Wed Dec 20, 2006 8:06 pm
Location: governor of Fayettenam

Re: Excel

Postby TheHammer24 on Fri Mar 01, 2013 2:17 pm

Vlookup is the way to go. But you can also just use "filter." That will bring a drop down box over the name of every column. Then you can just select only the zips you want, and only rows containing those zips will be displayed. Copy and Past the names out into a separate sheet.
TheHammer24
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 14,157
Joined: Mon Nov 06, 2006 9:28 pm

Re: Excel

Postby mac5155 on Tue Jun 04, 2013 10:05 am

quick question.

I have a list of 2,500 numbers that are 7 digit. I want to count the occurances of each one and see what one is on there the most, least, and everywhere between.

Is it a Count-If function? I don't want to have to type in each individual number. There are probably 1500 different numbers on there 2500 times.
mac5155
NHL Third Liner
NHL Third Liner
 
Posts: 48,152
Joined: Wed Dec 20, 2006 8:06 pm
Location: governor of Fayettenam

Re: Excel

Postby mac5155 on Tue Jun 04, 2013 10:26 am

nvm, got it with a pivot table, duh.
mac5155
NHL Third Liner
NHL Third Liner
 
Posts: 48,152
Joined: Wed Dec 20, 2006 8:06 pm
Location: governor of Fayettenam

Re: Excel

Postby relantel on Tue Jun 04, 2013 11:21 am

Try being the idiot that went row by row manually changing absolute cell references to another sheet to assemble a set of data sortable in another sheet without simply copying & pasting the data only. Considering the stat is dead and won't be added to, I didn't need to do that. But there has to be a way to do a realtive copy/paste and then convert to an absolute refernence without having to manually insert a pair of $$ in each cell.
relantel
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 17,439
Joined: Sat Jan 28, 2006 10:24 am
Location: The card table

Re: Excel

Postby Letang Is The Truth on Tue Jun 04, 2013 11:57 am

What were you trying to do?
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,499
Joined: Fri Oct 31, 2008 1:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby mac5155 on Tue Jun 04, 2013 12:10 pm

could do a merge cells right?
mac5155
NHL Third Liner
NHL Third Liner
 
Posts: 48,152
Joined: Wed Dec 20, 2006 8:06 pm
Location: governor of Fayettenam

Re: Excel

Postby relantel on Tue Jun 04, 2013 12:42 pm

Letang Is The Truth wrote:What were you trying to do?

had a sheet that aggregated data off of some 40+ other sheets unto a sum column, along with two text columns. Was pulling those three to a new sheet to sort independently in the new sheet without messing with the entry order (as the raw entries are largely grouped across the sheets - names rarely appear in more than 10 sheets).

Maybe what I was intending would work with relative references, but I've found absolute less error prone when sorting.
relantel
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 17,439
Joined: Sat Jan 28, 2006 10:24 am
Location: The card table

Re: Excel

Postby ulf on Sun Jun 09, 2013 10:06 am

v-lookup?
ulf
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 14,341
Joined: Wed Oct 03, 2007 3:41 pm

Re: Excel

Postby Letang Is The Truth on Thu Jun 27, 2013 3:43 pm

so i have an 07 document that populates with customer encounters. i thought i would be able to make it thru the fiscal year but i reached the row limit. its about 55 columns wide with some pretty sophisticated logic as well. is there an easy way to convert this to a 2010 document and increase the row limit?
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,499
Joined: Fri Oct 31, 2008 1:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby Beveridge on Thu Jun 27, 2013 3:46 pm

If I follow you, you should be able to just open the file in the 2010 version and continue.
Beveridge
AHL'er
AHL'er
 
Posts: 3,852
Joined: Mon Jun 25, 2012 2:38 pm
Location: Punxsutawney

Re: Excel

Postby Letang Is The Truth on Thu Jun 27, 2013 3:53 pm

i tried that, it still keeps the finite number of rows
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,499
Joined: Fri Oct 31, 2008 1:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby ExPatriatePen on Thu Jun 27, 2013 5:39 pm

Letang Is The Truth wrote:so i have an 07 document that populates with customer encounters. i thought i would be able to make it thru the fiscal year but i reached the row limit. its about 55 columns wide with some pretty sophisticated logic as well. is there an easy way to convert this to a 2010 document and increase the row limit?

Are they individual customer encounters? Ie. one per customer experience?

Couldn't you just break it up with a tab for each month as an easy solution?
ExPatriatePen
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 22,691
Joined: Sat Feb 04, 2006 2:57 pm
Location: Source, Destination, Protocol, Port, size, sequence number, check sum... Yep, that about covers it.

Re: Excel

Postby King Sid the Great 87 on Thu Jun 27, 2013 6:08 pm

Letang Is The Truth wrote:i tried that, it still keeps the finite number of rows


Did you do a save-as and change the format to 2010 and then re-open?
King Sid the Great 87
AHL'er
AHL'er
 
Posts: 2,987
Joined: Wed Jul 12, 2006 6:41 am

Re: Excel

Postby Letang Is The Truth on Thu Jun 27, 2013 7:19 pm

King Sid the Great 87 wrote:
Letang Is The Truth wrote:i tried that, it still keeps the finite number of rows


Did you do a save-as and change the format to 2010 and then re-open?


Yes, no dice :(
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,499
Joined: Fri Oct 31, 2008 1:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby Letang Is The Truth on Thu Jun 27, 2013 7:21 pm

ExPatriatePen wrote:
Letang Is The Truth wrote:so i have an 07 document that populates with customer encounters. i thought i would be able to make it thru the fiscal year but i reached the row limit. its about 55 columns wide with some pretty sophisticated logic as well. is there an easy way to convert this to a 2010 document and increase the row limit?

Are they individual customer encounters? Ie. one per customer experience?

Couldn't you just break it up with a tab for each month as an easy solution?


Yes however we have a several pivot tables worth of data that draw their info from this sheet. Is the source data changeable so that you can have it cover multiple tabs?
Letang Is The Truth
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 24,499
Joined: Fri Oct 31, 2008 1:59 pm
Location: The Panda Will Fly Away On A Rainbow

Re: Excel

Postby ulf on Thu Jun 27, 2013 8:40 pm

v lookup?
ulf
NHL Healthy Scratch
NHL Healthy Scratch
 
Posts: 14,341
Joined: Wed Oct 03, 2007 3:41 pm

Re: Excel

Postby mikey287 on Tue Jul 22, 2014 7:49 pm

So, being familiar with RANK, MATCH and INDEX. And having a table using such commands, I have a question that is somewhat difficult to describe.

Let's say I'm ranking the best NHLers.

A = my rank (1, 2...10)
B = data (players)
C = RANK(A1,$A$1:$A$10,1)
...
E = MATCH(G1,$C$1:$C$10,0)
...
G = Final rank (1, 2...10)
H = INDEX($B$1:$B$10,E1)

So let's say I'm making my list of the 50 best players in the league (I only used 10 spots up there, but shut up) and I'm at 47 and I go, "crap, forgot Patrick Kane" and he belongs at 16...I would have to type in Kane as 16 and then every other players number would have to change. Which, using the series command wouldn't be bad if I only make one big error. But if I make a few and start moving things around and etc. I need the list to shape-shift with me so to speak.

You know in a fantasy football or hockey thing on Yahoo. You can kind of drag and drop your players? So, you think this will be a huge year for Derrick Pouliot and you drag him all the way up to #6, well, that slides Ryan Getzlaf to 7, Shea Weber to 8, Anze Kopitar to 9, etc.

In my formula above, if I put Pouliot at 6, I have to manually move Getzlaf to 7, Weber to 8, Kopitar to 9...is there any way to create that mechanism? Or am I stepping into the uncharted (for me) waters of macros?
mikey287
NHL Fourth Liner
NHL Fourth Liner
 
Posts: 19,109
Joined: Sun Jan 29, 2006 5:40 pm
Location: Philadelphia, PA - @MichaelFarkasHF

Re: Excel

Postby PensFanInDC on Tue Jul 22, 2014 9:54 pm

Just put Sid the kid at #1 n'at. Ain't need no list after dat anyhow.
PensFanInDC
NHL Third Liner
NHL Third Liner
 
Posts: 25,909
Joined: Thu Aug 23, 2007 2:28 pm
Location: Fredneck

Previous

Return to NHR

Who is online

Users browsing this forum: No registered users and 8 guests


e-mail