Valiant Collection points question...

[etos45]

This probably goes out to Greg, but maybe someone else can answer too...

Okay, so I was doing the Valiant inventory thing on the web site the other day when I got the generated text thing to email to Greg. I didn't think much about it and just emailed it away. Later it occurred to me that this was probably being run through excel (because of the separating commas) which just happened to be a lot like the excel list I was trying to make. My question is how would I go about generating some of the stats like what's on people's inventory list? I made one for total number of issues, but I can't figure out how to come up with a number for individual issues, total percent complete and all that. I'd also love it if I could have some of the other separating factors like on the website. For example, how many variants, actually calculating the points, etc. Basically, I want whatever Greg (or whoever does it) uses, if possible.

[greg]

Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory.

[betterthanezra]

Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory.

YA Think

-Brian

[acc205]

Someone needs an intern....or a knob

[etos45]

Close... the comma-delimited style is used by Access.

Argh!! Now I have to learn another program?
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! Not to mention it entered my Qty in for me.

I'm about a year behind on updating the inventory.

Heh... well, it's a fun system when it's up... I love playing the "points" game. Speaking of which, if you ever do get around to updating it and you want to delete G. Diehl, you can... I resubmitted the new one under etos45... figured it made more sense to use the screen name.


If any excel geniuses know how to get me where I wanna be I'd love to hear it... in the mean time I've got another 50 excel tutorials to watch...

[Chiclo]

Close... the comma-delimited style is used by Access.

Argh!! Now I have to learn another program?
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! Not to mention it entered my Qty in for me.

Sorta, not really. It's still all part of Office.

[etos45]

Close... the comma-delimited style is used by Access.

Argh!! Now I have to learn another program?
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! Not to mention it entered my Qty in for me.

Sorta, not really. It's still all part of Office.

Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word.

[greg]

Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory.

YA Think

-Brian

Hey!

I just compiled all the data that's been waiting for the inventory update.

44,138 rows.



Ok, now to make it all the same format and actually update the database.

Some of you have "helpful" email systems that add extra line feeds for no reason...
It's especially lovely when you're trying to have one comic per row,
and you get data that looks like this:

1185,Unity Trade Paperback - Diamond Limited
1,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with
slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover
Price,1,204,"someone","8/4/2006"

...when it should look like this:

1185,Unity Trade Paperback - Diamond Limited,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover Price,1,204,"someone","8/4/2006"

Not a big deal, right? Well, not unless you've got 44,138 of them.

[whovianone]

Close... the comma-delimited style is used by Access.

As far as "totals" go, I created a "Pre-Unity Rescue" page a while back,
showing how many of the pre-Unity books had been rescued into our collections:
http://www.valiantcomics.com/valiant/rescue.asp

That lead to the next question... "What about the other books?"

So, here ya go:
Valiant 1991-1996 totals:
http://www.valiantcomics.com/valiant/rescueall.asp


I'm about a year behind on updating the inventory.

YA Think

-Brian

Hey!

I just compiled all the data that's been waiting for the inventory update.

44,138 rows.



Ok, now to make it all the same format and actually update the database.

Some of you have "helpful" email systems that add extra line feeds for no reason...
It's especially lovely when you're trying to have one comic per row,
and you get data that looks like this:

1185,Unity Trade Paperback - Diamond Limited
1,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with
slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover
Price,1,204,"someone","8/4/2006"

...when it should look like this:

1185,Unity Trade Paperback - Diamond Limited,1,204,"someone","8/4/2006"
952,Unity Saga Trade Paperback 1 (with slipcase),1,204,"someone","8/4/2006"
957,Valiant Era Trade Paperback,1,204,"someone","8/4/2006"
1284,Visitor vs Valiant Universe 1 - $2.50 Cover Price,1,204,"someone","8/4/2006"

Not a big deal, right? Well, not unless you've got 44,138 of them.

OMG, I feel your pain Greg. I had to do an inventory for a furniture store. 126,000 entries! IN QUICKBOOKS!

Anyway, just know your efforts will be greatly appreciated by at least one of us.

[greg]

Ok, forgot to add the link to the Acclaim totals earlier today:
http://www.valiantcomics.com/valiant/rescueacclaim.asp


Oh, and by the way... Inventories updated.

http://www.valiantcomics.com/valiant/co ... points.asp

[betterthanezra]

Ok, forgot to add the link to the Acclaim totals earlier today:
http://www.valiantcomics.com/valiant/rescueacclaim.asp


Oh, and by the way... Inventories updated.

http://www.valiantcomics.com/valiant/co ... points.asp

-Brian

[Chiclo]

It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops.

[SmokinAFuente]

It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops.

Same here.

Now, when are you going to add the BloodShot Platinum to the list?

[SmokinAFuente]

Thanks for doing all that work for us Greg.

[greg]

It's awesome you updated the inventories, but now I see I need to send an updated e-mail.

Woops.

Same here.

Now, when are you going to add the BloodShot Platinum to the list?

Actually, it can be added, but not using the automated tool.

If you've got one, just let me know.

