Eliminate manual typing

[dp]

I receive a report with a 1 or 2 alpha code/description in col A., such as:
A SERVICE
AB SERVICE
AL FEE
AM SERVICE
B SERVICE
C SERVICE
DI FEE

I insert a column and manually type a single digit code (1 to 7) so I can
use Sort by that digit, and use Data, Subtotal.
1 A SERVICE
2 AB SERVICE
6 AL FEE
6 AM SERVICE
1 B SERVICE
2 C SERVICE
5 DI FEE

Is there a way to eliminate the manual typing of the numbers 1-7? There are
about 70 rows. I am Using excel 2003, Windows 2000 Professional

Hope I made this clear. Just learning. TIA

 

[Sherin]

A simple but practical solution would be to:-

a. Type 1 and 2 in first two rows
b. Block the above two cells
c. Double click on the small box appearing in the right hand corner in the
bottom part of the selection.

Next option would be to:-

a. Type 1 in the first row (say in A2)
b. Type the formula A2+1 in the next row (i.e. in A3)
c. Copy the cell A3
d. Block and select the remaining cells and paste

Hope it helps.

Sherin

 

[dp]

Thanks for your reply.
Tried what you said but the problem I have is that all the "A's" are not #1.
A, B = #1
AB, C = #2
Thanks again

 

[Sandy Mann]

What is the logic behind how the 1 - 7 codes are entered?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[dp]

My manager has identified services/fees into 7 categories. No logic that I
can see! !Expanding on my original example AB, C, EA, EM, HT ... all = number
2. In fact there are 12 letter combinations that = 2.

I will only receive this report quarterly so I could continue to add the
numbers manually. Thanks

 

[dp]

No logic that I can see. The information following the two letters are
basically the same too, either services or fees. The only difference in the
whole report is the two letter code and the number my manger has assigned.
This is only a quarterly report so I can continue to manually.

Seems like just as much work, but can I put the letters in a separate column
"b" then in cell a2 use an IF? IF(B2=ab or c or em, then put 2 in cell a2)

Thanks to you both

 

[Sandy Mann]

I set all the letters down Column G and the corresponding codes in Column H.
Then, assuming that the letters are in Column A by themselves, and do not
have leading/trailing spaces I used the formula:

=VLOOKUP(A2,$G$1:$H$133,2,FALSE) to return the appropriate code.

If the letters do/could have spaces around them then try:

=VLOOKUP(TRIM(A2),$G$1:$H$133,2,FALSE)

If Services/fees are in the same cell as the letters then, (assuming that
there is no leading space), try:

=VLOOKUP(TRIM(LEFT(A2,2)),$G$1:$H$133,2,FALSE)

If the letters and Services/fees are in the same cell with a leading space
then try:

=VLOOKUP(TRIM(LEFT(TRIM(A2),2)),$G$1:$H$133,2,FALSE)

Note that the range down to row 133 is much bigger then the codes will need
so that when your manager invents more codes you can just addthen a the
bottom of all the other codes.

Also the codes do not have to be in any order, the formula will still find
them.

If none of the above work then post back.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[dp]

Thanks you sooo much for your time. I will try this today and post back.

 

[dp]

IT WORKS! (Now I just have to learn why)
Thanks again for your time. God bless!

 

[Sandy Mann]

Glad that you got it to work. If XL Help is not enough Debra has a good
explanation here:

http://www.contextures.com/xlFunctions02.html#Arguments

Thanks for taking the time to post back.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[dp]

Sandy - I think I finally understand how this works but I have a question
about trim. I noticed in my file that some entries have more than one space
after the initial letter code(s). Am I correct that with trim 2 characters,
"AB Service" will give me AB but will "A Service" give me "A " (A with
a space)? And conversely if my table array has an extra space it will affect
matching the column. Thanks again

 

[dp]

Think i answered my own question - by playing with data in a file & found
that the space(s) do make a difference. So this is why I recd some #N/A's -
it's great to understand.

Also I was also reading other posts and found one that spoke of "text to
columns" so I learned something else. Two things in one day! - doesn't get
much better!

 

[Sandy Mann]

Yes. From Help:

"Removes all spaces from text except for single spaces between words. Use
TRIM on text that you have received from another application that may have
irregular spacing."

With "<space><space>A <space> <space> <space> Service<space><space>"
(without the quotes) in A1,

=LEN(A1) returns 15 but:

=LEN(TRIM(A1))

Returns 9
--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[dp]

Question on this same thread
Can I combine these two formulas below, or best to run each separately?
in cell a2 =trim(left(b2,2))
in cell c2 =vlookup(a2,$F$2:$G$62,2,false)
TIA