Unpivot columns

[lekkerbek]

Hi,

The solution is probably easy, but I can't get it to work as the outcome is not the same if I manually calculate.

I have an excelsheet covering the following:

Article nr	Article group	0-30 number	0- 30 value	31-60 number	31-60 value	61-90 number	61-90 value
001	Article name	1	€ 10,50	5	€ 52,50	2	€ 21

I would like it to look like:

Article nr	Article group	Age	Number	Value
001	Article name	0-30	1	€ 10,50
001	Article name	31-60	5	€ 52,50
001	Article name	61-90	2	€ 21

Could someone help me with this please? When I tried unpivotting I got duplicates and when I tried to remove those I apparantly removed too much as the total sum did not match the excelsheet anymore.

Thanks in advance. 

[Anonymous · Accepted solution]

Hi @lekkerbek ,

Please follow the below steps to acheive your request. 

• First Unpivot the columns Fig 1.1
• Split the columns based on space delimiter to the Right-most delimiter Fig 1.2 and 1.3.
• Pivot the Number and Values to columns based on Value column Fig1.4.

Fig 1.1:

Fig 1.2:

Fig 1.3:

Fig 1.4:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lekkerbek]

Thank you both @Anonymous  and @Tahreem24 for your time to help me. Much appreciated.

[Tahreem24]

@lekkerbek ,

You want something like below screen shot? (I tried to replica your requirement at my side)

Please Give KUDOS to my post and accept this as a solution if it helps you!

[Tahreem24]

@lekkerbek ,

Yeah, the Number Column is nothing but the Value column.

[lekkerbek]

The number column should reflect the number of articles in stock. The value column reflects the total value of the stock of that article. Those two can't be combined into one column.

[Tahreem24]

@lekkerbek ,

Can you please give screen shot or upload Excel with sample data?

[lekkerbek]

I seem to be unable to upload an excelfile, so I included a screenshot in the opening post (and also here).

[Tahreem24]

Hi @lekkerbek ,

I replicated your scenario at my end. Let me know if I missed anything. 

Please dont forget to give KUDOS!

Input:

Output:

[lekkerbek]

@Tahreem24  yes, but it should also include the column "value".