Create new worksheet with Excel Macros/Formulas from another works
 

Hi,

I have one worksheet with 3 colums and rows.
I need to create a new dinamic worksheet from the data on the 1st sheet.
Columns in the 1st sheet a

Material Plant Quantity
----------------------------------------------
AAA 1 100
AAA 2 50
AAA 3 20
BBB 1 30
BBB 3 10

I know descriptions for the Plants: 1 - "East", 2 - "West", 3 - "South".
I have to create macros or formulas to build a new worksheet from
information on this one. It is basically take a Material group by, then Plant
and sum of Quantity for that specific Material and specific Plant , and build
a column for that specific Plant, with provided description. So if its Plant
=1 , then column is "East", and so on.
So, essentially the new worksheet shooud look like this.
Quantity for Materials based on Plant.

Material East West South
-----------------------------------------------------------
AAA 100 50 20
BBB 30 - -
BBB - - 10

Can someone give me tips on how to build that new worksheet and how should i
build macro?

Thank you,
Vadim.

Create new worksheet with Excel Macros/Formulas from another works
 

Hi,

That's a pivot table. It's all there for you...

Put Pivot Table into excel's help & you can't go wrong.

Sam


"vadim0770" wrote:

Hi,

I have one worksheet with 3 colums and rows.
I need to create a new dinamic worksheet from the data on the 1st sheet.
Columns in the 1st sheet a

Material Plant Quantity
----------------------------------------------
AAA 1 100
AAA 2 50
AAA 3 20
BBB 1 30
BBB 3 10

I know descriptions for the Plants: 1 - "East", 2 - "West", 3 - "South".
I have to create macros or formulas to build a new worksheet from
information on this one. It is basically take a Material group by, then Plant
and sum of Quantity for that specific Material and specific Plant , and build
a column for that specific Plant, with provided description. So if its Plant
=1 , then column is "East", and so on.
So, essentially the new worksheet shooud look like this.
Quantity for Materials based on Plant.

Material East West South
-----------------------------------------------------------
AAA 100 50 20
BBB 30 - -
BBB - - 10

Can someone give me tips on how to build that new worksheet and how should i
build macro?

Thank you,
Vadim.

Create new worksheet with Excel Macros/Formulas from another w
 

Hi Sam,

I have tried to record a macro on the 2nd worksheet. Selecting data on the
first sheet, pasting to the 2nd worksheet and then do "Data-Pivot Table and
Pivot Chart Report. Created a Pivot table.

Selected :
Material - Row.
Plant - Column.
Quantity - Data.

Also, tried other selections for Material, Plant and Quantity, but can' get
it to see results like that:

Material 1 2 3
-----------------------------------------------------------
AAA 100 50 20
BBB 30 - -
BBB - - 10

Any tips?

Thanks.

"Sam Wilson" wrote:

Hi,

That's a pivot table. It's all there for you...

Put Pivot Table into excel's help & you can't go wrong.

Sam


"vadim0770" wrote:

Hi,

I have one worksheet with 3 colums and rows.
I need to create a new dinamic worksheet from the data on the 1st sheet.
Columns in the 1st sheet a

Material Plant Quantity
----------------------------------------------
AAA 1 100
AAA 2 50
AAA 3 20
BBB 1 30
BBB 3 10

I know descriptions for the Plants: 1 - "East", 2 - "West", 3 - "South".
I have to create macros or formulas to build a new worksheet from
information on this one. It is basically take a Material group by, then Plant
and sum of Quantity for that specific Material and specific Plant , and build
a column for that specific Plant, with provided description. So if its Plant
=1 , then column is "East", and so on.
So, essentially the new worksheet shooud look like this.
Quantity for Materials based on Plant.

Material East West South
-----------------------------------------------------------
AAA 100 50 20
BBB 30 - -
BBB - - 10

Can someone give me tips on how to build that new worksheet and how should i
build macro?

Thank you,
Vadim.