Urgent - Function needed to done this

A

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
Hi,

I have problem Please look at the picture below:

2cehy8p.png


In this picture you can each url ending with pagenum=1

There are 29670 rows in excel sheet and all ending with pagenum=1

And also you can see the zip code which is "Ab10" for all 29670 rows.

WHAT I AM LOOKING FOR A FUNCTION THAT WILL REPLACE EACH ZIP CODE AFTER 10 ROWS AND add PAGENUM=1-10

For example First zip code is AB10 so i want first 10 rows like this and then next zip code is "ab11" and so on for all zip codes. Check image below for better understanding.

29xx0fp.png


So the gap is 10 rows. After each 10 rows the ab10 should be replaced with next zip code and the pagenum should add 10 pages.
E.g
Pagenum=1
Pagenum=2
Pagenum=3
and so on.. for each 10 rows.

I have a list of zip codes already. Please Help me if it is possible to do. Thank you in advance
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
It's Simple - Just copy this line to excel
&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
and fill it with auto fill for 29670 rows.

konew1 said:
Can you post your data in a format that can be copied into excel please.
Click to expand...
 
Upvote 0
this will make your example, but I don't know what you mean about the zip codes.
Excel Workbook
B
1&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
2&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
3&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
4&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
5&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
6&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
7&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
8&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8
9&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=9
10&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=10
11&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
12&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
13&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
14&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
15&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
16&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
17&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
18&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8
Sheet5
 
Upvote 0
It seems formula is working great at your side. When i try to do this at my side it does correct with pagenum=1-10 but it also add number to "AB10" don't know why.
I am sending you the sheet with List of zip codes and the original sheet. Please try it yourself and let me how you done it.
I want the zip codes to replaced each after 10 rows. So first zip code is AB10 which should be as it is for first 10 rows but after that you can see the next zip code in the zip codes sheet.
So 1 zip code - 10 Page numbers.
next zip code and 10 page numbers.
Download the sheet here - https://www.dropbox.com/s/hkr7ofj6xucyipl/Test Sheet.xlsx?dl=0



konew1 said:
this will make your example, but I don't know what you mean about the zip codes.
Sheet5

B
1&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
2&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
3&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
4&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
5&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
6&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
7&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
8&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8
9&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=9
10&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=10
11&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
12&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
13&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
14&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
15&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
16&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
17&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
18&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=LEFT(A1,27)&ROUNDDOWN(ROW()/10,0)+10&MID(A1,30,LEN(A1)-30)&MOD(ROW(),10)+IF(MOD(ROW(),10)=0,10,0)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Hey @konew1 AS you can in your example it is happening same on both sides. you can see pagenum=1 works but it also affects the "Ab10" you can see after each Pagenum=10 "Ab10" converts to "Ab11" and it fills automatically AB12, Ab13 and so on... The function should only change pagenum=1 not the zip code. Zip code part is different than pagenum=1.

For example
Column A contain urls and Column "C" contains the zip codes.
I want each zip code in 10 urls.
So The function should replace C1 zip code with A1:A10 cells zip code and add pagenum=1 to 10.
Then again replace C2 zip code with A11:A20 Cells zip cdoe and add pagenum=1 to 10.
and repeat. Hope it clears now? Thank you in advance

konew1 said:
this will make your example, but I don't know what you mean about the zip codes.
Sheet5

B
1&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
2&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
3&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
4&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
5&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
6&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
7&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
8&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8
9&keywords=clubs&location=Ab10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=9
10&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=10
11&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
12&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
13&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
14&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
15&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
16&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
17&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=7
18&keywords=clubs&location=Ab11&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=8

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:993px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=LEFT(A1,27)&ROUNDDOWN(ROW()/10,0)+10&MID(A1,30,LEN(A1)-30)&MOD(ROW(),10)+IF(MOD(ROW(),10)=0,10,0)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Sorry, had to sleep.
Rather than having 60,000 identical lines and layers of text formulas, I have split the static data onto sheet3

Excel Workbook
A
3keywords=clubs&location=
4Ab10
5&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=
61
Sheet3



Then on sheet 2 put it all together, linking back to your sheet of zip codes. NOTE: this is starting in row 10 and copy down, not row 1.

Excel Workbook
A
10keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
11keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
12keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
13keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
14keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
15keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6
Sheet2




Here is what it gives for row 358:362 looks correct to me
Excel Workbook
A
358keywords=clubs&location=AL1&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=9
359keywords=clubs&location=AL1&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=10
360keywords=clubs&location=AL10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
361keywords=clubs&location=AL10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
Sheet2
 
Last edited:
Upvote 0
Sorry @konew1 I am not able to execute it. I am newbie in excel. I apologize but i need a step by step guide to perform this task. Please Tell me how to perform this in my sheet. and yes it looks correct to me.

konew1 said:
Sorry, had to sleep.
Rather than having 60,000 identical lines and layers of text formulas, I have split the static data onto sheet3

Sheet3

*A
3keywords=clubs&location=
4Ab10
5&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=
61

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Then on sheet 2 put it all together, linking back to your sheet of zip codes. NOTE: this is starting in row 10 and copy down, not row 1.

Sheet2

*A
10keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
11keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2
12keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=3
13keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=4
14keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=5
15keywords=clubs&location=AB10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=6

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
A10=Sheet3!$A$3&INDIRECT("'zip codes'!A"&ROUNDDOWN(ROW()/10,0))&Sheet3!$A$5&MOD(ROW(),10)+1

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4



Here is what it gives for row 358:362 looks correct to me
Sheet2

A
358keywords=clubs&location=AL1&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=9
359keywords=clubs&location=AL1&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=10
360keywords=clubs&location=AL10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=1
361keywords=clubs&location=AL10&searchType=manualexpansion&autocomplete=kw_recent&scrambleSeed=981534963&pageNum=2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Vlookup() and Index(Match()) returning N/A, even though the lookup value exists in the range and is identical to that value within the range.
Replies
6
Views
1K
nemmi69
nemmi69
D
  • Question
Excel USPS Zip Code Chart
Replies
3
Views
191
Scott Huish
Scott Huish
B
  • Solved
need help finding cells with different values that contain the same mailing address
Replies
6
Views
554
Micron
M
G
  • Question
Formula to count with multiple criteria and exclude from a separate column, then sort by count in one cell with spill
Replies
0
Views
340
Greenbehindthecells
G
C
  • Solved
Ladder League Workbook, replace one row with another
Replies
2
Views
237
CordingBags
C

Forum statistics

Threads
1,214,823
Messages
6,121,779
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top