Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
I am busy working with an assessment report. The problem I am experiencing is
that for each person/record on the spreadsheet there are multiple scores for the assessment €“ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
So you want to keep the highest score--whether it's passing or failing?
With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €“ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
Many thanks for your help it makes sense and should sort the sorting out! i
will give it a go tommorow at work!! "Dave Peterson" wrote: So you want to keep the highest score--whether it's passing or failing? With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €€œ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
ive just been applying your solution to my problem and have encountered some
errors - for example after following your instructions on a simple example i have the problem of 1 being linked to different scores - both above and below the pass rate.(this is after sorted in ascending and descending order as suggested) Below is a graphic representation: name score john smith 78% john smith 42% john smith 61% john smith 89% john smith 40% alice green 87% alice green 54% alice green 90% alice green 12% So from the above data i need only the following scores: john smith 89% alice green 90 % (the highest score from both pass and fail) ive thought if sorting ascending and then using if statement with all the possible scores and assigning a reference number to resort and delete later but your suggestion seems the best way - just having trouble implementing it!!! "Dave Peterson" wrote: So you want to keep the highest score--whether it's passing or failing? With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €€œ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
Make sure you sort your data in ascending order by name and descending order by
score. Then you'll keep the best score of each name--it'll be at the top of each group of names. Your sample data didn't have the data sorted--at least when you posted it. vlookupabyss wrote: ive just been applying your solution to my problem and have encountered some errors - for example after following your instructions on a simple example i have the problem of 1 being linked to different scores - both above and below the pass rate.(this is after sorted in ascending and descending order as suggested) Below is a graphic representation: name score john smith 78% john smith 42% john smith 61% john smith 89% john smith 40% alice green 87% alice green 54% alice green 90% alice green 12% So from the above data i need only the following scores: john smith 89% alice green 90 % (the highest score from both pass and fail) ive thought if sorting ascending and then using if statement with all the possible scores and assigning a reference number to resort and delete later but your suggestion seems the best way - just having trouble implementing it!!! "Dave Peterson" wrote: So you want to keep the highest score--whether it's passing or failing? With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €€œ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
Hi
Many thanks for all your help - i am almost there - sorted correctly and getting numbers , but the numbers differ per record especially further down the sheet ...for example Lomile Hlolloane Quiz1 2007/12/08 07:12 2007/12/08 07:34 Completed 21 1 100 10 Lomile Hlolloane Quiz1 2007/12/08 07:01 2007/12/08 07:11 Completed 9 1 88 11 Lomile Hlolloane Quiz1 2007/12/06 10:43 2007/12/06 10:54 Completed 10 1 76 12 Lomile Hlolloane Quiz1 2007/12/08 06:31 2007/12/08 06:37 Completed 5 1 76 13 above for that indivdual the numbers are 10,11,12,13 not 1,2,3,4 etc...what am i doing wrong here??? thanks "Dave Peterson" wrote: Make sure you sort your data in ascending order by name and descending order by score. Then you'll keep the best score of each name--it'll be at the top of each group of names. Your sample data didn't have the data sorted--at least when you posted it. vlookupabyss wrote: ive just been applying your solution to my problem and have encountered some errors - for example after following your instructions on a simple example i have the problem of 1 being linked to different scores - both above and below the pass rate.(this is after sorted in ascending and descending order as suggested) Below is a graphic representation: name score john smith 78% john smith 42% john smith 61% john smith 89% john smith 40% alice green 87% alice green 54% alice green 90% alice green 12% So from the above data i need only the following scores: john smith 89% alice green 90 % (the highest score from both pass and fail) ive thought if sorting ascending and then using if statement with all the possible scores and assigning a reference number to resort and delete later but your suggestion seems the best way - just having trouble implementing it!!! "Dave Peterson" wrote: So you want to keep the highest score--whether it's passing or failing? With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €€œ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
lots of data to be sorted for each unique record
Did you modify the formula?
=countif(a$2:a2,a2) Did you sort your data before you inserted the column with the formula? Sometimes, it's good to take a small portion of data and get that working first. vlookupabyss wrote: Hi Many thanks for all your help - i am almost there - sorted correctly and getting numbers , but the numbers differ per record especially further down the sheet ...for example Lomile Hlolloane Quiz1 2007/12/08 07:12 2007/12/08 07:34 Completed 21 1 100 10 Lomile Hlolloane Quiz1 2007/12/08 07:01 2007/12/08 07:11 Completed 9 1 88 11 Lomile Hlolloane Quiz1 2007/12/06 10:43 2007/12/06 10:54 Completed 10 1 76 12 Lomile Hlolloane Quiz1 2007/12/08 06:31 2007/12/08 06:37 Completed 5 1 76 13 above for that indivdual the numbers are 10,11,12,13 not 1,2,3,4 etc...what am i doing wrong here??? thanks "Dave Peterson" wrote: Make sure you sort your data in ascending order by name and descending order by score. Then you'll keep the best score of each name--it'll be at the top of each group of names. Your sample data didn't have the data sorted--at least when you posted it. vlookupabyss wrote: ive just been applying your solution to my problem and have encountered some errors - for example after following your instructions on a simple example i have the problem of 1 being linked to different scores - both above and below the pass rate.(this is after sorted in ascending and descending order as suggested) Below is a graphic representation: name score john smith 78% john smith 42% john smith 61% john smith 89% john smith 40% alice green 87% alice green 54% alice green 90% alice green 12% So from the above data i need only the following scores: john smith 89% alice green 90 % (the highest score from both pass and fail) ive thought if sorting ascending and then using if statement with all the possible scores and assigning a reference number to resort and delete later but your suggestion seems the best way - just having trouble implementing it!!! "Dave Peterson" wrote: So you want to keep the highest score--whether it's passing or failing? With the data in A2:Bxxx (headers in row 1) I'd sort columns A:B by column A in ascending order and column B in descending order. Then I'd add a header in C1: Count In C2: =countif(a$2:a2,a2) and drag down. You'll see 1's for the highest score for each person. And 2, 3, ... for the duplicate name entries. Then apply data|Filter|autofilter to column C and filter to show the values greater than 1. Delete those visible rows, remove the filter and delete column C. You may want to copy to a new worksheet first--just in case! vlookupabyss wrote: I am busy working with an assessment report. The problem I am experiencing is that for each person/record on the spreadsheet there are multiple scores for the assessment €€œ for example John Smith may have 4 completed attempts of the assessment where he failed 3 times and passed once (the pass mark is 90% or more) Is there a fast way in which I can filter the data and remove the multiple entries so that if someone has passed only that score is shown and if they have failed only that score/status is shown?? I need to clean up the raw data report before I start to pull more data in from other sources via vlookup.. Any and all help much appreciated -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Attempting to sort unique/only count first record in each unique g | Excel Discussion (Misc queries) | |||
Filter for frist record in a sorted list | Excel Discussion (Misc queries) | |||
Filtering Duplicate Data to obtain Unique record | Excel Worksheet Functions | |||
EXTRACTING UNIQUE RECORD BASED ON CONDITION | Excel Worksheet Functions | |||
Display unique record | Excel Discussion (Misc queries) |