Here’s a dataset with 4 columns and 2 rows. In column A, there is Patient ID and in the other columns are observations recorded by 3 Doctors for each patient. As can be seen, in each cell there are multiple observations which are either separated by Alt+Enter (another line in the same cell) and/or a comma. To make things more complicated, there are duplicate text entries appearing in each cell. In cell B2, Creative appears twice and in cell D2, Fatigue, headaches and stress each appear twice. Please also note that the entries appear in mixed case in each cell i.e. in cell D2, the first occurrence of stress is in proper case and the second instance is in small case.
The objective is to remove duplicates from each cell (disregarding case sensitivity) and standardize the multiple entries with an Alt+Enter in each cell. The expected result is shown in the green table below
I have solved this problem using Power Query a.k.a. Data > Get & Transform in Excel 2016 and higher versions. You may download my solution workbook from here.
Remove duplicates from each cell of a dataset
{ 1 Comments }