Data Manipulation¶
Problem: SUM of Digits in a cell
Can you write a formula to generate the SUM of all digits in a cell?
Solution:
To use when you are sure that there are only digits in the column:
=SUMPRODUCT(--MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1))
But if there are other characters too use this:
=SUMPRODUCT((LEN(B3)-LEN(SUBSTITUTE(B3,ROW(1:9),"")))*ROW(1:9))
Problem: Uniqueness Check
Given the data below, please answer the following questions
| Region | ID |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
| C | 4 |
| B | 3 |
| C | 4 |
This is a 3 part question:
Given a table of data how do you tell if it has duplicates?
Create a table with distinct values from this
Can you do a conditional duplicate check on this table?
Solution:
You can check for duplicates using:
= COUNTIF($B$2:$B$7)
Rows with value > 1 has duplicates
Inorder to create a table with Unique values there are 2 ways:
Select the table and click on remove duplicates
If you want to keep the source table and create the unique value table elsewhere use:
=UNIQUE(A2:B7)
Conditional check can be done using IF clause, for example if you want to check duplicates only for ID > 3 you can use something like:
=IF(B2>3,COUNTIF($B$2:$B$7,B4),0)