Adding letters and numbers


T

TH3ROOKIE

Hi.

Here's what I'm looking to do.

If I enter this in cell A:6 to A:8

A:6 = 10b65b22
A:7 = 10b65b22
A:8 = 10b65b21

then excel will see that A:6 and A:7 is the same value so it will erase
A:7 and B:6 will give 2.

So if A:8 was the same then A=6 then A:8 erase itself and B:6 gives 3.

The other thing is if I enter this

A:6 = 8kb7800

Then I want excel to recognize the first 3 numbers/letters and if the
first 3 characters = 8kb, then c:6 = FEDEX

Hope I can get some help
 
Ad

Advertisements

D

Dave Peterson

You could use a formula in B2 (headers in Row 1):

=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(A:A,A2),"Delete me")
then drag down the length of your data

Select column B and
edit|copy
edit|paste special|values

Apply data|filter|autofilter to column B
Filter to show "delete me"
select those visible cells
rightclick on your selection
delete
entire row.

Delete the original column A if you want.

For the second part, I' create a new worksheet with those 3 letter codes and
translated names.

Then use a formula like this in another helper column:
=vlookup(left(b2,3),sheet2!a:b,2,false)

For more info about =vlookup()...
You could look at Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html
 
T

TH3ROOKIE

Doesn't work.... gives me an error when entering formula

I've entered this formula in cell b1, and when I tried to do somethin
else... BANG !!!! an error.... :mad:

=IF(COUNTIF($A$2:*-A2,A2-*)=1,COUNTIF(A:A,A2),"Delete me")

it doesn't accept the value that are bold characters.

Same thing for the vlookup too, I've read and tried the example on th
link you gave me, it gives me an error too...

I'm trying hard to learn, but excel doesn't help me alot... it tell
me that I have an error.. but not what kind....
 
Ad

Advertisements

D

Dave Peterson

First, the interface that most use doesn't show any bold characters (I connect
directly to the newsgroups--not through excelforum).

Do you see numbers as column headers or letters?

If you see numbers, change them (temporarily, if you want) to letters:
tools|option|General tab|uncheck R1C1 reference style.

If you're outside the USA, you might use a different list separator. In Europe,
lots of people use a semi colon:

=IF(COUNTIF($A$2:A2;A2)=1;COUNTIF(A:A;A2);"Delete me")

(I think I changed all the commas to semicolons.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top