Urgent - Function needed to done this

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
Hi,

I have problem Please look at the picture below:



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.



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
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
Can you post your data in a format that can be copied into excel please.
 

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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.

Can you post your data in a format that can be copied into excel please.
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
this will make your example, but I don't know what you mean about the zip codes.
Excel Workbook
B
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
 

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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



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
 

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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

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
 

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
It seems @konew1 Is not available from the last 3 hours. Can any other person here will help me? Thanks
 

konew1

Well-known Member
Joined
Oct 17, 2007
Messages
2,287
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
10
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:

Abbas999

Board Regular
Joined
Jan 4, 2015
Messages
102
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.

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
 

Forum statistics

Threads
1,081,729
Messages
5,360,930
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top