Hey Friends,
Welcome back to issue #3 of The Sheet.
Here is the brief summary of what’s in The Sheet this week:
💡TheSheetTip: Combine multiple columns, whilst removing duplicates
📖 What we’re reading: The Pathless Path
If you’re finding the content of The Sheet useful, please share it with a friend or colleague who would find it useful. We’re on a mission 🚀 to help more people like you become more efficient in Excel - working smarter 🧠, NOT harder!
=💡TheSheetTip(Combine multiple columns, whilst removing duplicates)
Have you ever had a situation where you need to combine your data in a single column whilst removing any duplicates at the same time? Thankfully this is relatively simple, with the use of two functions.
To achieve this we will use 2 functions, one inside the other:
UNIQUE - this formula removes all duplicates for the selected array
TOCOL - this formula stacks multiple columns of data into one
Here’s what we’d need to type: =UNIQUE(TOCOL(Array))
Note: The ‘Array’ is simply the data we are interested in (B5:D12 in our example)
In the example below we want a list of all the students who have won an award. However, some students have won multiple awards and we don’t want them to appear twice.
📖 What we’re reading: The Pathless Path by Paul Millerd
This week, we wanted to share another book we have been reading over here at Functional Excel. "The Pathless Path" by Paul Millerd is a great read for anyone who is interested in personal and professional development. The book explores the idea that there is no one "right" path in life, and that success and happiness can be found by following your own unique path. So, if you're looking for some inspiration and guidance, be sure to check out "The Pathless Path"!
🖇️ https://amzn.eu/d/7OG6JQ4 [This is not an affiliate link]
That's it for this issue of The Sheet. Don’t forget you can check out previous issues directly on our substack page.
Until next time - Happy Excel-ing
Functional Excel Team