Adding a number to a large group of data

Hi all,
I am looking for a way to add a number to a large group of data. I have in the past used the "Paste Special" to achieve this however the data group is so large and the amount of time I am updating means I need a quicker way to do this.

An example of what I am looking to achieve -

The data:

Blue cars: $100 $200 $300 $400 $500
Red cars: $100 $200 $300 $400 $500

I wish to add $5 to the price of all the red cars and $6 to the price of the blue cars. Ideally I would like to type "5" or "6" into a linked cell so it updates all the car prices accordingly.

Any ideas? Thanks all in advance.

Regards,

Ryan

Hi,

Easiest way is to sort the list so that all blue car rows appear together. If you want to retain the original order afterwards, use a helper column and number it 1,2,3 with a Series fill before the sort.

Now put the number 5 in a spare cell somewhere, and Edit Copy it. Now select all the blue car values and do an Edit PasteSpecial and choose the Add option and OK. This will add $5 to each of the selected values.

Now if necessary to get the data back to its original order, resort it based on the helper column.

HTH

Hi Richard,
Thank you for your message. I would prefer to find a different way to update the number as the spreadsheet is so large it takes a heck of a long time to update it. I would prefer to spend some time setting up the spreadsheet so I can update one or two cells to update the whole sheet.

Any other ideas?

Cheers

Ryan

Hi Ryan,

Perhaps you misunderstood. When I said select all the blue car cells, I didn't mean one by one, and I can see how this might have been confusing, I should have said select all the blue car rows in one go. i.e. if you sort the rows and you have 1000 containing blue car details, select all the rows, or all the relevant columns, and do the edit paste special add in one hit. Shouldn't take more that a few seconds.

If necessary, and if you don't want to update all the numbers on the blue car rows, take a copy of all the blue car rows to another sheet, then just select the whole columns which contain the cells that you want to update. Then just copy these updated rows back to the original sheet.

If it's something you're considering doing on a regular basis then a macro which did just this task would be simple to create, but if it's just a one off task, it's probably not worth the effort - just do it manually, in, as I say, a few moments.

HTH