Merge rows with same records

Question

I have following table as a dataset. I am looking forward to merge cells with same records for all the columns

CountryProvinceCityCANONTorontoCANONTorontoCANONMississaugaCANABEdmontonCANABCalgaryCANNSHalifaxCANNSHalifax NSLunenburgUSCASFUSCASFUSTXAustinUSTXAustinUSTXDallasUSTXDallas

Desired output is: I am not able to paste the desired output. But I am looking to merge cells in a way that every country, province and city appears only once.

Sample input and output is attached with this.

Sample_Alteryx.xlsx

jirikrecek · Answer

You don't need Alteryx for this at all! What you have shown in your second screenshot is a very simple Excel Pivot table in Tabular formatting. That is literally all you need. No Alteryx required.

Yoshiro_Fujimori · Answer

Alteryx is powerful when used to cleanse data from a dirty data (like a table with merged cells) to a clean data (like a table in normalized form),

but not so powerful to make a clean data back to a dirty data.

For your use case, maybe you would be better to use Data Visualization tools, like Tableau, Power BI etc.

(Though this is not Tableau community, just for example)

with Tableau, you can make this table with a few clicks, keeping the original data clean.

Pooja_Khanna · Answer

I tried multi-row formula but that's not helping as my purpose is to avoid users to sort data and mess-up with rows. That's why merging is needed.