| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
highlighting and sorting
is there anyway to do following task:
A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.22 Lee 5 10.10.10.11 wong 30 -----highlight 10.10.10.9 wong 15 10.10.10.10 chan 3 -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. after sorting A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.11 wong 30 -----highlight 10.10.10.10 chan 3 -----highlight 10.10.10.22 Lee 5 10.10.10.9 wong 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks.. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
highlighting and sorting
I would try this pseudo code:
set rng=range("A1").currentregion assume data at top left of worksheet rng.sort key1:=range("B2"), key2:= range("C2") both ascending, label present set rng=range("B2:B" & range("B2").end(xldown).row) for each c in rng if c.offset(1,0)<"" then if c.offset(1,0)<c then c.offset(0,1).interior.color=vbyellow c.offset(0,2)=1 end if end if next c set rng=range("A1").currentregion rng.sort key1:=rnage("D2") desc On Mar 9, 12:10*pm, kokhong wrote: is there anyway to do following task: *A * * * * * * * * * * * * * * * * * * * * * * * * B * * * * * * * * * * * * * * * * * * * * * * * C IP * * * * * * * * * * * * * * * * * * * *Username * * * * * * * * * * * * * * * * * * * hits 10.10.10.1 * * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * 10 * * ----highlight 10.10.10.22 * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * * *5 10.10.10.11 * * * * * * * * * * * * * wong * * * * * * * * * * * * * * * * * * * * * * 30 * *-----highlight 10.10.10.9 * * * * * * * * * * * * * * *wong * * * * * * * * * * * * * * * * * * * * * * 15 10.10.10.10 * * * * * * * * * * * * * chan * * * * * * * * * * * * * * * * * * * * * * * *3 * -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. * * * * * * * * * * * * * * * * * * * * *after sorting *A * * * * * * * * * * * * * * * * * * * * * * * * B * * * * * * * * * * * * * * * * * * * * * * * C IP * * * * * * * * * * * * * * * * * * * *Username * * * * * * * * * * * * * * * * * * * hits 10.10.10.1 * * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * 10 * * ----highlight 10.10.10.11 * * * * * * * * * * * * * wong * * * * * * * * * * * * * * * * * * * * * * 30 * *-----highlight 10.10.10.10 * * * * * * * * * * * * * chan * * * * * * * * * * * * * * * * * * * * * * * *3 * -----highlight 10.10.10.22 * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * * *5 10.10.10.9 * * * * * * * * * * * * * * *wong * * * * * * * * * * * * * * * * * * * * * * 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks.. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
highlighting and sorting
PY & Associates, how to use this pseudo code?in macro?do you mind to list
down.. "PY & Associates" wrote: I would try this pseudo code: set rng=range("A1").currentregion assume data at top left of worksheet rng.sort key1:=range("B2"), key2:= range("C2") both ascending, label present set rng=range("B2:B" & range("B2").end(xldown).row) for each c in rng if c.offset(1,0)<"" then if c.offset(1,0)<c then c.offset(0,1).interior.color=vbyellow c.offset(0,2)=1 end if end if next c set rng=range("A1").currentregion rng.sort key1:=rnage("D2") desc On Mar 9, 12:10 pm, kokhong wrote: is there anyway to do following task: A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.22 Lee 5 10.10.10.11 wong 30 -----highlight 10.10.10.9 wong 15 10.10.10.10 chan 3 -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. after sorting A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.11 wong 30 -----highlight 10.10.10.10 chan 3 -----highlight 10.10.10.22 Lee 5 10.10.10.9 wong 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks.. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
highlighting and sorting
PY & Associates, now im able to use the information u give me to
highlight..now i only left the last step..how to filter the highlight columns? "kokhong" wrote: PY & Associates, how to use this pseudo code?in macro?do you mind to list down.. "PY & Associates" wrote: I would try this pseudo code: set rng=range("A1").currentregion assume data at top left of worksheet rng.sort key1:=range("B2"), key2:= range("C2") both ascending, label present set rng=range("B2:B" & range("B2").end(xldown).row) for each c in rng if c.offset(1,0)<"" then if c.offset(1,0)<c then c.offset(0,1).interior.color=vbyellow c.offset(0,2)=1 end if end if next c set rng=range("A1").currentregion rng.sort key1:=rnage("D2") desc On Mar 9, 12:10 pm, kokhong wrote: is there anyway to do following task: A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.22 Lee 5 10.10.10.11 wong 30 -----highlight 10.10.10.9 wong 15 10.10.10.10 chan 3 -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. after sorting A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.11 wong 30 -----highlight 10.10.10.10 chan 3 -----highlight 10.10.10.22 Lee 5 10.10.10.9 wong 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks.. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
highlighting and sorting
PY & Associates, it really can make it, now i only know the purpose why do
you add the 1 to D columns..now i can finish my task aldy thanks you... "kokhong" wrote: PY & Associates, now im able to use the information u give me to highlight..now i only left the last step..how to filter the highlight columns? "kokhong" wrote: PY & Associates, how to use this pseudo code?in macro?do you mind to list down.. "PY & Associates" wrote: I would try this pseudo code: set rng=range("A1").currentregion assume data at top left of worksheet rng.sort key1:=range("B2"), key2:= range("C2") both ascending, label present set rng=range("B2:B" & range("B2").end(xldown).row) for each c in rng if c.offset(1,0)<"" then if c.offset(1,0)<c then c.offset(0,1).interior.color=vbyellow c.offset(0,2)=1 end if end if next c set rng=range("A1").currentregion rng.sort key1:=rnage("D2") desc On Mar 9, 12:10 pm, kokhong wrote: is there anyway to do following task: A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.22 Lee 5 10.10.10.11 wong 30 -----highlight 10.10.10.9 wong 15 10.10.10.10 chan 3 -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. after sorting A B C IP Username hits 10.10.10.1 Lee 10 ----highlight 10.10.10.11 wong 30 -----highlight 10.10.10.10 chan 3 -----highlight 10.10.10.22 Lee 5 10.10.10.9 wong 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks.. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
highlighting and sorting
Please take care of the last entry.
If the name is single appearance, you will miss it. At end of procedure, add "columns(4).clearcontents" On Mar 9, 3:18*pm, kokhong wrote: PY & Associates, it really can make it, now i only know the purpose why do you add the 1 to D columns..now i can finish my task aldy thanks you... "kokhong" wrote: PY & Associates, now im able to use the information u give me to highlight..now i only left the last step..how to filter the highlight columns? "kokhong" wrote: PY & Associates, how to use this pseudo code?in macro?do you mind to list down.. "PY & Associates" wrote: I would try this pseudo code: set rng=range("A1").currentregion * * *assume data at top left of worksheet rng.sort key1:=range("B2"), key2:= range("C2") * *both ascending, label present set rng=range("B2:B" & range("B2").end(xldown).row) for each c in rng if c.offset(1,0)<"" then * *if c.offset(1,0)<c then * * * c.offset(0,1).interior.color=vbyellow * * * c.offset(0,2)=1 * *end if end if next c set rng=range("A1").currentregion rng.sort key1:=rnage("D2") desc On Mar 9, 12:10 pm, kokhong wrote: is there anyway to do following task: *A * * * * * * * * * * * * * * * * * * * * * * * * B * * * * * * * * * * * * * * * * * * * * * * * C IP * * * * * * * * * * * * * * * * * * * *Username * * * * * * * * * * * * * * * * * * * hits 10.10.10.1 * * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * 10 * * ----highlight 10.10.10.22 * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * * *5 10.10.10.11 * * * * * * * * * * * * * wong * * * * * * * * * * * * * * * * * * * * * * 30 * *-----highlight 10.10.10.9 * * * * * * * * * * * * * * *wong * * * * * * * * * * * * * * * * * * * * * * 15 10.10.10.10 * * * * * * * * * * * * * chan * * * * * * * * * * * * * * * * * * * * * * * *3 * -----highlight 1. highlight all the same user name with highest hits among same user name. 2. highligh all the unique user name. (example chan) 3. sort all the highlight columns to the top. any way to do this?thanks in your advance.. * * * * * * * * * * * * * * * * * * * * *after sorting *A * * * * * * * * * * * * * * * * * * * * * * * * B * * * * * * * * * * * * * * * * * * * * * * * C IP * * * * * * * * * * * * * * * * * * * *Username * * * * * * * * * * * * * * * * * * * hits 10.10.10.1 * * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * 10 * * ----highlight 10.10.10.11 * * * * * * * * * * * * * wong * * * * * * * * * * * * * * * * * * * * * * 30 * *-----highlight 10.10.10.10 * * * * * * * * * * * * * chan * * * * * * * * * * * * * * * * * * * * * * * *3 * -----highlight 10.10.10.22 * * * * * * * * * * * * * Lee * * * * * * * * * * * * * * * * * * * * * * * * *5 10.10.10.9 * * * * * * * * * * * * * * *wong * * * * * * * * * * * * * * * * * * * * * * 15 if highlighting procedure cannot make it, it is ok..as long as the thing can be sorted as above,finally.thanks..- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic sorting (giving max and min) based on custom sorting lis | Excel Worksheet Functions | |||
| Sorting data by highlighting? | Excel Discussion (Misc queries) | |||
| Highlighting | Excel Discussion (Misc queries) | |||
| Highlighting every other row? | Excel Discussion (Misc queries) | |||
| Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
Hybrid Mode
