RF Generation.  The Classic and Modern Gaming Databases.RF Generation.  The Classic and Modern Gaming Databases.

New on the Blogs
Hot Community Blog Entries
Nielsen's Favorites on Channel 4
RF Generation Message Board Welcome, Guest. Please login or register.
Did you miss your activation email?
March 29, 2024, 05:34:07 AM
Home Help Search Calendar Member Map Arcade Login Register
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] Print
Author Topic: Excel Lists  (Read 3605 times)
techwizard
Donor
*****
Canada
Posts: 3840


 Stats
« 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
*****
Canada
Posts: 3840


 Stats
« 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
*****
Canada
Posts: 3840


 Stats
« 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
*****
United States
Posts: 6181


WWW Stats
« 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
****
United States
Posts: 1831


WWW Stats
« 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
*****
Canada
Posts: 3840


 Stats
« Reply #5 on: May 27, 2015, 10:29:55 AM »

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

United States
Posts: 6


WWW Stats
« 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
*****
Canada
Posts: 3840


 Stats
« Reply #7 on: July 08, 2015, 05:20:01 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
*****
United States
Posts: 6181


WWW Stats
« 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

United States
Posts: 6


WWW Stats
« Reply #9 on: July 09, 2015, 11:14:30 AM »


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...  laugh
Logged

"UNIX is very simple, it just needs a genius to understand its simplicity." -Dennis Ritchie
A8scooter
CD-i
**
Posts: 343


WWW Stats
« 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
*****
Canada
Posts: 3840


 Stats
« 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


WWW Stats
« 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
*****
United States
Posts: 10853
Awards: 2013 Fantasy Football Winner



 Stats
« 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
*****
Canada
Posts: 3840


 Stats
« 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] Print 
« previous next »
Jump to:  


Login with username, password and session length

Powered by MySQL Powered by PHP 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
Valid XHTML 1.0! Valid CSS!
Page created in 0.079 seconds with 24 queries.
Site content Copyright © rfgeneration.com unless otherwise noted. Oh, and keep it on channel three.