Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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),"") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Formula Help Required | Excel Discussion (Misc queries) | |||
FORMULA REQUIRED | Excel Discussion (Misc queries) | |||
Formula help required!!! | Excel Worksheet Functions | |||
Formula Required | Excel Worksheet Functions |