Hobbsy

have some data that I need to sort.

Column A has cost centre

Column B has location

Column C user details

Column D Telephone number

I ceased about 100 telephone lines about a year ago and they still appear on our BT bill (column D). These numbers I can paste into say column G. Is there a Formula and/or V look up that can be used to do the following. If the data is in column G and appears in column D then it puts text in say column E as ceased?

Cheers

markr1963

I'd paste the numbers on to a separate sheet and do an ascending sort.

On the sheet with your existing data, in col D use something like this

=IF(ISERROR(VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$17,1,0)),"","ceased")

Where A1 is the number you are looking to match
Sheet2!$A$1:$A$17 is the lookup range. You could use a named range instead. Don't forget to use absolute addressing!
1 is the column number returned from the lookup range
0 makes the expression retun exact matches only

HTH
Mark

Hobbsy

Mark, you have lost me somewhat. Can A1 be refrenced to the number range. I have 100 numbers that I am sure are in the original data just dont want to have to manually sort through and type ceased.

markr1963

The references I used are just examples. If I understand you, you've got 3 columns of data; A, B and C on Sheet 1. The number of rows doesn't matter. In your col D of the first row of data (D1 maybe) the formula would look something like:
=IF(ISERROR(VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$17,1, 0)),"","ceased")

Remember the sheet2 bit will need altering to fit your data so 100 hundred numbers would be something like $A$1:$A$100. This list is what you're trying to match to the numbers you have in col C on sheet 1. Assuming the formula in D1 gives the correct answer copy the formula down col D. The sheet 1 refence will change to match the row but the range defined on sheet 2 'cos of the absolute reference (the $ bit)


Hope that's a bit clearer

Mark

markr1963

This might help


Mark

Hobbsy

Cheers Mark for your time, managed a bodge way of sorts using a formula d2-d1 but will give your method a go as it is a far more proffesinal way of doing it!

Cheers again

markr1963

No worries