Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists.
Hi there
I was wondering if anyone can help with this problem. I have a spreadsheet that will contains a column for rank, name, points and difference in the first sheet called Monday through to the 7th sheet called Sunday. The purpose of the spreadsheet is to show the points for 500 agents that they have got so far in total. What I need to do is to highlight the agents whose points have either stayed the same as the day before or have lost points and highlight them. This would be fairly straight forward for me if it wasnt for the fact that the agents themselves will change position and so a simple just minus formula wont work. Sheet Monday Rank Name points Difference 1 Ian 10000 2 Steve 9000 3 Chris 8000 4 Peter 7000 5 Leo 6000 6 Gus 5000 7 Phil 4000 8 Linda 3000 9 skinner 2000 10 Lucy 1000 Sheet Tuesday Rank Name points Difference 1 Ian 110000 100000 2 Steve 9000 0 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 7 Phil 3789 -211 8 Linda 3000 0 9 Lucy 1020 20 10 skinner 892 -1108 If this is possible I would appreciate any help with the formulas if you could provide me with the basic formulas then I could enter in all the agents names etc. This could be to complicated for formulas and may require a macro instead. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists.
in Sheet Tuesday, under the Differences' column
select the cells which you want to highlight go to Format on the Tool bar select Conditional Formatting Under Condition 1, choose Cell Value Is choose less than or equal to in the box next to it Type 0 in the next box next to it click on Format select your color click OK -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "corbett" wrote: Hi there I was wondering if anyone can help with this problem. I have a spreadsheet that will contains a column for rank, name, points and difference in the first sheet called Monday through to the 7th sheet called Sunday. The purpose of the spreadsheet is to show the points for 500 agents that they have got so far in total. What I need to do is to highlight the agents whose points have either stayed the same as the day before or have lost points and highlight them. This would be fairly straight forward for me if it wasnt for the fact that the agents themselves will change position and so a simple just minus formula wont work. Sheet Monday Rank Name points Difference 1 Ian 10000 2 Steve 9000 3 Chris 8000 4 Peter 7000 5 Leo 6000 6 Gus 5000 7 Phil 4000 8 Linda 3000 9 skinner 2000 10 Lucy 1000 Sheet Tuesday Rank Name points Difference 1 Ian 110000 100000 2 Steve 9000 0 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 7 Phil 3789 -211 8 Linda 3000 0 9 Lucy 1020 20 10 skinner 892 -1108 If this is possible I would appreciate any help with the formulas if you could provide me with the basic formulas then I could enter in all the agents names etc. This could be to complicated for formulas and may require a macro instead. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists.
In Tuesday sheet:
=C2-VLOOKUP(B2,"Monday!B$2:C$100,2,FALSE) If it's possilbe for a name to be on Tuesday, but not Monday: =C2-IF(ISERROR(VLOOKUP(B2,"Monday!B$2:C$100,2,FALSE)), 0,VLOOKUP(B2,"Monday!B$2:C$100,2,FALSE))) Then use Format-Conditional Format to highlight cells that have <= 0. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "corbett" wrote: Hi there I was wondering if anyone can help with this problem. I have a spreadsheet that will contains a column for rank, name, points and difference in the first sheet called Monday through to the 7th sheet called Sunday. The purpose of the spreadsheet is to show the points for 500 agents that they have got so far in total. What I need to do is to highlight the agents whose points have either stayed the same as the day before or have lost points and highlight them. This would be fairly straight forward for me if it wasnt for the fact that the agents themselves will change position and so a simple just minus formula wont work. Sheet Monday Rank Name points Difference 1 Ian 10000 2 Steve 9000 3 Chris 8000 4 Peter 7000 5 Leo 6000 6 Gus 5000 7 Phil 4000 8 Linda 3000 9 skinner 2000 10 Lucy 1000 Sheet Tuesday Rank Name points Difference 1 Ian 110000 100000 2 Steve 9000 0 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 7 Phil 3789 -211 8 Linda 3000 0 9 Lucy 1020 20 10 skinner 892 -1108 If this is possible I would appreciate any help with the formulas if you could provide me with the basic formulas then I could enter in all the agents names etc. This could be to complicated for formulas and may require a macro instead. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists.
I forget the formula, place this in Differences column, D2 and copy down
=IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) You can also try the Custom format right click on D2 select Format Cells select Custom under Number tab place this under Type and copy down as Fill Formatting only #,##0_);[Red]-0;[Red]#,##0 -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: in Sheet Tuesday, under the Differences' column select the cells which you want to highlight go to Format on the Tool bar select Conditional Formatting Under Condition 1, choose Cell Value Is choose less than or equal to in the box next to it Type 0 in the next box next to it click on Format select your color click OK -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "corbett" wrote: Hi there I was wondering if anyone can help with this problem. I have a spreadsheet that will contains a column for rank, name, points and difference in the first sheet called Monday through to the 7th sheet called Sunday. The purpose of the spreadsheet is to show the points for 500 agents that they have got so far in total. What I need to do is to highlight the agents whose points have either stayed the same as the day before or have lost points and highlight them. This would be fairly straight forward for me if it wasnt for the fact that the agents themselves will change position and so a simple just minus formula wont work. Sheet Monday Rank Name points Difference 1 Ian 10000 2 Steve 9000 3 Chris 8000 4 Peter 7000 5 Leo 6000 6 Gus 5000 7 Phil 4000 8 Linda 3000 9 skinner 2000 10 Lucy 1000 Sheet Tuesday Rank Name points Difference 1 Ian 110000 100000 2 Steve 9000 0 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 7 Phil 3789 -211 8 Linda 3000 0 9 Lucy 1020 20 10 skinner 892 -1108 If this is possible I would appreciate any help with the formulas if you could provide me with the basic formulas then I could enter in all the agents names etc. This could be to complicated for formulas and may require a macro instead. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists.
Thanks a lot for this it works great
"Francis" wrote: I forget the formula, place this in Differences column, D2 and copy down =IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) You can also try the Custom format right click on D2 select Format Cells select Custom under Number tab place this under Type and copy down as Fill Formatting only #,##0_);[Red]-0;[Red]#,##0 -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "Francis" wrote: in Sheet Tuesday, under the Differences' column select the cells which you want to highlight go to Format on the Tool bar select Conditional Formatting Under Condition 1, choose Cell Value Is choose less than or equal to in the box next to it Type 0 in the next box next to it click on Format select your color click OK -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "corbett" wrote: Hi there I was wondering if anyone can help with this problem. I have a spreadsheet that will contains a column for rank, name, points and difference in the first sheet called Monday through to the 7th sheet called Sunday. The purpose of the spreadsheet is to show the points for 500 agents that they have got so far in total. What I need to do is to highlight the agents whose points have either stayed the same as the day before or have lost points and highlight them. This would be fairly straight forward for me if it wasnt for the fact that the agents themselves will change position and so a simple just minus formula wont work. Sheet Monday Rank Name points Difference 1 Ian 10000 2 Steve 9000 3 Chris 8000 4 Peter 7000 5 Leo 6000 6 Gus 5000 7 Phil 4000 8 Linda 3000 9 skinner 2000 10 Lucy 1000 Sheet Tuesday Rank Name points Difference 1 Ian 110000 100000 2 Steve 9000 0 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 7 Phil 3789 -211 8 Linda 3000 0 9 Lucy 1020 20 10 skinner 892 -1108 If this is possible I would appreciate any help with the formulas if you could provide me with the basic formulas then I could enter in all the agents names etc. This could be to complicated for formulas and may require a macro instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
array formula to show differences in 2 lists? | Excel Worksheet Functions | |||
How can I differences in two Excel Workbook lists automaticall | Excel Discussion (Misc queries) | |||
Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
Comparing two workbooks and highlighting the differences | Excel Discussion (Misc queries) | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) |