Solved Remove Letters From List After Use

April 8, 2019 at 12:55:36
Specs: Windows 7
I have alphabetic numbers, but different quantities of each letter. If I use the letter in a message I would like to take the letter away from the number available.

See More: Remove Letters From List After Use

Report •

✔ Best Answer
April 8, 2019 at 15:44:16
Razor2.3's fine formula will work, first in Column C, then in Column D, then in Column E. Basically moving right if you want to update the count after each "new" message is entered in A1.

If you want to keep the count in Column B, his formula can be re-written to work in VBA and always write the "new" answer into Column B:

Sub SubtractLetterCount()

 For nxtLtr = 4 To 29
   Range("B" & nxtLtr) = _
      Range("B" & nxtLtr) - (Len(Range("A1")) - Len(Replace(UCase(Range("A1")), Range("A" & nxtLtr), "")))
 Next

End Sub

Obviously, with more details as to your overall requirements, we could probably enhance both options.


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
April 8, 2019 at 13:08:01
First, a posting tip:

When posting in a Help forum such as this, please try to use a Subject Line that tells us something about your question. If everyone used a generic Subject Line such as "How To Write A formula" we wouldn't be able to tell one question from another and the Archives would essentially be useless.

I have edited the subject line of your post to show you what I mean. Scan the list of questions in this forum to see the types of Subject Lines that others have used.

As for your question, you are going to have to have supply more details.

re: "I have alphabetic numbers..."

What is an "alphabetic number"?

Where do you "have them"? We don't even know what application you are using. Excel? Word? Open Office? Something else?

re: "If I use the letter in a message"

What kind of message?

What do you by "use the letter"? Any letter? All letters used?

Please keep in mind that we can't see what you are doing from where we are sitting. You need to explain your requirements in enough detail that we can figure out what you are trying to do. What does your input look like and what do you want the output to be.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
April 8, 2019 at 13:18:16
I'm sorry about the poor detail. I have all the letters of the alphabet, but I have different quantities of each letter. If I use the letters in a message I would like to deduct the letter from the number on hand.

Example: Happy Birthday!

Letter Quantity
A 5
B 3
C 3
D 4
E 5
F 2
G 3
H 5
I 4
J 2
K 2
L 4
M 4
N 4
O 4
P 3
Q 1
R 4
S 5
T 3
U 2
V 2
W 2
X 1
Y 5
Z 2


Report •

#3
April 8, 2019 at 14:46:26
I don't think that you are going to be able to accomplish your goal with a formula. A formula can only "change" the value of the cell in which it resides. It doesn't really "change" the value, it returns the result of the formula.

It is probably going to take a macro (VBA code) to search the list of letters based on the phrase, letter by letter. When a match is found, subtract 1 from the value next to the letter.

Before we could offer any code, we will need more specifics, such as where your data is located. Click on the How-To link at the bottom of this post and read the instructions on how to post example data in this forum. Please be sure to include Row numbers and Column letters. The code will need that.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
April 8, 2019 at 15:02:34
Maybe use something like this as a base?
=B4 - (LEN($A$1) - LEN(SUBSTITUTE(UPPER($A$1), A4, "")))

How To Ask Questions The Smart Way


Report •

#5
April 8, 2019 at 15:44:16
✔ Best Answer
Razor2.3's fine formula will work, first in Column C, then in Column D, then in Column E. Basically moving right if you want to update the count after each "new" message is entered in A1.

If you want to keep the count in Column B, his formula can be re-written to work in VBA and always write the "new" answer into Column B:

Sub SubtractLetterCount()

 For nxtLtr = 4 To 29
   Range("B" & nxtLtr) = _
      Range("B" & nxtLtr) - (Len(Range("A1")) - Len(Replace(UCase(Range("A1")), Range("A" & nxtLtr), "")))
 Next

End Sub

Obviously, with more details as to your overall requirements, we could probably enhance both options.


How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Ask Question