[superman-prime]

when was the last invantory update?

[superman-prime]

o wait im in there
Greg is mah friend


now i only got 2500 issues to add at this point
and 20 cgc issues
(everyone wants a turok 9.8

[whovianone]

when was the last invantory update?

He just said he updated it, Supes.

[whovianone]

o wait im in there
Greg is mah friend


now i only got 2500 issues to add at this point
and 20 cgc issues
(everyone wants a turok 9.8

Ya know, I wouldn't pay for one, but I'd trade for one. PM me what you want.

[cjmiller2213]

Close... the comma-delimited style is used by Access.

Argh!! Now I have to learn another program?
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! Not to mention it entered my Qty in for me.

Sorta, not really. It's still all part of Office.

Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word.

I am confused as to why you can't use this list in excel. I don't really know what you are talking about - but I do know if you have data in access you can easily convert it to an excel file by using the export funcions. And if the data is comma separated, there is no reason excel cannot open it. Thought I'd throw myself into the mix - I deal with data conversion daily (and crapccess is not a very friendly program).

-Cory

[greg]

Close... the comma-delimited style is used by Access.

Argh!! Now I have to learn another program?
Well, on the bright side, I used the comma system to separate the text list you had and it basically typed out the list for me, which is good because I only made it to Bloodshot before I got REALLY bored! Not to mention it entered my Qty in for me.

Sorta, not really. It's still all part of Office.

Yeah... but... it's, like, the SOUTH side of Office... I only chill here on the North to North-East side of Office with Excel and Word.

I am confused as to why you can't use this list in excel. I don't really know what you are talking about - but I do know if you have data in access you can easily convert it to an excel file by using the export funcions. And if the data is comma separated, there is no reason excel cannot open it. Thought I'd throw myself into the mix - I deal with data conversion daily (and crapccess is not a very friendly program).

-Cory

This list can be used in Excel.

I just mentioned Access because that's where I use it.
(...and it only sucks for the first four or five years. After that it rocks the house.)

If someone does a "cut-and-paste" of the data, into a regular text file, like Notepad...
Then change the extension from ".txt" to ".csv", it should open in Excel.

[greg]

...which reminds me... I solved all these problems last year.

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved.

[etos45]

...which reminds me... I solved all these problems last year.

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved.

Yeah... I noticed that when I was looking at my lovely pie charts.

I have one last question for you if you can answer it... I've got everything I want on my little excel list... I finally figured out the whole total issues, total individual, total needed deal, but I want it to do the point calculation too (because, as I said, it's so dearn fun ). But I can't figure out the formula for crap. I know it's something like 1 point per dollar for the first issue and then it's cut in half every time after that, but how do you formulate that? I figured it probably uses the IF function with some kind of loop, but that's about as far as I could get it.

[greg]

...which reminds me... I solved all these problems last year.

Just choose a collection from the list:
http://www.valiantcomics.com/valiant/ma ... select.asp

Scroll to the bottom of the page...
http://www.valiantcomics.com/valiant/ma ... asp?id=999
...and look for a link that says:
Create a Microsoft Excel file for this collection.

Problem solved.

Yeah... I noticed that when I was looking at my lovely pie charts.

I have one last question for you if you can answer it... I've got everything I want on my little excel list... I finally figured out the whole total issues, total individual, total needed deal, but I want it to do the point calculation too (because, as I said, it's so dearn fun ). But I can't figure out the formula for crap. I know it's something like 1 point per dollar for the first issue and then it's cut in half every time after that, but how do you formulate that? I figured it probably uses the IF function with some kind of loop, but that's about as far as I could get it.

I'm not sure how you'd do it in Excel.

The point system is:
"value divided by copynumber"

So, the first copy of a $5 book is worth 5/1 points (5 points).
The second copy of that same $5 book is worth 5/2 points (2.5 points).
The third copy of that same $5 book is worth 5/3 points (1.67points).
etc.

I wrote a function in Access to do the calculation, which uses a loop.

I'm sure you can do the same thing in Excel, but I don't know where.

-----
Edit:

Ok, here you go, cut-and-paste the code between the lines
into Excel's Visual Basic Editor (ALT+F11):

---------------------




Public Function GetPoints(x As Variant, y As Variant) As Double

Dim i As Integer

GetPoints = 0
i = 1

Do While (i <= x)
GetPoints = GetPoints + (CDbl(y) / i)
i = i + 1

Loop

End Function


---------------------


Then you call the function as:

=GetPoints(A1,B1)

Where
A1 = the number of copies you have
and
B1 = the value of one copy

Use any columns you like, that's just an example.
The function is GetPoints(Copies,Value)

[etos45]

I don't know if I'm dumb or what, but this isn't seeming to work for me (oh... and really cool with the coding editor in excel). So, I pulled up the Alt-F11 Visual Basic editor. I clicked on Sheet 1. Then I pasted the code. Everything looked okay on that end, but when I went into excel and tried to call the function it gave me a name error. It's acting like the function isn't there, which makes me wonder if I forgot to do something in the VB editor to make it available?