How to duplicate cells while increasing their number content

**aussieinus** · 10-26-2010, 02:57 PM

Hi,
I apologize in advance that I am not proficient with computer jargon so Iwill try and explain what I need to do by figures.

I need to generate a list like this in one column:

xxxxx-xx10
xxxxx-xx10
xxxxx-xx11
xxxxx-xx11
xxxxx-xx12
xxxxx-xx12 and so on

In a separate column I need to change the numbers to this:

xxxxx-xx10-P
xxxxx-xx10-C
xxxxx-xx11-P
xxxxx-xx11-C
xxxxx-xx12-P
xxxxx-xx12-C and so on

Is this a quick way to do this please? I am currently typing each entry - not very time efficient!

I will greatly appreciate any suggestions.

**mrice** · 10-26-2010, 03:09 PM

What should happen after you reach 99 (or will you not get that far). ?

**mrice** · 10-26-2010, 03:17 PM

The attachment will give you the values up to 99 - specify the number that you want in the yellow box and hit the button.

**martindwilson** · 10-26-2010, 03:22 PM

with functions see attached
or even version 2 if right hand xx are to be reduced as number of digits increase

**aussieinus** · 11-04-2010, 08:00 PM

Thank you for your question. The numbers continue into the 1000s.

Sorry for the delay in the response - being a newbie I thought I would receive an email when a response was posted.

**aussieinus** · 11-04-2010, 08:02 PM

Thank you very much for the sheets. I will use them and let you know how I go.

I greatly appreciate everyone's help.

**martindwilson** · 11-04-2010, 08:30 PM

just a thought replace formula in col e with
=IF(MOD(ROW(A1),2),LEFT($C$1,LEN($C$1)-LEN(D1))&D1&"-P",LEFT($C$1,LEN($C$1)-LEN(D1))&D1&"-C") no need for col f then

**aussieinus** · 11-05-2010, 02:14 PM

Thank you. The formulas worked very nicely. I was so excited I called my boss immediately. This is truly an art/skill that is invaluable!

To expand on that problem the last thing I need to do is very similar but I don't think the true/false format will work?
I need to generate a list like this in one column:

xxxxx-xx10
xxxxx-xx10
xxxxx-xx10
xxxxx-xx10
xxxxx-xx11
xxxxx-xx11
xxxxx-xx11
xxxxx-xx11 and so on

In a separate column I need to change the numbers to this:

xxxxx-xx10-CP
xxxxx-xx10-CV
xxxxx-xx10-OP
xxxxx-xx10-OV
xxxxx-xx11-CP
xxxxx-xx11-CV
xxxxx-xx11-OP
xxxxx-xx11-OV and so on

**martindwilson** · 11-05-2010, 07:40 PM

same sort of principle

**aussieinus** · 11-08-2010, 01:05 PM

Awesome!!!!! Thank you so much!! Is that also a true/false query?

**martindwilson** · 11-08-2010, 02:36 PM

nope it used choose. chose returns things in order from 1 thro 29
i just made the mod of the row = 0,1,2 or 3 using MOD(ROW(A1),4) and added 1 to it to give 1,2,3,or 4
and put your required text in the right place essentially =choose(value,"OV","CP","CV","OP") so 2 would return second term "CP"

How to duplicate cells while increasing their number content

LinkBack

Thread Tools

Rate This Thread

Display

How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Re: How to duplicate cells while increasing their number content

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions