Excel function for Script

[Gentile Romano]

Dear all;

 

 

How can I convert this in Excel to write script programe

(The above 3 columns to below one column) as shown in the Image

 

 

thanks

Romano

[YZ]

How do you feel about visual basic code in the backend of Excel? There is not an Excel formula for this, but it is possible as amn Excel macro. I can recommend a spreadsheet forum if that solution is agreeable to you.

[KevUK]

Hi

 

Try the following in E9 and copy down

 

=IFERROR(INDEX($A$1:$C$5,CEILING(ROW(),3)/3,MOD(ROWS($1:1)-1,3)+1),"")

 

I take it that you are using Excel 2007 or >, If not you will have to remove the IFERROR and use IF & ISERROR

[steven-g]

I use this variation but starting at row 1

=INDIRECT(IF(MOD(ROW(),3)=0,"C",IF(MOD(ROW(),3=2,"B","A")) & IF(MOD(ROW(),3)=0,INTEGER(ROW()/3),INTEGER(ROW()/3+1))

PS this might throw up an error as I use a dutch language version of excel, so I first have to find the correct function in english, convert it to dutch, try the formula and then translate everything back to English including the command seperator which always throws me off

[KevUK]

steven-g

 

The problem with the INDIRECT function is that it is volatile

[steven-g]

I only use that sort of formula for quick tests and one off ideas and never had a problem with it. For larger ranges and more permanet spreadsheets I would prefer to go with the idea YZ offered of using a VBA solution, as you can also add in the ability to directly create a script file that can be run externally to Excel or called from an Autocad macro.

 

EDIT

Another method is to concatenate the cells with a space between each command so that the resulting cells can be copied and pasted to Autocad and the spaces ensure each command is carried out correctly, the formulas in that case would only take up as many rows as there is data

Edited September 22, 2014 by steven-g