Thread: Another excel question

Ok so here is what I need to do. I have to do a test case that involves me doing something 20k times. So I am trying to create an excel file with 20k different things that way I only have to do the task itself once and complete it 20k times, as opposed to doing 1 or 2 inputs and sending each of them 10k.

So in excel what I want to do is create a file that basically looks like this

cojiro1tpg
cojiro2tpg
cojiro3tpg
cojiro4tpg
etc
down to 20k

What I am wondering is if there is anything that I can put into the # spot where it will autofill with the number of the row.

So something like cojiro(someshit)tpg.

i tried googling but I am having a bitch of a time putting all that information ^^^^ into something understandable in search terms.

This may not be the best/easiest solution, but it will work.

Make A1 cojiro
Make B1 1
Make C1 tpg
Make D1 =A1&B1&C1

Copy A1 down to 20k
If you click on B1 there will be a a dark square on the bottom right. Click, hold, and drag it down to line 20k. It will make it all 1s, but click on the little box and select fill series.
Copy C1 to 20k
Copy D1 to 20k

Column D will have the cojiroXtpg

I will keep looking for an easier way.

bwuaha thanks. that works.

If the number is at the beginning or end you can do it without the formula. If it has to be in the middle you will have to use the formula though. For example.

A1 = cojiro1
A2 = cojiro2

Highlight A1 and A2, then click the fill handle and drag to 20K.

no i thought of that but it is in the middle. its my email address using the + handle for it to register as a different address.

so its more like cojiro+1@tpg.com

I do this all the time. Here is what you do.

In A1, have this, "=ROWS(A$1:$A1)"

In B1, have this, "="cojiro"&A1&"tpg""

Now, drag and highlight down from A1 to A20k, and then click "ctrl + d" to drop fill. That gives you 1 to 20k.

Then, do the same thing on B1 to B20k. Tada!

I knew there was an easier way. Nice work huh.

A1 can use the fill series.

And I just realize the quotes are kinda confusing in my post. Anyways, you guys will get it.

Also, sometimes when you try to process excel file with functions, it gets tricky. You better highlight the column, copy, and paste special to a new excel with values, or better yet post to a text file before processing the file.

Alt+F11
Double-click on the sheet you want to do this on and copy the following:

Code:

Sub CojiroNaming()
    Dim x As Integer
    x = 1
    
    Do Until x = 20001
        Range("A" & x).Select
        ActiveCell.Value = "corijo" & x & "tpg"
        x = x + 1
    Loop
End Sub

Go back to your spreadsheet, go to Developer ribbon, click on Macros and run CojiroNaming

Not even going to ask why you need 20k email addresses.