Remove Duplicates and Transpose ROWS to COLUMNS

[Pavan Kumar Sistla]

Hello,

Attached is the excel file with a column (column name: Account) highlighted in YELLOW. There are repetitions of Account name column. The requirement is to create a data model with these Account names in column instead of rows by removing duplicate rows.

Could you please help in creating this model?

Thank You and Regards,
Pavan.

 

[NARAYANK991]

Hi ,

Can you upload your workbook showing how the first 10 rows of data in the Sheet1 tab will look like after they have been processed , removing duplicates and transposing the Account names to columns instead of rows ?

Narayan

 

[Pavan Kumar Sistla]

Hi Narayana,

Thank you for the reply. Here is the updated file with sample in 1st sheet.

Kindly suggest as I have thousands of rows of data of this sort. Do we need to use Power Query/Power Pivot/Pivot Table or any VBA code.

Please suggest.

Thank You and Regards,
Pavan.

 

[NARAYANK991]

Hi ,

VBA can easily do what is required ; check back later.

Narayan

 

[NARAYANK991]

Hi ,

Can you confirm one point - will the duplicates be together throughout your data , or is it possible that they can be dispersed ?

Narayan

 

[John Jairo V]

Hi to all!

With Power Query is an easy task. Check file. Blessings!

 

[Pavan Kumar Sistla]

Hello John,

Thank you very much. I could do it with some extra time in normal Pivot Table.

However, Power Query can do it faster I guess. Could you please throw some light on how you achieved this task using Power Query which would be helpful.

Thank You and Regards,
Pavan.

 

[John Jairo V]

I'll try to explain (english is not my native language):

1. Convert the basis range into a table (Select any cell of your data, and later press Ctrl + T, or go to Insert - Table).

2. Go to Data - From Table/Range. This show you the Data into Power Query.

3. In Power Query, Select Account Column, and later go to Transform - Pivot Column. In Column Value, put Amount Column.

4. Later, you can rearrange Salaries and Additional Pay Columns, like you need the columns.

5. In Home, Go to on Close and Load. Done!

I hope it helps. Blessings!

 

[Pavan Kumar Sistla]

Thank you so much for the quick learning tips