Transpose from multiple column,

Question

Hello together

Maybe someone can help me. I've already tried transpose and cross-tab, but I can't get the right end result.

The following starting position:

DateWardPatientsBedsWardPatientsBedsWard...01.01.2019540110520184081402.01.2019540310520184081503.01.20195405105201840813

There is an Excel sheet with the bed occupancy of a clinic.

They have one row per date and then simply filled in the name of the department in the Ward columns. Then the number of patients on that day and then how many beds they had open.

For this client this is done over several columns, a total of almost 12 departments.

Because this is a very difficult representation to analyse, I would like to have it in the following representation:

DateWardPatientsBeds01.01.201954011001.01.20195201801.01.201940814...02.01.201954031002.01.20195201802.01.201940815...

How do I do that?

I have attached a sample file

Sample.xlsx

Qiu · Answer

@Mirco_Kunz 
This one will work for even 12 departs.

1206-Mirco_Kunz.yxmd

JP_SDAK · Answer

I started this before taking my daughter to swim this morning.  Decided to go ahead and finish and post (mostly as an exercise for myself  since  WernerE   already gave a much more elegant example😀)

example.yxmd

WernerE · Answer

Hi @Mirco_Kunz

You should be able to achieve the desired result with the attached and add as many departments as you like.

Let me know if you have any questions.

Example.yxzp