Find and Replace

[pbf98]

I have a spread sheet with over 3360 rows and in column a I have a number that goes up by 1 every 7 rows.
So The column looks like:

1
1
1
1
1
1
1
2
2
2
2
2
2
2
3...


I have to change all those numbers to match a new set of numbers. So for an actual example from my spread sheet 145802 has been changed to 146522, 145803 changed to 146523, and so on, each one just add 1 more
Currently the way I am doing it is using find and replace. This way I have to do a find a replace 480 times but if there is another more efficient way I would appreciate the advice a lot! As it will save me a lot of time down the line because I will be doing this same process 4 more times

Thanks for any help you can offer, and if something needs better explaining, I will do my best to explain a little bit better.
Thank you!

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pbf98]

Hope this is what you were looking for haha

Book2.xlsx

[Pepe Le Mokko]

If I understand correctly you have to add a value to all cells in a column?
If so, enter that value in an empty cell somewhere - Copy It - Select your range - Paste special - Add - OK

[K m]

Assume you start your list starting with cell A1, enter this formula in cell A2 and copy down:
=IF(INT((ROW()-1)/7)=(ROW()-1)/7,A1+1,A1)

[pbf98]

What I am trying to do is change the cells in the column to match numbers from a corresponding column.

With the fact that it each row only increments once (to me) its similar to filling in three cells in a column with consecutive numbers and dragging the column down to populate all of them. The only difference being that instead of it incrementing every row it increments after 7 rows

[K m]

In your example why not add 720 to column A
For example in cell C2 the formula would be:
=A2+720

Copy the formula down

[Pepe Le Mokko]

In your example why not add 720 to column A
For example in cell C2 the formula would be:
=A2+720

Copy the formula down

The same as my suggestion but needs a column more

[pbf98]

I believe I see what you are getting at by just adding 720 to each row it gets me to what the new value is.

Does copy the formula down mean any thing special like an easy way to copy it? I do see this saving time over the find and replace.
Thanks for the advice given so far

[K m]

In cell A2 put in this formula:
=145802+720
In cell A3 put in this formula and copy down
=IF(INT((ROW()-2)/7)=(ROW()-2)/7,A2+1,A2)

Try this spreadsheet:

[Pepe Le Mokko]

Just apply my suggestion post #4 (using 720 as a value). takes about 15 secs

[pbf98]

under paste special there is no option for add. Is there a way to add this to it? When I click paste special all that appears for choices is: Unicode Text and Text

Sorry was away from the computer for about an hour there, and just working through what has been posted

[K m]

Not sure what you are doing wrong but it works.

Just select a cell somewhere on your spreadsheet that has the number 720.
Right mouse click - select copy
Highlight the column entire column
Right mouse click - Paste Special/Values/Add
and press enter

[K m]

Not sure what you are doing wrong but it works.

Just select a cell somewhere on your spreadsheet that has the number 720.
Right mouse click - select copy
Highlight the column entire column
Right mouse click - Paste Special/Values/Add
and press enter

[pbf98]

Here is what I am seeing as far as paste special, it looks completely different from all the examples I have looked at

excel.jpg
excel2.jpg

[pbf98]

Not sure what I did, but I did get the special paste to work, and the adding 720 worked, thanks to both for your help

[pbf98]

double post can mod delete

[Pepe Le Mokko]

Your first picture shows that you copied the value in another workbook than the destination one. You should copy in the same wbk as the destination wbk
The second is OK

[crabiajohan]

By using Clrt + F we can find any charter or word or etc...
Travelodge International Drive Hotel

[NeedForExcel]

Hey All!!


I have a sheet of 4500 Rows where Ive already used a formula like below -

=VLOOKUP(CONCATENATE(A18,B18),C4:D16,2,0)

I want to know if there is a way to add "=IFERROR" function in front of the formulas Ive already used through out the sheet. I want the output to be -

=IFERROR(VLOOKUP(CONCATENATE(A18,B18),C4:D16,2,0),"Wrong Input")

Is there a way we can Find and Replace formulas in such a case??

Thank You,

Deep

[NeedForExcel]

Any Help on the above Post??

Thank You,

Deep