This is a discussion on Need help creating large custom numerical list within the Excel Questions forums, part of the Question Forums category; Hello, I need help creating a large numerical list and I can't figure it out for the life of me. ...
Hello, I need help creating a large numerical list and I can't figure it out for the life of me. I can use excel's autofill function to create an initial list; for example 1 to 10,000. However, I don't need all 10,000 values. Instead, for every 100 values I only want to keep 76 of them; ie., 1-76, 101-176, 201-276, etc all the way to 10,000.
In reality my list is a little over 1 million but the idea remains the same regardless if I'm looking within every 10th range, 100th range, 1000th range. In anycase, that's a lot of tedious repetitive deleting--there must be a better way. Articles I've read regarding conditional formatting and array formulas don't exactly address my situation or maybe my search criteria isn't good enough. I know nothing about VBA. Regardless, any help would be GREAT!
In addition to the above (although not required) I would like to further know how to Keep/delete every "nth" value within the custom list. So, within 1-76 I may want to delete every 7th value. It's like a custom list within a custom list! lol
Again, thanks to any and all that may have ideas on how to accomplish these two tasks.
It's a bit more complicated than that. (my fault for not being more clear) Essentially I want my list to look like this
1234 1235 1236 1237 1238 1239 1245 1246 1247 1248 1249 1256 1257 1258 1259 1267 1268 1269 1278 1279 1289 1345 1346 1347 1348 1349 1356 1357 1358 1359 1367 1368 1369 1378 1379 1389 1456 1457 1458 1459 1467 1468 1469 1478 1479 1489
There are many things going on here.
1st: I don't need values ending in 0-3. A simple colume delete will handle this regarless of the size of the list.
2nd: this example has 3 "triangles" each stopping at 1X89 before beginning the next triangle.
3rd: Within a triangle each row begins at a factor of 11. Thus 1456+11=1467 etc until 1489.
4th: each triangle begins by a factor of 111 of the previous triangle. Thus 1234+111=1345.
This is too complicated for autofill to do. And it's very tedious to do manually seeing as my list is much MUCh larger--a cool million EASY. Plus it contains a few thousand of these triangles whereas this example only has 3. I figure it'd be easier to build a function focused on deleting data rather than generating it.
Lastly, this example doesn't highlight the fact that I'm also deleting rows of data/numerical values before beginning the next triangle.
Yup, kinda complicated for an amateur like myself. I would LOVE to know if a formula exist that does everything at the push of an enter key! I hope I've been clearer. If not, I'd still like my data to look like the example above. Crossing fingers anyway lol
Code:Sub longlist() Dim mx As Long, a() As Long Dim i As Long, k As Long mx = 10 ^ 6 ReDim a(1 To mx, 1 To 1) For i = 1 To mx If Right(i, 2) < 77 And _ Right(i, 2) <> "00" And _ i Mod 7 > 0 Then k = k + 1 a(k, 1) = i End If Next i Cells(1).Resize(k) = a End Sub
... although your second post does look very different from your first post ...
Lol, one thing that has definitely held true is that, much like physics, normal rules don't apply on extreme scales! lol This list has developed a "geometry" I could've never predicted. Like, triangles within triangles and likely within another obscenely huge triangle.
lol, yea. I agree. Very different. Writing a post is like putting a child that you've raised out there into the world only to be completely surprised with the result you get back. Like, wtf and a rehabing the hell out of it.
And sadly, I don't know a thing about VBA. ...was kinda hoping to avoid that... but if its the only way...
Last edited by mgrantes; Dec 26th, 2011 at 08:15 PM.