Excel Help.......

[rambabu304]

Guys I need a help in Excel… so Excel guru’s please help me……….

I have column X which has 5,000 rows and each cell in it is either RED or YELLOW or GREEN depending on the conditional formatting it has.

Now I need to create 3 new columns as RED (column Y), YELLOW (column Z) & GREEN (column AA). And these columns should only pull its color cell from column X. So results should be:


Column Y – Should only have RED values
Column Z– Should only have YELLOW values
Column AA – Should only have GREEN values

I am sure there should be a formula to do this. I can sort the COLUMN X, but I need them to put aside in different columns and need to show in a graph..…

zara kocham help cheyadani amma.........

[b]spl note to lollilolli... nuuvu vacha ee thread ni kompu lepaku...... book ani adi ani..... stay out of this .......[/b]

[_poori]

aa conditional formatting rules unnaya nee deggara?

[Jatka Bandi]

dude... right away strike ayina method avute.... sort use chey aa column paina..... ante select that column and "data" lo "sort" option ki velli... sort by cell color ane option vuntundi chudu... appudu cell color vunnavi anni oka order lo vastayi... then u can copy and paste easily..... if this doesn't serve your purpose let me know....

[CORLEONE]

[Jatka Bandi]

also, column select chesi..... "data" lo "filter" select chey..... appudu aa clumn first cell lo arrow laga vastundi... aa arrow click chesi filter by color use cheyi... only same color vi adi same order lone vastayi... vatini copy cheste unfiltered vi copy avvavu.... them paste them in to another column.....

[Jatka Bandi]

got what you want rambabu?

[_poori]

kavali ante ee macros kuda vadukovachu:

==========================================


Sub Test()
For Counter = 1 To 5000
Set curCell = Worksheets("Sheet1").Cells(Counter, 24)

Set color1Cell = Worksheets("Sheet1").Cells(Counter, 25)
Set color2Cell = Worksheets("Sheet1").Cells(Counter, 26)
Set color3Cell = Worksheets("Sheet1").Cells(Counter, 27)

If curCell.Interior.Color = vbRed Then color1Cell.Value = curCell.Value
If curCell.Interior.Color = vbYellow Then color2Cell.Value = curCell.Value
If curCell.Interior.Color = vbGreen Then color3Cell.Value = curCell.Value

Next Counter
End Sub

==========================================

Hope this helps!

[150bryant]

[url="http://www.cpearson.com/excel/SortByColor.aspx"]http://www.cpearson.com/excel/SortByColor.aspx[/url]

[badrii]

[quote name='_poori' timestamp='1349888438' post='1302605401']
kavali ante ee macros kuda vadukovachu:

==========================================


Sub Test()
For Counter = 1 To 5000
Set curCell = Worksheets("Sheet1").Cells(Counter, 24)

Set color1Cell = Worksheets("Sheet1").Cells(Counter, 25)
Set color2Cell = Worksheets("Sheet1").Cells(Counter, 26)
Set color3Cell = Worksheets("Sheet1").Cells(Counter, 27)

If curCell.Interior.Color = vbRed Then color1Cell.Value = curCell.Value
If curCell.Interior.Color = vbYellow Then color2Cell.Value = curCell.Value
If curCell.Interior.Color = vbGreen Then color3Cell.Value = curCell.Value

Next Counter
End Sub

==========================================

Hope this helps!
[/quote]
[img]http://www.andhrafriends.com/uploads/gallery/album_20/gallery_24383_20_515168.gif[/img]

