# Splitting a List into Multiple Lists

If you deal with data as part of your business, you must have stumbled upon this problem every now and then. Many of you look for a solution in Excel or Googlesheets, but a solution in Python is a lot more convenient, especially when you have access to online spaces like Jupyter Notebooks or Google Colab.

# Data Transformation using Excel and Tableau

Data transformation is fun! More so when you’re doing it using tools like Microsoft Excel and Tableau. While programming languages like Python and R facilitate this very easily, it’s not everybody’s cup of tea. Excel and Tableau are relatively easier to understand.

# Counting Unique Values in a List

## Problem and Dataset

I recently had a client who wanted a quick way to count the number of unique values in a list. In the following case, the number of unique names in the data in column A. ## Formula

The general format of the formula is:

`=SUMPRODUCT(1/COUNTIFS(data,data))`

## How it Works

The trick is in knowing how the formula actually works.

If you evaluate the formula, this is what you get:

`=SUMPRODUCT(1/{7;7;7;7;7;1;7;7;1;2;2})`

1. The formula counts the number of instances of each name in the list (7; 7; 7; ….), and stores the count in an array.
2. 1/Array creates fractions for each value in the list.
3. SUMPRODUCT then sums up the fractions for each name (7*1/7, 2*1/2, etc.) and gives you the number of 1s in the calculation.

Genius, I say!

## References

1. Count unique values in a range with COUNTIF