Multicolumn spreadsheet: extract unique values in Column 1, given same values in Columns 3 and 4?

Question

Here's a problem: given a (large) spreadsheet with userIDs in Column 1, a time/date pair in Column 2, a course code in Column 3, and a course title in Column 4: I want to produce a report showing how many unique users have taken each course. That is, given that user 12345 may have opened the same online course six times, and thus be on six rows, I want my report to show only user 12345 only once under that course ID.

I believe one way to do that would be to create another sheet with a column consisting of the course code, user name, and course title all combined into a single string, from which I could then eliminate duplicate values, but I'm wondering if there's a better way.

Answer 1

If you are using Microsoft 2007 or 2010, you can use Data>Remove Duplicates, and eliminate the concatenated column step.

In Excel 2003 you can use Data>Filter>Advanced Filter>Unique Values Only to display the rows with unique values in the columns you select.

You could also create a simple pivot table to do this.

Answer 2

In column 5 (E) put the following function all the way down the column, you may need to adjust the references.

 =1+COUNTIFS(C:C,C1)-COUNTIFS(A:A,A1,C:C,C1)

Then sort the entire chart by column C ascending followed by column E ascending. That's important. Then paste all the data in columns C and D to column G and H. Eliminated duplicates in column G and H only. Then in column I put this function:

 =VLOOKUP(G1,C:E,2,FALSE)

That will produce a chart which shows each course and the number of unique users. If you want, you can put the columns G to I part on a new sheet, just make sure the references line up.

I think that's what you were asking for, but I don't like it and I would just do your original suggestion of copy to a new sheet and remove duplicates. Or better: if this is a reoccurring problem, create a database. Microsoft Access makes that really easy.