formula required

[Igneshwara reddy]

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.

 

[Lars-Åke Aspelin]

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($B2>COLUMN()-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

 

[Max]

Believe it's meant to be: =IF($B2>= ...
in Lars' expression:

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

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---

 

[Lars-Åke Aspelin]

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

 

[Max]

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

 

[Ron Rosenfeld]

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

 

[Lars-Åke Aspelin]

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

 

[Ron Rosenfeld]

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