[150bryant]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Step 1: Create a 'User Defined Function' which will be called "ColorRank"[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- Open the relevant workbook[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- Alt+F11 opens a Visual Basic window[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- from the top menu select < Insert < Module and another box opens within the VB window.[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- Copy the following into that box:[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Function ColorRank(ColorOrder As Range, LookCell As Range)[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'''''''''''''''''''''''''''''''''''''''''''''[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'Ranks a list of Colors so they can be sorted[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'''''''''''''''''''''''''''''''''''''''''''''[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Dim i As Integer[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Dim ICol1 As Integer[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Dim ICol2 As Integer[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]i = 1[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ICol2 = -1[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ColorRank = 0[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'Loop until match is found[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Do Until ICol1 = ICol2[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'Replace "Interior" with Font to sort by Font color[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ICol1 = ColorOrder(i, 1).Interior.ColorIndex[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ICol2 = LookCell.Interior.ColorIndex[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]If i = ColorOrder.Rows.Count + 1 Then[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'No Match found place in Text[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ColorRank = "No colour match!!!"[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Exit Do[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]End If[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]'Pass the Row number of the colour match[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]ColorRank = i[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]i = i + 1[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Loop[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]End Function[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- hit Alt+Q which will take you back to the workbook[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- Save your workbook[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Step 2: Create a reference range[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]- create a block of cells with each cell having one of the different colours that you have in your data. If you have red, yellow and blue coloured data, then you will have created 3 cells. One of each colour.[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Put the colours in the reference cells in the order that you wish to use in your sort.[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Step 3: Create a sort reference column[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Have a blank column (let's call it our "ColorRank" column)[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Into that column enter the following formula:[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]=ColorRank($A$1:$A$3,C1)[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]where:[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]A1:A3 is the range of the reference cells[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]C is the Column containing the data with colours to be sorted[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]C1 is the first cell in that column of data[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]You may change these cells in the formula if if the positions of your data and reference range are different.[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]After you hit enter you will see a digit appear which will represent the position of that colour in your reference range. If it is the top colour, you will see a number 1.[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Copy that formula down the column.[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]You will now have a column full of 1's, 2's & 3's if you have only three colours.[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Step 4: Perform your sort[/background][/size][/font][/color]

[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Select all your data, including the new "ColorRank" column[/background][/size][/font][/color]
[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]Sort your data "by" that column![/background][/size][/font][/color]


[color=#000000][font=verdana, arial, sans-serif][size=2][background=rgb(249, 249, 249)]I hope that all makes sense. It does work. I took the code from one of those google references, but then had to sort out what to do with it.[/background][/size][/font][/color]

[Jatka Bandi]

[quote name='jamajacha' timestamp='1349888771' post='1302605430']
[url="http://www.cpearson.com/excel/SortByColor.aspx"]http://www.cpearson....ortByColor.aspx[/url]
[/quote]bongem kadu... excel do provide sorting by color.... VB functions akkarla em akkarla... paina procedure cheppa chudu....

[150bryant]

[url="http://www.sheffieldforum.co.uk/archive/index.php/t-148385.html"]http://www.sheffieldforum.co.uk/archive/index.php/t-148385.html[/url]

[url="http://office.microsoft.com/en-us/excel-help/guidelines-and-examples-for-sorting-and-filtering-data-by-color-HA010212590.aspx"]http://office.microsoft.com/en-us/excel-help/guidelines-and-examples-for-sorting-and-filtering-data-by-color-HA010212590.aspx[/url]

[url="http://spreadsheets.about.com/od/datamanagementinexcel/ss/090930-sort-background-color-excel.htm"]http://spreadsheets.about.com/od/datamanagementinexcel/ss/090930-sort-background-color-excel.htm[/url]

hope these help you

[_poori]

[quote name='badrii' timestamp='1349888815' post='1302605436']
[img]http://www.andhrafriends.com/uploads/gallery/album_20/gallery_24383_20_515168.gif[/img]
[/quote]
[img]https://lh5.googleusercontent.com/-oF3C1a1vyE8/T-eJEzdWtlI/AAAAAAAAHCE/mV6RFKbg_7E/s150/PK-34.gif[/img] thread starter em annado last line lo

[badrii]

[quote name='_poori' timestamp='1349889113' post='1302605469']
[img]https://lh5.googleusercontent.com/-oF3C1a1vyE8/T-eJEzdWtlI/AAAAAAAAHCE/mV6RFKbg_7E/s150/PK-34.gif[/img] thread starter em annado last line lo
[/quote]

[b]spl note to [size=5]lollilolli[/size]... nuuvu vacha ee thread ni kompu lepaku...... book ani adi ani..... stay out of this .......[/b]

[b][img]http://www.andhrafriends.com/uploads/gallery/album_20/gallery_24383_20_49898.gif[/img][/b]

[_poori]

[quote name='badrii' timestamp='1349889242' post='1302605488']

[b]spl note to [size=5]lollilolli[/size]... nuuvu vacha ee thread ni kompu lepaku...... book ani adi ani..... stay out of this .......[/b]

[b][img]http://www.andhrafriends.com/uploads/gallery/album_20/gallery_24383_20_49898.gif[/img][/b]
[/quote]
[img]https://lh3.googleusercontent.com/-ZdlVWooDhdA/ToJcNBWMDrI/AAAAAAAAEfY/spAtMnUHogs/s150/PK-2.gif[/img] spamming cheyaku ani antunnadu