11-28-2019, 01:00 PM
|
#1
|
|
#1 Goaltender
|
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.
|
|
|
|
11-28-2019, 01:05 PM
|
#2
|
|
Franchise Player
Join Date: Mar 2015
Location: Pickle Jar Lake
|
vlookup.
|
|
|
|
|
11-28-2019, 01:36 PM
|
#3
|
|
#1 Goaltender
|
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.
|
|
|
|
|
11-28-2019, 01:38 PM
|
#4
|
|
Franchise Player
Join Date: Mar 2015
Location: Pickle Jar Lake
|
The only other thing I can think of is a pivot table.
|
|
|
|
|
11-28-2019, 01:59 PM
|
#6
|
|
Lifetime Suspension
Join Date: Mar 2013
Exp: 
|
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.
|
|
|
|
|
The Following 3 Users Say Thank You to AnonymousStranger For This Useful Post:
|
|
|
11-28-2019, 02:05 PM
|
#7
|
|
Franchise Player
Join Date: Feb 2010
Location: Park Hyatt Tokyo
|
Huh, who knew Hells Angels used excel for their record keeping. Progressive times.
|
|
|
|
|
The Following 3 Users Say Thank You to topfiverecords For This Useful Post:
|
|
|
11-28-2019, 02:16 PM
|
#8
|
|
#1 Goaltender
|
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.
|
|
|
|
|
The Following User Says Thank You to 81MC For This Useful Post:
|
|
|
11-28-2019, 04:48 PM
|
#9
|
|
Franchise Player
Join Date: Aug 2008
Location: California
|
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.
|
|
|
|
|
11-28-2019, 06:12 PM
|
#10
|
|
Franchise Player
|
Would filters help you?
|
|
|
|
|
11-28-2019, 07:08 PM
|
#11
|
|
Franchise Player
Join Date: Feb 2011
Location: Somewhere down the crazy river.
|
Quote:
Originally Posted by bizaro86
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.
|
|
|
|
|
11-28-2019, 09:33 PM
|
#13
|
|
Crash and Bang Winger
|
Quote:
Originally Posted by 81MC
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?
|
|
|
|
|
11-28-2019, 09:35 PM
|
#14
|
|
#1 Goaltender
|
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.
|
|
|
|
|
11-29-2019, 01:13 PM
|
#15
|
|
Powerplay Quarterback
|
Pivot tables is pretty powerful at sorting entries
You can tally how much times an entry appears in a column, filter etc
|
|
|
|
|
11-29-2019, 01:32 PM
|
#16
|
|
First Line Centre
|
Quote:
Originally Posted by AnonymousStranger
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.
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT -6. The time now is 03:22 AM.
|
|
