formula required
 

Hi,

I have a data in column A for 800 lines.

Column A Column B Column C Column D Column E
123 3 123-002 123-003 123-004
432 2 432-002 432-003

As defined above, the data what I have in Column A should be counted the
number of times in Column B and it has to repeated so many times in different
columns with the sequence of 002, 003 004 etc., The sequence should start
only from 002 and should continue to the number of times it has to be
repeated.

Please help me out.

Regards,
Igneshwara Reddy.

formula required
 

On Wed, 24 Dec 2008 15:16:01 -0800, Igneshwara reddy
wrote:

Hi,

I have a data in column A for 800 lines.

Column A Column B Column C Column D Column E
123 3 123-002 123-003 123-004
432 2 432-002 432-003

As defined above, the data what I have in Column A should be counted the
number of times in Column B and it has to repeated so many times in different
columns with the sequence of 002, 003 004 etc., The sequence should start
only from 002 and should continue to the number of times it has to be
repeated.

Please help me out.

Regards,
Igneshwara Reddy.



Try the following formula in cell C2

=IF($B2COLUMN()-2,$A2&"-"&RIGHT("000"&COLUMN()-1,3),"")

Copy cell C2 as far to the right as needed to cover the maximum times.
Then copy down from row 2 to row 801.

Hope this helps / Lars-Åke

formula required
 

Believe it's meant to be: =IF($B2= ...
in Lars' expression:
=IF($B2COLUMN()-2,$A2&"-"&RIGHT("000"&COLUMN()-1,3),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

formula required
 

Actually not, as I believe the OP want the xxx-002 to appear in
column C and so on.
Maybe it is confusing to have COLUMN()-2 in one place and COLUMN()-1
in another place. But if you change to = then you also have to
change the COLUMN()-2 to COLUMN()-1. I think...

/ Lars-Åke


On Thu, 25 Dec 2008 02:22:00 -0800, Max wrote:

Believe it's meant to be: =IF($B2= ...
in Lars' expression:
=IF($B2COLUMN()-2,$A2&"-"&RIGHT("000"&COLUMN()-1,3),"")

formula required
 

But I got the exact results that the OP indicated s/he wanted with that tweak
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---
"Lars-Ã…ke Aspelin" wrote:
Actually not, as I believe the OP want the xxx-002 to appear in
column C and so on.
Maybe it is confusing to have COLUMN()-2 in one place and COLUMN()-1
in another place. But if you change to = then you also have to
change the COLUMN()-2 to COLUMN()-1. I think...

/ Lars-Ã…ke

formula required
 

On Wed, 24 Dec 2008 15:16:01 -0800, Igneshwara reddy
wrote:

Hi,

I have a data in column A for 800 lines.

Column A Column B Column C Column D Column E
123 3 123-002 123-003 123-004
432 2 432-002 432-003

As defined above, the data what I have in Column A should be counted the
number of times in Column B and it has to repeated so many times in different
columns with the sequence of 002, 003 004 etc., The sequence should start
only from 002 and should continue to the number of times it has to be
repeated.

Please help me out.

Regards,
Igneshwara Reddy.


If your data starts in row 1, then:

C1: =IF(COLUMNS($A:A)$B1,"",$A1&TEXT(COLUMNS($A:A)+1, "\-000"))

Fill right as far as required. The select C1:Cn and fill Down as far as
required.
--ron

formula required
 

Yes, you are right. The tweak is indeed needed in order to get the
correct number of repetitions, Thanks for the correction Max.

/ Lars-Åke

On Thu, 25 Dec 2008 02:41:00 -0800, Max wrote:

But I got the exact results that the OP indicated s/he wanted with that tweak

formula required
 

On Dec 25, 11:22*pm, Max wrote:
Believe it's meant to be: =IF($B2= ...
in Lars' expression: =IF($B2COLUMN()-2,$A2&"-"&RIGHT("000"&COLUMN()-1,3),"")

Or $B2COLUMN()-3

Phil

formula required
 

On Thu, 25 Dec 2008 07:27:27 -0500, Ron Rosenfeld
wrote:

On Wed, 24 Dec 2008 15:16:01 -0800, Igneshwara reddy
wrote:

Hi,

I have a data in column A for 800 lines.

Column A Column B Column C Column D Column E
123 3 123-002 123-003 123-004
432 2 432-002 432-003

As defined above, the data what I have in Column A should be counted the
number of times in Column B and it has to repeated so many times in different
columns with the sequence of 002, 003 004 etc., The sequence should start
only from 002 and should continue to the number of times it has to be
repeated.

Please help me out.

Regards,
Igneshwara Reddy.


If your data starts in row 1, then:

C1: =IF(COLUMNS($A:A)$B1,"",$A1&TEXT(COLUMNS($A:A)+1, "\-000"))

Fill right as far as required. The select C1:Cn and fill Down as far as
required.
--ron

One note:

The use of COLUMNS($A:A) vs using COLUMN() as a method to generate the serial
numbers will behave differently if you should Insert a column.

e.g:

=IF(COLUMNS($A:A)$B1,"",$A1&TEXT(COLUMNS($A:A)+1, "\-000"))
or
=IF((COLUMN()-2)$B1,"",$A1&TEXT(COLUMN()-1,"\-000"))

Using the first method, the result of the calculation will be unchanged; using
the second, the result of the calculation will change depending on the column
number after the insertion.

Which is "better" depends on your specifications for what should happen in this
instance.
--ron