Help with a formula

[Richard Lewis]

Hi,

I'd like to create a formula to replace "Rubbish" with the appropriate
value. For example:

Column A Column B
Rubbish Name 1
Rubbish Name 1
Rubbish Name 1
Rubbish Name 2
Rubbish Name 2
Rubbish Name 3
Rubbish Name 3
Rubbish Name 3
Rubbish Name 4

Name 1 relates to Mr A
Name 2 relates to Mr A
Name 3 relates to Mr A
Name 4 relates to Mr B

Replace "Rubbish" with "Mr A" where name = 1-3
Replace "Rubbish" with "Mr B" where name = 4

Is there a straightforward method for the above?

Many thanks.

Richard

 

[Gord Dibben]

Richard

If those are the only choices...........

=IF(B1="Name 4","Mr. B","Mr. A")

Enter this in A1 and copy down overwriting the "Rubbish" in column A.

Gord Dibben Excel MVP XL2002

 

[Richard Lewis]

Richard

If those are the only choices...........

=IF(B1="Name 4","Mr. B","Mr. A")

Enter this in A1 and copy down overwriting the "Rubbish" in column A.

Gord Dibben Excel MVP XL2002

Thanks very much for the reply Gord.

Unfortunately, I have to deal with Mr A, B, C, D... Each "Mr" will have
about 6 or so "names" relating to it.

For example:

Column A Column B
Rubbish Name 01
Rubbish Name 02
Rubbish Name 03
Rubbish Name 04
Rubbish Name 05
Rubbish Name 06
Rubbish Name 07
Rubbish Name 08
Rubbish Name 09
Rubbish Name 10
Rubbish Name 11
Rubbish Name 12
Rubbish Name 13
Rubbish Name 14
Rubbish Name 15
Rubbish Name 16
Rubbish Name 17
Rubbish Name 18
Rubbish Name 19
Rubbish Name 20

Name 01-06 relate to Mr A
Name 07-12 relate to Mr B
Name 13-15 relate to Mr C
Name 16-20 relate to Mr D

Hope you can help.

Many thanks

Richard

 

[Tom Ogilvy]

Create a table on another sheet that looks like this

COLUMN A COLUMN B
Name 01 MR A <==== row 1
Name 02 MR A
Name 03 MR A
Name 04 MR A
Name 05 MR A
Name 06 MR A
Name 07 MR B
Name 08 MR B
Name 09 MR B
Name 10 MR B
Name 11 MR B
Name 12 MR B
Name 13 MR C
Name 14 MR C
Name 15 MR C
Name 16 MR D
Name 17 MR D
Name 18 MR D
Name 19 MR D
Name 20 MR D

Select the table and do Insert=>Name=>Define
Name: Table1
Refers To: =Sheet2!$A$1:$A$20

go back to you rubbish column, assume the first Rubbish is in A1

=Vlookup(B1,Table1,2,False)

then drag fill down the column

This is only useful if you have a lot of data and the names repeat in your
data.

Another approach would be to pick out the numerical value and have a table
that shows the first number for each name.

however, I suspect your name 01, name 02, are just to represent different
text in the cells and don't have the format name 01, etc. So if you want a
specific solution, you need to layout what you are really dealing with.