Format excel spreadsheet

[lareya]

Hi,
I am using excel 2002. I have a simple, but large spreadsheet.

Name Procedure Procedure ID
Dr. X lap chole 123456
Dr. Y lap chole w/grams 123456
Dr. z open chole 112456

Now, all I want is to take the column of Procedure ID and format all the procedures by color. they are spread all around, since the grouping was on Dr's. - but I want to visually be able to see the "same" procedure ID. I am a complete newbie w/excel and have tried to work something up in the conditional format. Also, I am afraid of running out of colors. Is there a better way for me to format these? Some how I need to identify all same procedures.
I looked/found this formula on this forum - but wasn't sure if I could implement this:

A quick one this as the question comes up time and again.

Sort your sheet by the numbers you are trying to find duplicates on (in this instance we shall call it column A)
Then in a spare column type this formula

=if(A1=A2,1,0)
Then switch on auto filter and filter by 1 on your column with the equation
Then delete these 1s
This should leave you with a set of de-duped refs.

this looks like it identifies same data - but instead of deleting, I want to format it.

Thanks for all your help. I am using WNT 2k.

lareya

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal 8.5; ORSOS/One Call Hospital Scheduling System v9.3; SQL database; Huge Newbie to Crystal! Operating Room RN Analyst

 

[GlennUK]

How many Procedure IDs are there? What colour are you going to choose for each one?

If you have a list of the unique Procedure IDs, can you colour the list to be the colours you want for each?


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[coachdan]

If there are only a few codes, you can use conditional formatting.

coachdan32

 

[lareya]

There are just a little over 10000 Procedure ID's, I was going to try to cycle the colors or hoped someone else could help me figure out a different way of formatting that would still be able to identify the same procedure ID's.

I may have to just scrap this and start over w/ something else.

Lareya

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Crystal 8.5; ORSOS/One Call Hospital Scheduling System v9.3; SQL database; Huge Newbie to Crystal! Operating Room RN Analyst

 

[GlennUK]

Hi Lareya,

you cannot realistically have 10000 different colours for your Procedure ids, and even if you were to choose 10 standard colours, how would assign a colour to a Procedure id? You could do that randomly, if you like.

Alternatively, you could use Conditional Formatting to colour alternating Procedure ids.

Select the data are you want shaded this way ( e.g. A2:F33000 ), and do menu command Format/Conditional Formatting, and choose a condition of "Formula is", and use this formula:

=MOD(SUM(1/COUNTIF($C$2:$C2,$C$2:$C2)),2)=1

and choose a Format to be used every other Procedure id ( assuming that Procedure id is in column C ).

If you want another Format for the Procedure ids in between the ones shaded this way, then add another condition ( see the "Add >>" button on the Conditional Formatting dialog ), but have the formula end with =0 instead of =1.



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

If you are not averse to a bit of VBA - how about some code that will highlight all the SAME IDs as the cell you are in

Assuming IDs are in col C

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 3 Then Exit Sub
    With ActiveCell.CurrentRegion
        With .FormatConditions
            .Delete
            .Add Type:=xlExpression, Formula1:="=" & Target.Address(False, False) & "=" & Target.Address
        End With
        With .FormatConditions(1)
            .Interior.ColorIndex = 6
        End With
    End With
End Sub

This needs to be pasted into the workSHEET module - if you need help with that, just ask

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question