Need help creating large custom numerical list

[mgrantes]

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.

[FatLane]

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.

This you can do easily using autofill.

Fill down through row 76. For row 77, use

=a1+100

Then you can do a single auto fill as far down as you wish.

The second part of your request I'll leave for someone more knowledgeable.

[mgrantes]

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

[mirabeau]

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.

If you know enough about VBA to just run a macro (they're very easy to run and very easy to find out how to run) then you might consider trying this one

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

[mirabeau]

... although your second post does look very different from your first post ...

[mgrantes]

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.

[mgrantes]

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...