Home› PSA Set Registry Forum

Getting the POP report data into a spreadsheet--

theBobstheBobs Posts: 1,136 ✭✭
in PSA Set Registry Forum
How?

I copied the data from the web page, but when I paste it into Excel there are spaces on each side of the number. Therefore I can't use the data. I have tried all of the TEXT functions, including TRIM.

Anyone out there know how to make this work?

Brian
Where have you gone Dave Vargha
CU turns its lonely eyes to you
What's the you say, Mrs Robinson
Vargha bucks have left and gone away?

hey hey hey
hey hey hey

Comments

  • mikeschmidtmikeschmidt Posts: 5,756 ✭✭✭
    For the 1955 Bowman set, I've simply re-typed all the numbers in. It is 320 times, which gets to be annoying, but I have yet to find a better way.
    I am actively buying MIKE SCHMIDT gem mint baseball cards. Also looking for any 19th century cabinets of Philadephia Nationals. Please PM with additional details.
  • theBobstheBobs Posts: 1,136 ✭✭
    Data entry is what I am trying to avoid. Hopefully someone has a solution.
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • carew4mecarew4me Posts: 3,471 ✭✭✭✭
    Brian,
    When i subscribed online I was able to highlight the page and paste directly to excel.
    I had to delete some stuff but other wise worked ok.

    Loves me some shiny!
  • theBobstheBobs Posts: 1,136 ✭✭
    Carew--

    That is were I am stuck. I have the information in Excel. I can view and print it from Excel. However, I can't calculate the data. Where I see an 8 in a box, Excel is showing " 8 ". So, if I try to calculate the " 8 ", I get #VALUE. These spaces aren't allowing me to use the data.

    I am hoping Jrinck has some ideas. I want to be able to dump data into the spreadsheet monthly, then use that data to run some analysis.

    Brian
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • carew4mecarew4me Posts: 3,471 ✭✭✭✭
    Brian,
    After you paste it inot the spreadsheet.
    Go to Edit>Clear>Formats and you will be able to enter functions on the row/columns.
    Hope that helps.

    Loves me some shiny!
  • brucemobrucemo Posts: 358 ✭
    New thread about a tool to do this stuff.

    bruce
    Collecting '52 Bowman, '53 Bowman B&W, and '56 Topps, in PSA-7.
    Website: http://www.brucemo.com
    Email: brucemo@seanet.com
  • waitilltheytrytosellwaitilltheytrytosell Posts: 378 ✭
    carew - The only problem with your method is that it doesn't clear all the formats. Most of the numbers are still being looked at as text and can't be played with.

    This is what I do. I use Excel and Word. Copy the info into the Excel spreadsheet. Then select the data in Excel and hit copy. Then copy it into Word using Paste Special - Unformatted Text. Then select it from inside Word and hit copy. Then go back into Excel and paste it into either a new sheet or the first free or clean cell to the right of the original formatted text. Everything you just pasted in will be ready to play with. Then just delete all the columns containing the original formatted text. Takes a few seconds when you are used to doing it.
  • theBobstheBobs Posts: 1,136 ✭✭
    Waittil,

    That is very funny, and sound like it will work. You should send that to Bill Gates. Screw the .net strategy...

    Thanks, I will give it a go. Kinda bummed I didn't think of that approach. Damned clever.

    Brian
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • waitilltheytrytosellwaitilltheytrytosell Posts: 378 ✭
    mikeschmidt - If you have a newer Excel, this is a trick which will save you typing all those numbers when you need to.
    Enter 1 in the first cell, then hit the down arrow. Enter 2, then hit the down arrow. Select both cells with your mouse.
    If you're in luck, there should be a small dark square in the bottom right corner of the 2nd cell.
    Move your mouse over the square in the bottom right corner and your mouse pointer will turn into a plus sign.
    Left click and hold down while it's a plus and drag down to how many ever rows you need.
    Release the left button, and you are done.
    You can use the same method to drag to multiple columns if you want to do a checklist with 50 numbers per column.
    1 51
    2 52
    Select the 4 cells drag down. Then drag right.

    There are other easy ways to do it besides typing them all, but this is easiest if your Excel is newer.
  • theBobstheBobs Posts: 1,136 ✭✭
    Copy from POP, Paste to Excel, Copy from Excel, Paste to Word, Copy from Word, Paste to Excel.

    Issue solved.

    Thanks Jrinck and Waittil...
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • waitilltheytrytosellwaitilltheytrytosell Posts: 378 ✭
    1965 - Did you try it like that? I don't believe that it will work in the same way. You are still going to have some numbers being treated as text. If you are working with the numbers you are going to need to put it into the Excel first.
  • theBobstheBobs Posts: 1,136 ✭✭
    Waittil,

    You are right, I didn't write the order correctly. I have been so busy building logic statements in my spreadsheet that I didn't realize the ordering.

    This works for me (I am in Excel 98) --

    POP to Excel to Word to Excel
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • waitilltheytrytosellwaitilltheytrytosell Posts: 378 ✭
    And when you paste it into Word it needs to go in as - Edit - Paste Special - Unformatted Text. Otherwise you just insert a spreadsheet.
  • theBobstheBobs Posts: 1,136 ✭✭
    Actually, I just did "control-c" then "control-v" on each switch, and everything worked.

    I believe both Word and Excel are 98 on my laptop.
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • gemintgemint Posts: 6,101 ✭✭✭✭✭
    Anybody try with Excel2000? When I "paste special" it doesn't give me the formatting options. It only gives me an option for 'unicode' or 'text'. Regardless of what I select, I get a nice picture with the numbers fanned across several rows totaling several hundred. I tried pasting both into Word and Excel but got the same results.

    Any suggestions? Thanks.
    https://ebay.com/sch/gemint/m.html?_nkw=&_armrs=1&_ipg=&_from=
  • carew4mecarew4me Posts: 3,471 ✭✭✭✭
    If you have excel 2000, the method I mentioned above is the fastest and easiest.

    All numeric values are treated as numbers and all formulas work.

    You only have to access one menu command, no word, no highlighting.




    Loves me some shiny!
  • mcastaldimcastaldi Posts: 1,206 ✭✭✭
    I guess I'm confused, because I've had no problem copy-and-pasting from the pop report straight into Excel.
    Start with card# 1 and select down to the last card. Command+C to copy. Move to Excel go to cell B1 - then Command+V to paste. Then. . .go back to the web pop report and select only the 2nd row (with "Total" under the Name field). Go to Excel and select cell A2 and paste it there. If you paste this row starting in cell A1, the columns won't line up properly.

    Then in the black rows, grades 1-6 will have garbage text. Doing a find-and-replace should take care of these. Since I do only 8s or better, I just delete these columns.

    This same has worked for me on various operating systems and versions of Excel-
    -- Windows XP & Excel 2000
    -- MacOS 9.2 & Excel 2001
    -- Mac OSX & Excel X

    I've also been able to drop the Excel pop reports into my Palm using a 3rd party app image

    Mike
    So full of action, my name should be a verb.
  • rw2winrw2win Posts: 557 ✭
    No problems either. I do it the way Mike C. does. I remember sending you some 65 info. last fall when you were dealing with the same problem. Good luck.
  • theBobstheBobs Posts: 1,136 ✭✭
    rw2win,

    Yes, I have been putting off building this spreadsheet for many months. Bit the bullet last night, and the first draft is ready. Just need to play with it a bit. Next step is to integrate the SMR.
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • BuckwheatBuckwheat Posts: 644 ✭
    Mike,

    I tried your method. the excel spreadsheet looks great, but the values are still text, and not numbers. Thus I can't do any calculating or manipulation of the numbers. Are you able to sum the numbers in your spread sheet, or do you use it just fro reference?

    Buck
    Ole Doctor Buck of the Popes of Hell

  • mcastaldimcastaldi Posts: 1,206 ✭✭✭
    Buck> The only fields I've ever had to change are the values for grades less than 7 that come in on the "black rows". Since I only care about 8 NQ or higher, I just delete these columns altogether. If you want to keep them, doing a simple find-and-replace command should fix them.

    As for your inability to "calculate or manipulate", I guess I'm confused. What are you trying to calculate on the pop report? To make sure adding all the grades for a card add up to the number in the "total" field?

    Mike
    So full of action, my name should be a verb.
  • rbeatonrbeaton Posts: 631 ✭
    Buckwheat,

    one thing you can do to convert from text to numbers is to select all the values which are supposed to be numbers, then find/replace a space with nothing.

    Robert
    Looking for:
    Any high grade OPC Jim Palmer
    High grade Redskins (pre 1980)
  • waitilltheytrytosellwaitilltheytrytosell Posts: 378 ✭
    mike - Say you wanted to sort by the number of 8's graded column to find out what the lowest pop cards are. When the numbers are looked at as text, they do not get sorted in numerical order. A lot of people play around with the numbers to see work up different percentages, totals, or otherwise work the numbers - like the jrinck 1971 project. If you are only going to look at or print the information it doesn't matter if the numbers are looked at as text.
  • theBobstheBobs Posts: 1,136 ✭✭
    I have actually built in logic statements, so that if I enter a grade on a card, the pop report loads on the same line.
    Where have you gone Dave Vargha
    CU turns its lonely eyes to you
    What's the you say, Mrs Robinson
    Vargha bucks have left and gone away?

    hey hey hey
    hey hey hey
  • mcastaldimcastaldi Posts: 1,206 ✭✭✭
    I see what you're saying. I guess I'm boring since I've only been interested in sorting by card number, which happens automatically when you drop straight into Excel. And with the sets I'm doing, I tend to know the 10 or so lowest-pop cards off the top of my head so there hasn't been much of a need.

    Anyway. . .

    Mike
    So full of action, my name should be a verb.
  • BuckwheatBuckwheat Posts: 644 ✭
    RBeaton: Have you actually done this and had it work? I tried it, but Excel couldn't find any spaces. To put a space in the find field, i just hit the spacebar. Is that right?

    Mike: One of the things I do is calculate a buy and sell value for common cards based on the population report. i use a lookup function with a table, so that if the pop report is x, the value of the card is y. This is very handy when i am bidding on Ebay, and want to keep some semblance of sanity.
    It also helps in selling and trading cards.
    Ole Doctor Buck of the Popes of Hell

Sign In or Register to comment.