Anonymous
Anonymous asked in Computers & InternetProgramming & Design · 1 decade ago

# Help with Microsoft Excel?

In each cell in a column in my spreadsheet, there are 8 possibilites:

BBB BBP BPB BPP PPP PPB PBP PBB

Is there a formula I can implement so that if there is a:

BBB the next column over will have a 1

BBP the next column over will have a 2

BPB the next column over will have a 3

BPP the next column over will have a 4

PPP the next column over will have a 5

PPB the next column over will have a 6

PBP the next column over will have a 7

PBB the next column over will have an 8

Example:

A........B

PBB...8

BBB...1

BPB...3

PBP...7 etc

Thanks.

Relevance
• 1 decade ago

you can use the LOOKUP() formula for that

- Sort your list so its in alphabetical order (important)

- if your list is in column A (starting in cell A1)

- B1 copy&paste this formula

=LOOKUP(A1,{"BBB","BBP","BPB", "BPP","PBB","PBP","PPB", "PPP"},{1,2,3,4,8,7,6,5})

the copy B1 and paste down the column.

- so if you need to add letters on the left, make sure its in alphabetical order. then add the number on the right in the same position.

• 1 decade ago

You could do it with 'IF' formulas, if you are using Excel 2007 or later. However, that would require a lot of formulas.

An easier way is with a macro like this one. If your column of data is not column A, then change all "A" references in the macro below to your column reference, i.e. "C".

Copy the macro, modified if necessary, to the clipboard:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

Select Case UCase(Cells(i, "A"))

Case Is = "BBB"

Cells(i, "A").Offset(0, 1).Value = 1

Case Is = "BBP"

Cells(i, "A").Offset(0, 1).Value = 2

Case Is = "BPB"

Cells(i, "A").Offset(0, 1).Value = 3

Case Is = "BPP"

Cells(i, "A").Offset(0, 1).Value = 4

Case Is = "PPP"

Cells(i, "A").Offset(0, 1).Value = 5

Case Is = "PPB"

Cells(i, "A").Offset(0, 1).Value = 6

Case Is = "PBP"

Cells(i, "A").Offset(0, 1).Value = 7

Case Is = "PBB"

Cells(i, "A").Offset(0, 1).Value = 8

Case Else

End Select

Next

End Sub

Select the appropriate worksheet and right click on the sheet tab.

Select 'View Code'.

Paste the macro into the sheet module editing area to the right.

Close the VBE and return to Excel.

Select a cell and the appropriate values will be entered in the next column.

• 1 decade ago

Let's assume your Column with the list is A.

In Column B, type the possibilities:

B1 = PBB

B2 = BBB

B3 = BPB

B4 = PBP

etc

In C1, type this:

=COUNTIF(A:A,B1)

Copy that down to the end of your Possibilities.

• 1 decade ago

i guess you need to go for the if then else, which i think so....according to me, no!