Output text from numbers

[LiAD]

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

 

[Gary''s Student]

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub

 

[LiAD]

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

 

[LiAD]

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

 

[Gary''s Student]

Worry not! Through persistence we will prevail. Begin by erasing the old macro>

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Just make sure that your input table is located in the proper rows and
columns.

 

[LiAD]

Sounds simple...but unfortunately it doesn't work for me. Thats actually
what I did when i got the compile error. One thought I have a non english
computer so when i use excel i need to use ; instead of , in formulas. Could
this be a problem here?

 

[Gary''s Student]

It might be a problem. I don't know.

 

[LiAD]

sorry stupid mistake on my part i was copying all the > > as well as the
routine.

works a lot better now!
thanks for your help