Friday Challenge – Find Unique List in Excel

Find a Unique Class List-in Excel
Find a Unique Class List-in Excel

It is time for another Friday Challenge.  This is based on actual scenario from a recent project.

A user had a large list of values in over 220 columns and over 5800 rows.  All in all, there were over 233,000 data points.  Many of the values were duplicated but they wanted to find the complete unique list of values in the data.

Here is a mock up of similar sample data in the format received for the project:

Class Sample Data in Excel
Sample Data in Excel

Video Review:

The Sample Challenge Data: 50 Columns, 2,000 Rows of 69,650 Data Points (some cells contained blanks)

The Task: Download the sample file and find a unique list such that it could be used for a picklist (i.e. don’t just find the number, but find all the values so that you can send a list to the client)

The Results: Leave a comment below with your count of unique classes and how you did it (manually is an option, but it will take awhile.  First see if you can do it some other way.

Monday Afternoon: I will post one way to do this in the next blog post, but I am sure that I am not the smartest person in the room, so I am excited to hear all about your results and how you were able to solve this problem.

Sample Data File: Sample-Data.xlsx

Good luck!!

Steve=True

6 COMMENTS

    • Thanks for our comment. I will have to do more research on Get and Transform to see how you did it. Unfortunately, you may want to review/test as this is not the correct answer. 🙁

      • After watching the video again i realized i removed too much the part “ACCT 6380 -” must also be taken in account. the answer is then 186.
        The process in Get and transform starts by unpivoting the data and then removing unwanted columns and further cleaning the data by
        – Filtering out unwanted characters
        – TRIM / The old column can then be removed
        – Remove duplicates

LEAVE A REPLY

Please enter your comment!
Please enter your name here