Home
Databases
Community
Blogs
Tools
Submit
About
Donate
Database Search
Title
Console
Genre
Year
Publisher
RFG ID#
Community Blogs
My Blog
Submit Info
RFG Chat
Store
New on the Blogs
The Top 5 Survival Horror Games for the Sega Dreamcast
(2/20/2024)
Trombone Champ Is a Good Game
(12/30/2023)
Exploring the Infinite Fusion Calculator
(12/5/2023)
Thoughts on the Nintendo Switch OLED Model
(11/21/2023)
Hypnospace Outlaw
(10/30/2023)
OMNIFATE's Release, and a Bit of Development History
(10/1/2023)
This Way Madness Lies is the Best, Weirdest Game I have Ever Played
(8/21/2023)
Hot Community Blog Entries
Trombone Champ Is a Good Game
(1)
Thoughts on the Nintendo Switch OLED Model
(1)
The Steam Indie Spring Sale - What should you get?
(3)
The Top 5 Survival Horror Games for the Sega Dreamcast
(1)
Exploring the Infinite Fusion Calculator
(0)
Flash gaming at its best: Liferaft Zero
(2)
Nielsen's Favorites on Channel 4
Press Playcast Episode 91 - Doki Doki Literature Club
(0)
RFGeneration Secret Santa 2022 Signups are LIVE!!
(0)
Press Playcast Episode 92 - Bioshock Infinite
(0)
Shmup Club January 2023 - Metal Black
(1)
Shoot the Core-cast Episode 052 - Deathsmiles
(0)
Shoot the Core-cast Episode 053 - Fire Shark
(1)
Welcome,
Guest
. Please
login
or
register
.
Did you miss your
activation email?
March 29, 2024, 05:34:07 AM
News:
RF Generation: Burgers from trash cans taste the best.
RF Generation Message Board
|
Collecting
|
Collection Connection
|
Excel Lists
0 Members and 1 Guest are viewing this topic.
« previous
next »
Pages:
[
1
]
Author
Topic: Excel Lists (Read 3605 times)
techwizard
Donor
Posts: 3840
Excel Lists
«
on:
May 26, 2015, 12:26:49 PM »
been playing around with some more advanced features in excel recently, came across pivot tables and charts. anyone else have any nifty excel tricks up their sleeves?
Logged
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #1 on:
May 26, 2015, 12:49:51 PM »
just found out about entering data in via forms, that's extremely useful instead of adding a blank line and entering each one that way
edit: to do this hit the drop down arrow next to the quick access bar at the top, hit more commands. then choose All Commands, find Form, hit Add. now you can just click that Form button and press "new" every time you want to add a new game entry and close when you finish. saves from having to scroll all the way down and add a new blank line (if you have formatting that needs copied). you can also search for any entry too by using the "criteria" button of the form window and typing in something to search in one of the boxes.
«
Last Edit: May 26, 2015, 01:50:06 PM by techwizard
»
Logged
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #2 on:
May 26, 2015, 01:43:06 PM »
you can sort while ignoring spaces and punctuation...wish i knew that years ago.
edit: to do this, first make your data all part of a table if it isn't already. add a new column anywhere to your sheet and use the code below to grab the data from your game title column (if that's what you sort by) and remove the spaces/punctuation. adjust the column letter to match what column you want to grab data from.
Code:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),",",""),".",""),"!",""),"'",""),"&","and")
this specific example will grab the data from cell A2 and remove spaces, commas, periods, exclamation points, apostrophes, and replace ampersand with "and". if you've done this in the first row of a blank column inside the same table as the rest of your data, it should automatically fill in the formula to every other row below including any new rows you make. it'll only do this automatically while in a table as far as i know.
this won't change anything in the actual game title column, only in the new column. now to make the new column hidden. first mark all other columns as unlocked by right-clicking the column letter at the top, go format cells, protection tab, uncheck the "locked" box and hit ok. then do the same for the new column but also mark it as hidden. now select the column with the formulas and next go to the home tab of the toolbar and in the "Cells" category go Format - hide & unhide - hide columns.
lastly, go to the data tab and hit Sort, then choose your hidden column from the drop down box and hit ok. if you'd like to sort in more detail, say by system first and then title, you can add an extra layer to do that. you should now see the list sorted by title but with spaces and punctuation ignored!
«
Last Edit: May 26, 2015, 02:04:39 PM by techwizard
»
Logged
Duke.Togo
Nintender Tape
Director
Posts: 6181
Re: Excel Lists
«
Reply #3 on:
May 26, 2015, 08:21:17 PM »
I've taught Excel for many years, but most folks don't get past the basics. Congrats on digging in and finding all the goodies.
Logged
GamerNick
Collector of All Things Video Game Related
Sega Genesis
Posts: 1831
Re: Excel Lists
«
Reply #4 on:
May 26, 2015, 09:27:31 PM »
I use excel too I should try this I think it would be cool! I use excel for business presentations but that's easy data entry. This would just look cool and me fun to do.
Logged
Nick Procopio
BNS Games
"Gotta Play To Win"
www.bnsgames.com
Looking for CIB - NES Games or Boxes & Manuals in Bulk.
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #5 on:
May 27, 2015, 10:29:55 AM »
Quote from: Duke.Togo on May 26, 2015, 08:21:17 PM
I've taught Excel for many years, but most folks don't get past the basics. Congrats on digging in and finding all the goodies.
cool! i just dived into userforms with VBA, this should get interesting.
Logged
subassy
Tiger Gizmondo
Posts: 6
Re: Excel Lists
«
Reply #6 on:
July 08, 2015, 03:38:03 PM »
I've been using excel for a few random things for a long time too and I think I'm about ready to try and figure what exactly pivot tables are. Seems like something that would seem incredibly easy once I saw the use and how to set it up.
Of course for something like a game collection it seems like a
real
database like Access would be better. Then there's actual SQL queries and and all kinds of fun stuff to add in. And it's actually made to act like a program. It may as well
be
Visual Basic. Could even add in cart scans as part of the data. Then use some OLE stuff to pump it back into excel for charts and graphs. I don't know if I'll ever get around do doing that kind of ridiculous stuff. I have had a few reasons to really learn Access though.
Logged
"UNIX is very simple, it just needs a genius to understand its simplicity." -Dennis Ritchie
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #7 on:
July 08, 2015, 05:20:01 PM »
Quote from: subassy on July 08, 2015, 03:38:03 PM
I've been using excel for a few random things for a long time too and I think I'm about ready to try and figure what exactly pivot tables are. Seems like something that would seem incredibly easy once I saw the use and how to set it up.
Of course for something like a game collection it seems like a
real
database like Access would be better. Then there's actual SQL queries and and all kinds of fun stuff to add in. And it's actually made to act like a program. It may as well
be
Visual Basic. Could even add in cart scans as part of the data. Then use some OLE stuff to pump it back into excel for charts and graphs. I don't know if I'll ever get around do doing that kind of ridiculous stuff. I have had a few reasons to really learn Access though.
you can do some of that in Excel too, like the cart scans. i tried access at one point but the only thing i used it for was filtering my list. then i found out if you make all your excel data into an actual table you can do a lot more with it, like enable filters.
Logged
Duke.Togo
Nintender Tape
Director
Posts: 6181
Re: Excel Lists
«
Reply #8 on:
July 08, 2015, 07:01:10 PM »
At that point you might just want to move up to Access. Very user friendly database software.
Logged
subassy
Tiger Gizmondo
Posts: 6
Re: Excel Lists
«
Reply #9 on:
July 09, 2015, 11:14:30 AM »
Quote from: techwizard on July 08, 2015, 05:20:01 PM
you can do some of that in Excel too, like the cart scans. i tried access at one point but the only thing i used it for was filtering my list. then i found out if you make all your excel data into an actual table you can do a lot more with it, like enable filters.
I know, you're just using excel as a database instead of using a database for your database. Excel isn't necessarily designed for querying data while Access is designed specifically ONLY for that. I'm saying this knowing myself I should be doing this. Actually I'm using google docs spreadsheets so it's even worse than excel.
By the way, not to detract from your accomplishment as you're more advanced than I am. Just saying you're...using a screw driver as a crowbar...
Logged
"UNIX is very simple, it just needs a genius to understand its simplicity." -Dennis Ritchie
A8scooter
CD-i
Posts: 343
Re: Excel Lists
«
Reply #10 on:
October 04, 2015, 12:44:45 PM »
If u add on excel like that does it move all columns down one cause mine look kinda like
Model # title cart manual box pic text 77 rlease
Cx 2601 combat x x x x x x .
Cx 2602. A s battle x x x x x
Cx2604 space war x x x x
Would cx2603 star ship fit right in with ur add feature without me making a new line the old way? And would the columns all move over the same way too downward?
«
Last Edit: October 04, 2015, 12:47:39 PM by A8scooter
»
Logged
Currently looking for :
Atari 8 bit
+ game cartridges ( all brands and most titles )
+ Prototype and Demo cartridges
+ manuals and boxes
+ Atari 130XE system, and any system boxes outside of the Atari 400 system.
Check out my Atari 8-Bit Museum Site at:
[url]http://www.a8museum.com[
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #11 on:
October 04, 2015, 02:19:48 PM »
which add feature, sorry? the one about entering data via forms? that will put all the data in a new line at the bottom and then you would have to use data sorting to get it where you want it. not sure what else you might be referring to.
Logged
A8scooter
CD-i
Posts: 343
Re: Excel Lists
«
Reply #12 on:
October 05, 2015, 02:18:14 PM »
I think that's what I'm referring to. It was the add feature and it was suppose to make a new line for your excel but didn't know where the line was placed (the bottom of the page seas where I was wondering or if it auto sorted to the right spot)
Logged
Currently looking for :
Atari 8 bit
+ game cartridges ( all brands and most titles )
+ Prototype and Demo cartridges
+ manuals and boxes
+ Atari 130XE system, and any system boxes outside of the Atari 400 system.
Check out my Atari 8-Bit Museum Site at:
[url]http://www.a8museum.com[
Shadow Kisuragi
Variant Collector
Director
Posts: 10853
Awards: 2013 Fantasy Football Winner
Re: Excel Lists
«
Reply #13 on:
October 05, 2015, 05:02:08 PM »
You can always sort using the built-in Excel sort, or you can define your own sorting preferences if you want.
Logged
techwizard
Donor
Posts: 3840
Re: Excel Lists
«
Reply #14 on:
November 11, 2015, 07:10:37 PM »
tidied up my excel list considerably. i seperated dates (date added to list, date completed, and date last played) to a seperate sheet in the same workbook. also added my digital games and hardware bundle lists as seperate sheets in my main workbook so everything is together now. went back to playing with pivot tables, now using date completed data. took a couple screenshots but not shown is i can check how many games i've beaten in any month, or how many on a specific day of each month.
https://scontent-sea1-1.x...3592011943329476869_o.jpg
https://scontent-sea1-1.x...6418290614037058622_o.jpg
«
Last Edit: November 11, 2015, 07:14:15 PM by techwizard
»
Logged
Pages:
[
1
]
« previous
next »
Jump to:
Please select a destination:
-----------------------------
Gaming
-----------------------------
=> Video Game Generation
===> RFGeneration Archives
=> Community Playthroughs
=> What About Channel 4?
=> The Arena
=> RF Generation Podcasts
-----------------------------
Collecting
-----------------------------
=> Collection Connection
=> Trophies From the Hunt
=> Sales Extravaganza
===> Free Stuff
===> Keep An Eye Out For Me!!!
-----------------------------
Announcements and Feedback
-----------------------------
=> Announcements and Feedback
=> The Thinktank
=> RF Generation Events
-----------------------------
Other
-----------------------------
=> Idle Chatter
=> Media Room
=> The Gym
1 Hour
1 Day
1 Week
1 Month
Forever
Login with username, password and session length
Powered by SMF 1.1.21
|
SMF © 2015, Simple Machines
Simple Audio Video Embedder
RF Generation Theme derived from YabbGrey By
Nesianstyles
| Buttons by A.M.A
Page created in 0.079 seconds with 24 queries.
Site content Copyright © rfgeneration
.
com unless otherwise noted
.
Oh, and keep it on channel three
.