Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 11-28-2019, 01:00 PM   #1
81MC
#1 Goaltender
 
Join Date: Aug 2017
Exp:
Default Excel help - brief and moderate request

Hi,
Sorry, nothing worse than bringing spreadsheets up on a hockey forum, I know.

Can someone please tell/remind me what the hell the thing I’m trying to do is called? I’ve done it in the past years ago, now I can’t recall how I saved it.

Column 1 has 1500 unique variables. Column 2 has 100 variables. Column two Value is tied to certain variables in Column 1. These are already tabled, and consistent.

I’d like to avoid long-ass IF arguments and formulas as I refer to this data across a ton of stuff. If anyone can help me out and remind me what this type of saved custom list is called, I’d be really grateful.
__________________
No, no…I’m not sloppy, or lazy. This is a sign of the boredom.
81MC is offline   Reply With Quote
Old 11-28-2019, 01:05 PM   #2
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

vlookup.
Fuzz is online now   Reply With Quote
Old 11-28-2019, 01:36 PM   #3
81MC
#1 Goaltender
 
Join Date: Aug 2017
Exp:
Default

That’s pretty well it, all I can come up with to. I could swear there is a way to take the function of a vlookup, and make it essentially an multi column auto fill list
__________________
No, no…I’m not sloppy, or lazy. This is a sign of the boredom.
81MC is offline   Reply With Quote
Old 11-28-2019, 01:38 PM   #4
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

The only other thing I can think of is a pivot table.
Fuzz is online now   Reply With Quote
Old 11-28-2019, 01:53 PM   #5
Firebot
First Line Centre
 
Join Date: Jul 2011
Exp:
Default

Pivot table is it.

https://www.excel-easy.com/data-anal...ot-tables.html
Firebot is offline   Reply With Quote
Old 11-28-2019, 01:59 PM   #6
AnonymousStranger
Lifetime Suspension
 
Join Date: Mar 2013
Exp:
Default

Forget vlookup, index(match) is where it's at. Look it up and it should only take 5 to 10 minutes to learn how to use the formula. Works way better and faster.
AnonymousStranger is offline   Reply With Quote
The Following 3 Users Say Thank You to AnonymousStranger For This Useful Post:
Old 11-28-2019, 02:05 PM   #7
topfiverecords
Franchise Player
 
topfiverecords's Avatar
 
Join Date: Feb 2010
Location: Hyperbole Chamber
Exp:
Default

Huh, who knew Hells Angels used excel for their record keeping. Progressive times.
topfiverecords is offline   Reply With Quote
The Following 3 Users Say Thank You to topfiverecords For This Useful Post:
Old 11-28-2019, 02:16 PM   #8
81MC
#1 Goaltender
 
Join Date: Aug 2017
Exp:
Default

Thanks guys. I guess I can can save the file and reference it, but that gets sketchy real quick if you have to try to hunt down where a change occurred. Pivots, lookups and Index are super useful, not quite what I hadn’t in mind but a simple match will have to do.

Now, back to tracking my ‘export’ of ‘product’ to international ‘clients’.
__________________
No, no…I’m not sloppy, or lazy. This is a sign of the boredom.
81MC is offline   Reply With Quote
The Following User Says Thank You to 81MC For This Useful Post:
Old 11-28-2019, 04:48 PM   #9
GGG
Franchise Player
 
GGG's Avatar
 
Join Date: Aug 2008
Exp:
Default

The other thing you can do if you can code is program in a bunch of loops using the Visual Basic editor. Once you learn how the cell referencing works it’s pretty powerful. You’d need some familiarity with coding though to make it work.
GGG is offline   Reply With Quote
Old 11-28-2019, 06:12 PM   #10
bizaro86
Franchise Player
 
bizaro86's Avatar
 
Join Date: Sep 2008
Exp:
Default

Would filters help you?
bizaro86 is offline   Reply With Quote
Old 11-28-2019, 07:08 PM   #11
Wormius
Franchise Player
 
Wormius's Avatar
 
Join Date: Feb 2011
Location: Somewhere down the crazy river.
Exp:
Default

Quote:
Originally Posted by bizaro86 View Post
Would filters help you?

I was just thinking that - filter, then copy and & paste. It's kind of brute force and not very elegant, but sometimes if its a one-off type thing then its not worth the time.
Wormius is online now   Reply With Quote
Old 11-28-2019, 07:45 PM   #12
Huntingwhale
Franchise Player
 
Huntingwhale's Avatar
 
Join Date: Sep 2008
Exp:
Default

For vlookup, make sure you also read not only the formula, but the specific 'rules' you need to do to also make it work correctly.

http://www.howtoexcelatexcel.com/blo...tion-in-excel/

https://exceljet.net/things-you-shou...-about-vlookup

I've also heard that index match is much better, but have never actually tried it yet.

Last edited by Huntingwhale; 11-28-2019 at 07:48 PM.
Huntingwhale is offline   Reply With Quote
Old 11-28-2019, 09:33 PM   #13
Ahuch
Crash and Bang Winger
 
Join Date: May 2012
Exp:
Default

Quote:
Originally Posted by 81MC View Post
Hi,
Sorry, nothing worse than bringing spreadsheets up on a hockey forum, I know.

Can someone please tell/remind me what the hell the thing I’m trying to do is called? I’ve done it in the past years ago, now I can’t recall how I saved it.

Column 1 has 1500 unique variables. Column 2 has 100 variables. Column two Value is tied to certain variables in Column 1. These are already tabled, and consistent.

I’d like to avoid long-ass IF arguments and formulas as I refer to this data across a ton of stuff. If anyone can help me out and remind me what this type of saved custom list is called, I’d be really grateful.
Are you thinking of using a data validation formula in the second column? e.g. =INDIRECT to determine which column the source variable is consistent with?
Ahuch is offline   Reply With Quote
Old 11-28-2019, 09:35 PM   #14
81MC
#1 Goaltender
 
Join Date: Aug 2017
Exp:
Default

FWIW, Vlookup has it’s limitations, Lookup can throw errors I’d things aren’t sorted correctly - Match and Index seem to be much less limited.
I’m certain what I had done years ago was essentially a Vlookup, then a custom list that used that Vlookup itself whenever it was created.

Cheers guys...nothing like some spreadsheets to make the night pass.
__________________
No, no…I’m not sloppy, or lazy. This is a sign of the boredom.
81MC is offline   Reply With Quote
Old 11-29-2019, 01:13 PM   #15
jeffman
Powerplay Quarterback
 
jeffman's Avatar
 
Join Date: Dec 2010
Exp:
Default

Pivot tables is pretty powerful at sorting entries
You can tally how much times an entry appears in a column, filter etc
jeffman is offline   Reply With Quote
Old 11-29-2019, 01:32 PM   #16
Esoteric
First Line Centre
 
Esoteric's Avatar
 
Join Date: Jul 2008
Exp:
Default

Quote:
Originally Posted by AnonymousStranger View Post
Forget vlookup, index(match) is where it's at. Look it up and it should only take 5 to 10 minutes to learn how to use the formula. Works way better and faster.
I agree, but I can't wait until XLOOKUP comes out. Just like index match, but quicker to type in.
Esoteric is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 09:52 AM.

Calgary Flames
2023-24




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2021