Programmy type thing request

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Okay I have an incomplete list of some numbers from 1 to 2000, say, and I want to output the numbers that are missing. How would I do this?

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

is the list ordered? are the number unique?

No? Then one way: make a 2000 strong array of boolean variables. If 1 or more instance of a number, i, is present, then mark array[i] to be true. Then afterwards print out the elements which are false.

kalmar · **Posted:** Mon Sep 01, 2008 22:19

I'd be tempted to try that in excel rather than bother writing a program..

Otherwise, if they're in order..

Code:

lastlist=1
for i=1 to 2000
 if list[i]>(lastlist+1) then
   lastlist=list[i]
   print list[i]
 endif
next i

except in Perl which I've never been arsed to learn...

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

The numbers are currently in an excel sheet and I've been trying to make sense of these instructions. I know nothing about programming things. Other than a tiny bit of basic.

MaliA · **Posted:** Mon Sep 01, 2008 22:22

From another place:

Quote:

your running a database back end make sure you sanitise anything passed to the sql query from the front end else someone nasty is going to come along and run a drop query on your tables and db.

Hope that helps.

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

here is some Ruby, which is very much like Perl but better, to do it:

Code:

File.open("theFiletoEndAllFiles", "r") do |s|
  hash.add s
end

hash.each do |h|
 bigArray[h.to_i] = TRUE
end

1..2000.times do |i|
 print bigArray[i] unless bigArray[i]
end

I think it's valid. Can't check right now. Haven;t bothered download the ruby runtime since my computer broke.

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Can someone do it in excel for me. That's about all I have. Or Access I suppose. Access scares me though. I don't understand these new fangled database things!

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

I can do Excel, how would you like the numbers returned to you, in a big long list, or populate another column?

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Another column would be handy. Much appreciated if you can help out! I tried the code on that website but it comes up with an error. I did modify a couple of numbers which I assumed were variable anyway.

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

Download and run this: http://rubyforge.org/frs/download.php/2 ... 186-26.exe
Copy and paste my code posting into a file named "bigpoo.rb". You might want to edit it so that the name of the file is actually the name of the file you have

place bigpoo.rb & the numbers file in the place you installed ruby. Drag the .rb file onto ruby.exe

done. Though you'll probably never use ruby again. OR, you could just send me the list (zip it)?

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

Code:

Sub findmissing()

