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.

4 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    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!

Still have questions? Get your answers by asking now.