Please help build array with constants

[suzbee]

Hello to the board,

I'm still using Excel 2003. I cannot seem to find any forum entries, or
google links, that address my question specifically enough that I can
understand it.

I am building a small inventory database in Excel. I am trying to build an
array that will take the dollar amount of an item's cost from a cell in one
column and generate an alphabetical cost code in a cell in another column.
This code (along with other data) will eventually be printed on an inventory
sticker to be placed on an item, so that only a clerk who knows the code can
see the cost of the item and determine how much bargaining room they have
for making a profitable sale.

For example, 1=A, 2=B, 3=C, etc. Is it possible to build an array so that if
I enter an amount like $1,359 it will take those numbers and automatically
generate an alphabetical code in the next column? In this example, the
item's cost code would be ACEJ.

If that is possible, could someone please show me an actual example that I
might substitute my code letters into? Warning: I understand this so little,
that I don't want you to tell me "how to build a watch." I just want to know
"what time it is!"

(I learned about building arrays in BASIC and COBOL programming classes long
ago, and did okay at it, but don't know if something like that will work in
Excel. From what I remember, I am concerned that cells will have to contain
only one numerical digit each to make this work. Acccckkkk. Never mind!)

If it is not possible, do you have any other suggestions for automatically
generating an alphabetical cost code?

Thank you so very much!
suzbee

 

[suzbee]

OOPS, in my example, cost code would be ACEI. Sorry.

 

[T. Valko]

If you're basing the code off of the the digits in the cost/price then
you've forgotten to include 0.

Here's one way...

Assuming the costs/prices are integers (whole numbers) only!

If you can download and install the free add-in Morefunc.xll from:

http://xcell05.free.fr/morefunc/english/index.htm

Alternate download site:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Create this 2 column table:

...........J..........K
1.......0..........A
2.......1..........B
3.......2..........C
4.......3..........D
5.......4..........E
6.......5..........F
...
10.....9..........J

Then:

A1 = 1359

Enter this array formula** in B1:

=MCONCAT(LOOKUP(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),J$1:K$10))

Result = BDFJ

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Gord Dibben]

Function CharDigit(Cell As String) As String
Dim L As Integer, I As Integer
Dim sTemp As String
sTemp = ""
L = Len(Cell)
For I = 1 To L
sTemp = sTemp & Chr(Mid(Cell, I, 1) + 64)
Next I
CharDigit = sTemp
End Function

=CharDigit(A1)

Returns ABCD from 1234

Returns ACEI from 1359


Gord Dibben MS Excel MVP

 

[JoeU2004]

I think it would be easier to do in VB.


Function costCode(amt As String) As String
'characters in rep replace 0,1,2,...,9 respectively
Const rep As String * 10 = "JABCDEFGHI"
Dim c As String * 1, I As Integer
For I = 1 To Len(amt)
c = Mid(amt, I, 1)
'ignore non-digit characters (e.g. ".")
If "0" <= c And c <= "9" Then
costCode = costCode & Mid(rep, c - "0" + 1, 1)
End If
Next I
End Function


To enter this:

1. Press alt+F11 to open the VB window.

2. In the VB window, click Insert > Module. That should open a VB editing
pane on the right.

3. Copy-and-paste the text of the above function into the VB editing pane.

To use the function in the Excel worksheet, if A1 contains the cost in
dollars and cents, enter the following in a cell where you want the
translated cost code:

=costCode(A1)

If A1 contains $1,234,567,890, costCode(A1) returns ABCDEFGHIJ.

Note: If A1 contains 12.34, costCode(A1) returns ABCD -- no indication
dollars and cents. To ensure that works even with 12.00, use the formula
=costCode(text(A1,"0.00")). This necessary only if costs are dollars and
cents, not just dollar amounts.

Alternatively, if you would prefer to leave the "." in the cost code, post
back with that information.

Finally, in order save and re-open the workbook with macros, it would be
prudent to change macro security. Click Tools > Macro > Security > Medium >
OK.


----- original message -----

 

[suzbee]

Biff, Gord, Joel,

Thanks a million! Let me work through these alternatives and see what goes.
I bet I'll have more questions.

Thank you for you time and brain power.
suzbee