Re-arrange large amount of data

[sudipballa]

I have 9 lac records in excel with columns Plant / Material / Cost. These details are for 1.5 lac materials for 6 plants.

I need to re-arrange the data so that i have MATERIAL and all 6 plants in TOP and then data for 1.5 lac records. This will help me in doing easy comparision across plants and spot odd ones out.

- I dont think i can use Pivot or Power Pivot for this
- I tried to arrange the data manually using VLOOKUP, but it takes enormous time and gives up

Any help on this will be appreciated?

Thanks

 

[Hui]

Why not just use a Pivot table


And pivot tables will easily Handle 1048576 records as shown below


If you want to summarise / group records you can do that either in Pivot tables or extract the records to an intermediate table first

 

[sudipballa]

Why not just use a Pivot table
View attachment 34481

And pivot tables will easily Handle 1048576 records as shown below
View attachment 34483

If you want to summarise / group records you can do that either in Pivot tables or extract the records to an intermediate table first

Thanks for help Hui. I am trying to kind of transpose data and not summarizing/grouping it. Hence did not use PIVOT. But if Pivot can be used to get to the same, would be great. I couldnt download the excel from your reply, can you re-check.

 

[Chihiro]

Can be done easily with PowerQuery as well.

Highlight "Plant" & Pivot column. "Cost" as values column. Advanced option->Aggregate Value Function, choose "Don't Aggregate".

 

[Hui]

I didn't attach the file as it would be too large anyway

Exactly what output do you want?
That will help us come up with ideas to achieve it

 

[sudipballa]

Hello Hui,

If you refer my attachment, you will see the Desired Output. Basically I want the PLANTS on columns and Materials on Rows with their COST in between.

Thanks
Kishore

 

[Chihiro]

? @ sudipballa

Isn't Hui's first screenshot your desired output?

See attached. Both pivot table and PowerQuery table on the sheet.

 

[Hui]

My first post above has your required output and a PT displaying exactly that below it?

Please clarify

 

[sudipballa]

Can be done easily with PowerQuery as well.

Highlight "Plant" & Pivot column. "Cost" as values column. Advanced option->Aggregate Value Function, choose "Don't Aggregate".

View attachment 34510

Perfect Thanks a lot.