I Wish I Was an Excel Programmer

March 9, 2006 6:18 AM

I appeal to the Excel gods of AskMe for help. My problem is similar to the one answered here, but is more complicated...

Here's the situation - I have a list of items that I need to collect eBay pricing data on. I have found a site that does a weekly scrape of eBay auctions that were completed in the last 7 days. The list I need to collect data for is a subset of the full dataset provided by this site. I am wasting 4 or 5 hours a week transcribing all of this by hand in Excel. I need to automate this process.

Here's an example of what I need:

1. Dataset from the website I found (combined with my subset):

Card Name        Card                Price   StdDev  Avg   High   Low   Change  Raw N
Ætherplasm       AEtherplasm         0.23    0.09    0.23  0.5    0.1    0       21
Agent of Masks   Absolver Thrull     0.09    0       0.09  0.09   0.09   0        1
Battering Wurm   Abyssal Nocturnus   0.72    0.44    0.76  2.75   0.05   0      324
Belfry Spirit    Agent of Masks      0.24    0.15    0.24  0.81   0.05   0       25
Caustic Rain     Angel of Despair    4       1.05    4     8.99   0.38   0      465
Conjurer’s Ban   Battering Wurm      0.15    0.07    0.15  0.25   0.1    0        3
Conjurer’s Ban   Belfry Spirit       0.14    0.08    0.14  0.25   0.06   0       10
etc.

2. What I need excel to do for me:

Card Name        Card                Price  StdDev  Avg   High  Low   Change  Raw N
Ætherplasm       AEtherplasm         0.23   0.09    0.23  0.5   0.1     0      21
                 Absolver Thrull     0.09   0       0.09  0.09  0.09    0       1
                 Abyssal Nocturnus   0.72   0.44    0.76  2.75  0.05    0     324
Agent of Masks   Agent of Masks      0.24   0.15    0.24  0.81  0.05    0      25
                 Angel of Despair    4      1.05    4     8.99  0.38    0     465
Battering Wurm   Battering Wurm      0.15   0.07    0.15  0.25  0.1     0       3
Belfry Spirit    Belfry Spirit       0.14   0.08    0.14  0.25  0.06    0      10
etc.

To make sure I am being clear, column A is my list of items I need the pricing data for. Columns B - I represent the dataset from the website.

posted by Irontom to computers & internet (10 comments total)

Looks like a Pivot table would do the trick.
posted by grateful at 6:21 AM on March 9, 2006

Put your card list in another worksheet (say sheet2) in that worksheet's column A. Make sure this list is sorted alphabetically like in your example.

Change A2 of the full cardlist worksheet to something like this:
IF(ISNA(VLOOKUP(B2,sheet2!A2:A$500,1,FALSE)),"",B2)

And lower-right-corner-copy-drag it down to the bottom.

Change the A2 and B2 to whatever row your data starts on, and change the 500 in A$500 to match whatever row your sheet2 cardlist ends on (probably okay to overshoot).
posted by fleacircus at 6:41 AM on March 9, 2006

Okay. I need to make sure I understand you.

Sheet 1 is the complete dataset, right?

Sheet 2 is my shorter list?
posted by Irontom at 6:48 AM on March 9, 2006

I agree with grateful, read up on the PivotTable in the help files.
posted by Roger Dodger at 6:51 AM on March 9, 2006

Here's what I came up with:

=IF(ISNA(VLOOKUP(B1,Sheet2!A1:A$55,1,FALSE)),"*",B1)

However, when I drag it down, it morphs into something like this:
=IF(ISNA(VLOOKUP(B3,Sheet2!A3:A$55,1,FALSE)),"*",B3)

See how the Sheet2 range is incrementing? Is there any way to fix this - I dont want to have to go in and fix them all by hand.
posted by Irontom at 7:03 AM on March 9, 2006

Regarding Pivot Tables - the help files are nice, but I am just not getting it for some reason. I'll keep trying to wade through it, but the kind of thing that fleacircus is describing is what I was after.
posted by Irontom at 7:07 AM on March 9, 2006

However, when I drag it down, it morphs into something like this:
=IF(ISNA(VLOOKUP(B3,Sheet2!A3:A$55,1,FALSE)),"*",B3)

When you add dollar signs to a cell reference, you can drag it without it changing. So, assuming I understand your question, try this:

=IF(ISNA(VLOOKUP(B3,Sheet2!A$1:A$55,1,FALSE)),"*",B3)

If you ever need to drag something to the side, you add another dollar sign: $A$1, for example.
posted by inigo2 at 7:48 AM on March 9, 2006

What inigo2 said; sorry about that.
posted by fleacircus at 9:22 AM on March 9, 2006

No worries - one problem I am having is punctuation.

For example, the card *Debtors’ Knell* throws a FALSE result, even when it's in both lists.

Is there a fix for this? I'm already saving hours a week, but I'm greedy when it comes to time...
posted by Irontom at 9:43 AM on March 9, 2006

If you don't need the punctuation there you can always do a global replace (ctrl-h) putting the apostrophe (or any punctuation) in the 'find' field and leaving the 'replace with' field blank. Then just hit replace all.
posted by langeNU at 10:42 AM on March 9, 2006