separating data

[ckelly0172]

Hi there,
I have a long list of data and would like to separate the different types so that they are colour coded, either the cell or the text.
eg, if the data is simply 1,2 or 3 then each number to be a different colour.
Could do it manually but i have approx 50000 bit of data of differing types.
thanks
c

 

[ckelly0172]

oop, should've mentioned the data i have is text not numerical... (if thats makes any difference???)
c

 

[xlbo]

need more info really - how many different colours do yuo need ?? If it is not many, this can probably be done with conditional formatting but I would caution against using coours to define different types of data as excel has no built in functionality to differentiate data by colour. You would probably be better off, long term, adding a column and using a formula to return a different string, dependant on your test conditions - that way you can then very easily seperate the data by type....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[mscallisto]

Have you tried conditional formatting?

Highlite the cell range
Format > conditional formating

If Cell value = 1 Format Pattern pick color

Add another condition

repeat above for values 2 and 3

 

[mscallisto]

I agree with xlbo but must add that you can sort by color.

 

[ckelly0172]

Dont think i can use conditional format as the data i have is like:-

D1 on
D2 on
D1 off
D2 off
D1 down
D2 up... etc

Also, this data is in one cell wiht other attributed to it.
Geoff, I require only 5 or 6 colours.
Thanks for help chaps.

 

[xlbo]

being as it is text, I don't think this will be achievable using conditional formatting - what are the colours and what are the logic tests that need to be applied - ie what determines the colour for each of the 5 or 6 conditions ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ckelly0172]

Takin the above example, I'd like to differentiate each combination with any colour.
eg all 'd1 on' = green
'd2 on' = blue... etc
c

 

[xlbo]

ok, this is not as simple as you would think

Step 1 - get a unique list of combinations - use the Filter>Advanced Filter & tick unique values or create a pivottable to do this

Step 2 - decide which colour you want for each combination and type that colour in next to each of the unique combinations. Give this range a name eg ColourLkup

Step 3 - Select the entire range and run some code which loops through all the cells in the range and looks up the combination to return the colour (in ColourLkup range) which is then applied to the cell

Base code would be

Sub Colour_Em()
dim myColour as string, colInd as integer
for each c in selection
 myColour = worksheetfunction.vlookup(c.text,[ColourLkup),2,false)
Select case myColour
    case "red"
      colInd = 3
    case "blue"
      colInd = 5
    case "green"
      colInd = 4
end select
c.interior.colorindex = colInd
next
end sub

Hope this gets you started

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ckelly0172]

Thanks Geoff, will give it a go.

 

[ckelly0172]

Geoff,

Having probs with the 'myColour = worksheetfunction.vlookup(c.text,[ColourLkup),2,false)' code.
Giving me a "run time error 1004"
c

 

[xlbo]

You must have a named range called "ColourLkup"

You must also enclose it in [ ]
I notice that you are missing the ] on your post (may just be a typo)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ckelly0172]

Could you please elaborate on the "named range".

this is the line straight out of the editor:-
myColour = WorksheetFunction.VLookup(c.Text, [ColourLkup], 2, False) and Iam having the "error 1004 uanble to get the Vlookup propertyof the Worksheetfunction class.
C

 

[mscallisto]

He most likely means:


In your worksheet

highlite your data you wish to name (maybe the whole column)

click insert > name > define call it ColourLkup

 

[xlbo]

Correct mscallisto - ColourLkup should refer to the range with the unique combinations and the colour descriptions next to them
I would expect something like
d1 on green
d1 off red
d2 on blue
d2 off yellow

Select the range and go Insert>Name>Define

type
ColourLkup in the textbox at the top and Add. You now have a range named "ColourLkup"

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question