This is the worst type of input data to get. Each row has been split into two (or sometimes more) rows. We want those to be combined into a single row. In an earlier article, I have shown one method of doing this.
Now here is a simpler, faster and more powerful method using Power Query.
(Estimated reading time 12 min)
Just one button to press. Data – Remove Duplicates.
Data should be a table (or like a table – column headings with data below). If it is a table choose Table Tools – Remove Duplicates.
By default, duplication is checked for ENTIRE ROW.
Remove checkboxes to check for duplicates for specific fields only.
Be careful. Duplicate rows are DELETED.
This is unlike Advanced Filter – Unique Rows only – where duplicates are HIDDEN.
For large data – Power Query – Remove Duplicates is infinitely more powerful and faster. We will cover it in a separate article.
Single row of data split into two rows! Trying to clean-up such data is a frustrating experience. Here is how you do it – faster and smarter! 3 min video
Experts tell us the best practices. But we do lot of mistakes before we can find the best way. Documenting the mistakes – which is what I call “Worst Practices” is also important.
This worst practice is very common. I have found it across the world and across industries. Read on and confirm that you are not making this mistake.