Columns("A").Select
b = 1
For a = 1 To 2000
Columns("A").Select
On Error Resume Next
Selection.Find(What:=a, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
If Err.Number = 91 Then
    Columns("B").Select
    Cells(b, 2).Select
    ActiveCell = a
    b = b + 1
End If

Next a

End Sub

Really messy, but it should work. I've assumed that your current list is in column A and you'd like the missing numbers output into column B.
If that's not the case, in the line:

Code:

Columns("A").Select

Change the A to the column where your numbers are
and in the lines

Code:

Columns("B").Select
    Cells(b, 2).Select

Change the B to the column you'd like to output the numbers and the 2 to its numerical equivalent (leave the b in the second line)

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Seems to be working. It's certainly doing a lot of flickery jumpyness! The other code I tried just wouldn't run. It's almost hypnotic. Yep, job done, thank you very much!

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

No worries.

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Just one more thing.. would it be able to cope with things like DVD0001 to DVD0200? I'd expect not myself..

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

Not at the moment, as it's checking for an entire match, if you let me know what you've got I should probably be able to sort something out.

Quick edit:

If you've got the numbers 0001 - 0200 all prefixed with DVD, then this should work:

Another edit as I've seen your post below, this should now work for 00001 - 00731, all prefixed with DVD.
Do you want it to just output the missing numbers (eg 5), or the whole thing (eg DVD00005)?

Code:

Sub findmissing()

strID = "DVD"

Columns("A").Select
b = 1
For a = 1 To 731
stra = Right("00000" & a, 5)
Columns("A").Select
On Error Resume Next
Selection.Find(What:=strID & stra, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
If Err.Number = 91 Then
    Columns("B").Select
    Cells(b, 2).Select
    ActiveCell = strID & stra
    b = b + 1
End If

Next a

End Sub

It's not very easy for you to customise at the moment though.

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

I've got numbers from DVD00001 to DVD00731. Can you sort it?

Joans · **Joined:** 31st Mar, 2008 **Posts:** 8655

I've edited it into my post above.

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

I got around it by changing the data. I pasted the data into wordpad and changed DVD to 976 (just because the film 976 evil came to mind). Pasted it back in, ran the macro and sorted it. Used wordpad to change 976 back to DVD. I expect your method is a little bit better.

EDIT: Just tried it and it didn't seem to work. It's fine though I'm sorted now. Thank you for all your kind help!

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

You should have used Ruby. I would only have had to have add 6 characters or so and BAM, it would have dealt with a CSV-excel sheet and BAM it could handle this DVD malarky.

Everyone should use Ruby. That includes YOU as well, reader.

Dimrill · **Posted:** Tue Sep 02, 2008 9:16

Pod wrote:

Everyone should use Ruby.

I dunno, it seems quite explosive. Is it safe?

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

I've yet to blow off any of my limbs with it.
\

Dimrill · **Posted:** Tue Sep 02, 2008 9:29

That's Eddie Murphy's opening scene to Trading Places, yes?

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

While we're at it, does anyone want to program some software for interfacing with Amazon's servers and knocking down the price on my listings when comparing against other sellers/handling orders/sending emails? Anyone? I'm offering money if anyone could actually provide software to do the job.

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Pod wrote:

Download and run this: http://rubyforge.org/frs/download.php/2 ... 186-26.exe
Copy and paste my code posting into a file named "bigpoo.rb". You might want to edit it so that the name of the file is actually the name of the file you have

place bigpoo.rb & the numbers file in the place you installed ruby. Drag the .rb file onto ruby.exe

done. Though you'll probably never use ruby again. OR, you could just send me the list (zip it)?

Only just noticed this. Sorry I missed it last night. Thank you for the offer and I'm going to have a look at it anyawy.

Pod · **Joined:** 30th Mar, 2008 **Posts:** 2568

Nirejhenge wrote:

Pod wrote:

Download and run this: http://rubyforge.org/frs/download.php/2 ... 186-26.exe
Copy and paste my code posting into a file named "bigpoo.rb". You might want to edit it so that the name of the file is actually the name of the file you have

place bigpoo.rb & the numbers file in the place you installed ruby. Drag the .rb file onto ruby.exe

done. Though you'll probably never use ruby again. OR, you could just send me the list (zip it)?

Only just noticed this. Sorry I missed it last night. Thank you for the offer and I'm going to have a look at it anyawy.

When I wrote that I didn't realise you were using excel files. but if you export them as comma seperated variable files it'll work just fine (with a very slight change to the code).

Grim... · **Posted:** Wed Sep 03, 2008 10:04

kalmar wrote:

I'd be tempted to try that in excel rather than bother writing a program..

Otherwise, if they're in order..

Code:

lastlist=0 //not 1
for i=1 to 2000
 if list[i]>(lastlist+1) then
   print list[i]
 endif
lastlist = list[i] //moved from above
next i

except in Perl which I've never been arsed to learn...

FTFY

kalmar · **Posted:** Wed Sep 03, 2008 10:05

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

To dredge this topic up once again, Amazon have changed their system a bit so I could use a bit more help.

They've changed things so that you can't re-use an old number. I figure I can get around this by adding a letter onto the end of the number. However to find the missing numbers I'm going to need it to ignore any letters in there. For example there might be the following sequence of numbers

4400033
4400044
4400045a
4400052
4400053a

So it needs to count count the ones with 'a' after them as not having the a (or whatever letter(s) might be there).

Get the idea? Much appreciated if you can help. AGAIN.

Grim... · **Posted:** Fri Apr 17, 2009 11:41

Still in Excel?
If all the numbers are the same length, you need

Code:

=LEFT(A1,7)

If not

Code:

=LEFT(A1,LEN(A1)-1)

Derek The Halls · **Joined:** 31st Mar, 2008 **Posts:** 3742

Thanks. Where will that go in the macro thing above?

Be Excellent To Each Other

Programmy type thing request
list comparison

Who is online

Be Excellent To Each Other

Programmy type thing request list comparison

Who is online

Programmy type thing request
list comparison