Is it possible in Excel to use a number in one cell to make that many copies of data from another cell in a 3rd cell?

Question

I'm trying to create a database/sitemap of sorts and need to be able to take data I already know and use it to make multiple copies of one cell, append something to what's in that cell based on a number in a different cell. Below is an example of data I have

26      example.com/search?find_loc=St.+John's+Newfoundland

What I need to do is take the URL and, based on the fact that we have 26 pages worth, create the 26 page URLs by appending to each except the first URL the page number as seen in the example below in a 3rd row.

Producing this:

example.com/search?find_loc=St.+John's+Newfoundland
example.com/search?find_loc=St.+John's+Newfoundland&start=10
example.com/search?find_loc=St.+John's+Newfoundland&start=20
example.com/search?find_loc=St.+John's+Newfoundland&start=30
example.com/search?find_loc=St.+John's+Newfoundland&start=40
example.com/search?find_loc=St.+John's+Newfoundland&start=50
example.com/search?find_loc=St.+John's+Newfoundland&start=60
example.com/search?find_loc=St.+John's+Newfoundland&start=70
example.com/search?find_loc=St.+John's+Newfoundland&start=80
example.com/search?find_loc=St.+John's+Newfoundland&start=90
example.com/search?find_loc=St.+John's+Newfoundland&start=100
example.com/search?find_loc=St.+John's+Newfoundland&start=110
example.com/search?find_loc=St.+John's+Newfoundland&start=120
example.com/search?find_loc=St.+John's+Newfoundland&start=130
example.com/search?find_loc=St.+John's+Newfoundland&start=140
example.com/search?find_loc=St.+John's+Newfoundland&start=150
example.com/search?find_loc=St.+John's+Newfoundland&start=160
example.com/search?find_loc=St.+John's+Newfoundland&start=170
example.com/search?find_loc=St.+John's+Newfoundland&start=180
example.com/search?find_loc=St.+John's+Newfoundland&start=190
example.com/search?find_loc=St.+John's+Newfoundland&start=200
example.com/search?find_loc=St.+John's+Newfoundland&start=210
example.com/search?find_loc=St.+John's+Newfoundland&start=220
example.com/search?find_loc=St.+John's+Newfoundland&start=230
example.com/search?find_loc=St.+John's+Newfoundland&start=240
example.com/search?find_loc=St.+John's+Newfoundland&start=250

Answer 1

I'd likely concatenate in the number using the &. Consider this: A1= the beginning of the url (example.com/search?find_loc=St.+John's+Newfoundland&start=) B1 = end of the url ( example.com/search?) Starting in A2, list all the numbers you need (10, 20, 30, etc.. drag the little formula handle to copy them bad boys). With this set up you can use the following formula in B2 and drag the fill handle to copy it all the way down.

=$A$1 & A2 & $B$1

If you need a clickable hyperlink, wrap the B2 formula in a HYPERLINK before copying down.

=HYPERLINK($A$1 & A2 & $B$1)

Answer 2

Code blocks help a bit for legibility.

example.com/search?find_loc=St.+John's+Newfoundland 
example.com/search?find_loc=St.+John's+Newfoundland&start=10

...

example.com/search?find_loc=St.+John's+Newfoundland&start=240 
example.com/search?find_loc=St.+John's+Newfoundland&start=250

I would use the =CONCATENATE() function.

In Cell A1, or whatever cell you are using, place:

example.com/search?find_loc=St.+John's+Newfoundland 

In cell B1:

&start=

And in column C, fill the column with the range 10..250. Then concatenate the rows in column D:

=CONCATENATE($A$1, $B$1, $C1)

Copy that formula down the column, and you should be able to display your data fairly easily.

Answer 3

I’ll assume that you have your counts in Column A and your base URLs in Column B, and you want your constructed URLs in Column D, all starting in Row 1.  This is fairly easy with “helper columns”.  I’ll use Columns G and H; you can use any two columns you want (e.g., Y and Z), and you can hide them once you get this working.

Enter the following:

• G1 → 1
• H1 → 0
• G2 → =IF(G1<0, -1, IF(H1+1<INDEX(A:A, G1), G1, IF(INDEX(A:A, G1+1)="", -1, G1+1)))
• H2 → =IF(G2<0, -1, IF(H1+1<INDEX(A:A, G1), H1+1, 0))
• D1 → =IF(G1<0, "", INDEX(B:B, G1) & IF(H1>0, "&start=" & H1*10, ""))

Select cells G2 and H2 and drag/fill down.  Select cell D1 and drag/fill down.

This will treat a 0 in Column A as if it were a 1.

Answer 4

Here's an implementation of the approach in my comment to your question. Cell A2 contains the base prefix that's common to all of the URLs that you want to generate. Cells B2, B3, B4,... are the locations that you want to generate. C2, C3, C4,... are the number of strings to generate for each location.

Each location will be generated in its own column. The formula in E1 is:

=IF(ROW()=1,$A$2&$B$2,IF(ROW()<=$C$2,$A$2&$B$2&"&start="&(ROW()-1)*10,""))

E2:

=IF(ROW()=1,$A$2&$B$3,IF(ROW()<=$C$3,$A$2&$B$3&"&start="&(ROW()-1)*10,""))

E3:

=IF(ROW()=1,$A$2&$B$4,IF(ROW()<=$C$4,$A$2&$B$4&"&start="&(ROW()-1)*10,""))

The "B" and "C" cells are moving down by one each time. Continue the pattern as you move to the right, and then fill the formulas down.

If you have hundreds of locations and don't want to edit each formula, then let me know. The whole thing can be done with a single formula, but it will require the use of INDIRECT() which I try to stay away from. It's one of the volatile functions so must be used judiciously.