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

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.

-
Sounds like the way I'd do it, but maybe someone else has a better idea. I've faced similar problems with analyzing results of a vulnerability report, and the method you describe is similar to what I've done in the past. – Iszi Sep 30 '11 at 18:43

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.

-
Excel 2003 for the time being, but thanks. – CarlF Sep 30 '11 at 20:10
+1 on the pivot table – brettdj Oct 1 '11 at 4:01

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.

-
Thanks, I'll try this. As for switching to a database, that's in progress, but we still have to use the old data that's in this spreadsheet. – CarlF Sep 30 '11 at 20:08
@CarlF Good show with switching to a database. When I arrived at the school where I work most of the stuff was in Excel. Needless to say, I've spent my share of time adding features to the little Access database which now needs to be upsized to SQL Server. – Ryan Clarke Sep 30 '11 at 20:13