Finding a case of two unique identifiers for a student

[roobydoo]

I have a list of around 6,000 entries in a spreadsheet. These are test records for students. Each time a student tests, a record is added to the list. Some students may have several records, some only one. Each student has a unique SSID number. In almost all cases, a student only has one SSID number. Occasionally, due to an error, a student may have tested under two different SSID numbers. Is there a way to find out, in this spreadsheet, any student who may have more than one SSID number? Here is a list of some of the column headings: SSID#, LName, FName, Student #, Grade, Ethnicity, Birthdate, Test Score. Can anyone help me?

 

[Akashwani]

Hi,

How about a Countif and a Helper column?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	SSID#	Lname	Fname	Student	*	*	*	*	*	Check
2	SS12345	Simpson	Lisa	Lisa Simpson	*	*	*	*	*	2
3	SS12346	Simpson	Bart	Bart Simpson	*	*	*	*	*	1
4	SS12347	Flanders	Tod	Tod Flanders	*	*	*	*	*	2
5	SS12348	Flanders	Rod	Rod Flanders	*	*	*	*	*	1
6	SS12349	Simpson	Lisa	Lisa Simpson	*	*	*	*	*	2
7	SS12350	Muntz	Nelson	Nelson Muntz	*	*	*	*	*	1
8	SS12351	Wiggum	Ralph	Ralph Wiggum	*	*	*	*	*	1
9	SS12352	Van Houten	Milhouse	Milhouse Van Houten	*	*	*	*	*	1
10	SS12353	Flanders	Tod	Tod Flanders	*	*	*	*	*	2
StudentList

The formulas used need to be copied down.

This will only work IF you DON'T have students with the same name!!

I hope it works for you.

Ak

 

[roobydoo]

Thanks so much for your time. This works perfectly!
regards,
Roobydoo

 

[Gday Bruce]

Taking Akashwani's example one step further;
If you add the birthdate to the name string in column D, students with identical names are catered for (unless they're twins).

 

[roobydoo]

Good idea. I will do that. I had to remove duplicates first - of the SSID and then I put in the formula and then sorted by the check column and all the students with duplicate SSIDs showed up. I love this forum because you are much better thinkers than I am!

 

[Akashwani]

Hi roobydoo,

Thanks for the feedback.

If you have 6,000 plus rows of data, I would suggest using Conditional Formatting to highlight the duplicates, or you could filter by column J in the sample data I gave.

To use C F...
Highlight your range from top left cell to bottom right cell within your range, select C F, in Excel 2007 select New Rule, Use a Formula etc and paste this into the box $J2>1 select your Format, click OK etc.

I hope that makes life a bit easier for you.

Good luck with your project.

Ak

 

[roobydoo]

Thank you. I never thought about conditional formatting. I use it occasionally but I always have to play around with the formula part to figure out what formula will work. So thanks for that.
Roobydoo