Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists 2
Hi there
Just like to say thanks to everyone who answered my previous questions as the spreadsheet now does what I needed to do but now I have another problem. Quick recap 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 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 This has been fixed by Francis who suggested =IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) and a conditional formatting Which works fine. I now need a new column added explaining why there scores have stayed the same or gone negative. This will require me to put A, I, i, or V in the next column. This is something that I need to do every day. Its a bit of a pain having to do this every day from scratch. I was wondering if there was a formula that would automatically copy over the settings from yesterdays sheet. A potential issue is that it would need to take into account the new position that somebody would be in and also need to be flexible enough that I can over ride it without deleting the formula. Rank Name points Difference Reason 1 Ian 110000 100000 2 Steve 9000 0 V 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 I 7 Phil 3789 -211 A 8 Linda 3000 0 V 9 Lucy 1020 20 V 10 skinner 892 -1108 A The 2nd problem is that i need a new worksheet that will show how many times somebody has one of the new symbols. I.E I need all the agents that have an symbol beside their names for 6/7, 5/7 of the work sheets. I would like it to look like this for the various symbols 5/7 6/7 7/7 Steve Linda Lucy |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists 2
Hi
Your example doesn't made sense to me, from what I have gather as : 1) postive differences = blank 2) negative differences = A 3) no differences = V Questions : what about the I for Leo and V for Lucy? I don't understand your 2nd requirement. You need to elaborate and provide an example. -- 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 Just like to say thanks to everyone who answered my previous questions as the spreadsheet now does what I needed to do but now I have another problem. Quick recap 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 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 This has been fixed by Francis who suggested =IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) and a conditional formatting Which works fine. I now need a new column added explaining why there scores have stayed the same or gone negative. This will require me to put A, I, i, or V in the next column. This is something that I need to do every day. Its a bit of a pain having to do this every day from scratch. I was wondering if there was a formula that would automatically copy over the settings from yesterdays sheet. A potential issue is that it would need to take into account the new position that somebody would be in and also need to be flexible enough that I can over ride it without deleting the formula. Rank Name points Difference Reason 1 Ian 110000 100000 2 Steve 9000 0 V 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 I 7 Phil 3789 -211 A 8 Linda 3000 0 V 9 Lucy 1020 20 V 10 skinner 892 -1108 A The 2nd problem is that i need a new worksheet that will show how many times somebody has one of the new symbols. I.E I need all the agents that have an symbol beside their names for 6/7, 5/7 of the work sheets. I would like it to look like this for the various symbols 5/7 6/7 7/7 Steve Linda Lucy |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists 2
Hi there Francis
sorry about the explanation Everybody that either has a negative score or a 0 score must have a reason and will need to have a symbol beside there name it can be any one of the 5 symbols ands its possible for 1 person to have 5 different symbols through out the week. What i hope is possible is that after manually entering all the data on one day instead of having to do that again for the next day, Is that there is a formula in place that will look up the person name and copy the symbol over automatically. I will still go through the list again checking that the right symbol is valid and if not manually changing the symbol to another one. This way I will only be changing 10 symbols a day instead of typing in 200 symbols every day. The 2nd problem is I need a total of how many people have the symbol beside them a certain number of times throughout the week. Basically a table that shows me every agent that has had the symbol A beside them for 5 out of the 7 days or 3 days out of the 7 days etc . Symbol A 3/7 4/7 5/7 6/7 Steve Peter Chris Ian Symbol V 3/7 4/7 5/7 6/7 Phil Linda Lucy Skinner Thanks again for any help you can offer. "Francis" wrote: Hi Your example doesn't made sense to me, from what I have gather as : 1) postive differences = blank 2) negative differences = A 3) no differences = V Questions : what about the I for Leo and V for Lucy? I don't understand your 2nd requirement. You need to elaborate and provide an example. -- 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 Just like to say thanks to everyone who answered my previous questions as the spreadsheet now does what I needed to do but now I have another problem. Quick recap 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 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 This has been fixed by Francis who suggested =IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) and a conditional formatting Which works fine. I now need a new column added explaining why there scores have stayed the same or gone negative. This will require me to put A, I, i, or V in the next column. This is something that I need to do every day. Its a bit of a pain having to do this every day from scratch. I was wondering if there was a formula that would automatically copy over the settings from yesterdays sheet. A potential issue is that it would need to take into account the new position that somebody would be in and also need to be flexible enough that I can over ride it without deleting the formula. Rank Name points Difference Reason 1 Ian 110000 100000 2 Steve 9000 0 V 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 I 7 Phil 3789 -211 A 8 Linda 3000 0 V 9 Lucy 1020 20 V 10 skinner 892 -1108 A The 2nd problem is that i need a new worksheet that will show how many times somebody has one of the new symbols. I.E I need all the agents that have an symbol beside their names for 6/7, 5/7 of the work sheets. I would like it to look like this for the various symbols 5/7 6/7 7/7 Steve Linda Lucy |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlighting the differences between lists 2
Hi
if you merely want to return the previous day's symbol which relates to a particular name, try this in E2, Col E1 being your reason for the symbol =IF(VLOOKUP(B11,Sheet2!$B$10:$E$20,4,0)=0,"",VLOOK UP(B11,Sheet2!$B$10:$E$20,4,0)) On your the other requirement, I need sometime to work out the solution. I suggest that you post a new thread for this returning names for a summary -- 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 Francis sorry about the explanation Everybody that either has a negative score or a 0 score must have a reason and will need to have a symbol beside there name it can be any one of the 5 symbols ands its possible for 1 person to have 5 different symbols through out the week. What i hope is possible is that after manually entering all the data on one day instead of having to do that again for the next day, Is that there is a formula in place that will look up the person name and copy the symbol over automatically. I will still go through the list again checking that the right symbol is valid and if not manually changing the symbol to another one. This way I will only be changing 10 symbols a day instead of typing in 200 symbols every day. The 2nd problem is I need a total of how many people have the symbol beside them a certain number of times throughout the week. Basically a table that shows me every agent that has had the symbol A beside them for 5 out of the 7 days or 3 days out of the 7 days etc . Symbol A 3/7 4/7 5/7 6/7 Steve Peter Chris Ian Symbol V 3/7 4/7 5/7 6/7 Phil Linda Lucy Skinner Thanks again for any help you can offer. "Francis" wrote: Hi Your example doesn't made sense to me, from what I have gather as : 1) postive differences = blank 2) negative differences = A 3) no differences = V Questions : what about the I for Leo and V for Lucy? I don't understand your 2nd requirement. You need to elaborate and provide an example. -- 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 Just like to say thanks to everyone who answered my previous questions as the spreadsheet now does what I needed to do but now I have another problem. Quick recap 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 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 This has been fixed by Francis who suggested =IF(ISNA(C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)),"",C2-VLOOKUP(B2,Sheet1!$B$2:$C$11,2,0)) and a conditional formatting Which works fine. I now need a new column added explaining why there scores have stayed the same or gone negative. This will require me to put A, I, i, or V in the next column. This is something that I need to do every day. Its a bit of a pain having to do this every day from scratch. I was wondering if there was a formula that would automatically copy over the settings from yesterdays sheet. A potential issue is that it would need to take into account the new position that somebody would be in and also need to be flexible enough that I can over ride it without deleting the formula. Rank Name points Difference Reason 1 Ian 110000 100000 2 Steve 9000 0 V 3 Peter 8900 1900 4 Chris 8800 800 5 Gus 6200 1200 6 Leo 6000 0 I 7 Phil 3789 -211 A 8 Linda 3000 0 V 9 Lucy 1020 20 V 10 skinner 892 -1108 A The 2nd problem is that i need a new worksheet that will show how many times somebody has one of the new symbols. I.E I need all the agents that have an symbol beside their names for 6/7, 5/7 of the work sheets. I would like it to look like this for the various symbols 5/7 6/7 7/7 Steve Linda Lucy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlighting the differences between lists. | Excel Discussion (Misc queries) | |||
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) | |||
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) |