more Exel help plze..........
#1
Scooby Regular
Thread Starter
Join Date: Apr 2002
Location: The poliotical wing of Chip Sengravy.
Posts: 6,129
Likes: 0
Received 0 Likes
on
0 Posts
Any chance of some help with this please ?
I am trying to sort through our companies phone bills and have so far got the data in from the online phone bill and pasted it in, I have grouped it into bunches of the same number etc.
I am trying to allocate a tag of either "personal" or "work" to the phone numbers.
=SUBSTITUTE(D16, "5552345", "Private")
this line replaces the numbers 5552345 with the text private - this is easy.
how can I get the line to search throgh a list of numbers and tag it either private ot work,
something like this, but the line is wrong:
=SUBSTITUTE(D16, "5552345,901,354474,8478464,363478347", "Private")(D16," 999,123,192"Work")
cheers,
mike.
#2
if this was me, i would try the IF command, so if(number)=334455,true(private),false(work)
I dont know if yu can link the if command with statements like, =334455 OR 445566 etc, but this might work
Gareth
I dont know if yu can link the if command with statements like, =334455 OR 445566 etc, but this might work
Gareth
#3
Set up a range of numbers (column) that you want to be classified as private and name it PrivateNos.
For each record on your list add the vlookup function.
E.g. Say column A has the numbers, in column B add this formula:
(Place this example in cell B2)
=if(isna(vlookup(A2,PrivateNos,1,false)),"Work","P rivate")
This works by evaluating the number against the list. If the vlookup function cannot return a value the ISNA part returns WORK, else it is PRIVATE.
Rgds,
Martin.
For each record on your list add the vlookup function.
E.g. Say column A has the numbers, in column B add this formula:
(Place this example in cell B2)
=if(isna(vlookup(A2,PrivateNos,1,false)),"Work","P rivate")
This works by evaluating the number against the list. If the vlookup function cannot return a value the ISNA part returns WORK, else it is PRIVATE.
Rgds,
Martin.
#4
have a separate table of known numbers e.g.
123245 Private
6235724 Private
1275057 Work
name this table "NUMBERS" (insert->name->define)
Then do a vlookup against the original list
if you have "123245" in A1 then in A2 have "=vlookup(+A1,NUMBERS,2,FALSE)" which would return "Private".
Deano
123245 Private
6235724 Private
1275057 Work
name this table "NUMBERS" (insert->name->define)
Then do a vlookup against the original list
if you have "123245" in A1 then in A2 have "=vlookup(+A1,NUMBERS,2,FALSE)" which would return "Private".
Deano