lots of data to be sorted for each unique record

[vlookupabyss]

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]

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]

Many thanks for your help it makes sense and should sort the sorting out! i
will give it a go tommorow at work!!

 

[vlookupabyss]

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]

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]

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]

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.

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