How can I transpose?

[phildot]

Hi, 

I have a clue.

I have such a file

Avril	Wednesday	Thursday	Friday
Antoine	LCI	RTT
Philippe	L 4	D 4	L 4
Alan	CV	L 4	R
Francis	D 6	D 6	D 6

I'd like to translate it to this

	Wednesday	Thursday	Friday
LCI	Antoine
RTT		Antoine
L4	Philippe	Alan	Philippe
D4		Philippe
CV	Alan
R			Alan
D6	Francis	Francis	Francis

What kind of formula do I need to put in the columns 2,3 and 4 of the last table to get if filled by the first table?

thanks for the help!

[#2 · Accepted solution]

@phildot See attached.

[#3]

@phildot 

Another variant is with Power Query

let
    Source = Excel.CurrentWorkbook(),
    FilterRange = Table.SelectRows(Source, each ([Name] = "Range")),
    Range = FilterRange{[Name="Range"]}[Content],
    PromotHeaders = Table.PromoteHeaders(Range, [PromoteAllScalars=true]),
    UnpivotOtherThanNames = Table.UnpivotOtherColumns(
        PromotHeaders,
        {"Column1"},
        "Attribute", "Value"
    ),
    PivotBack = Table.Pivot(
        UnpivotOtherThanNames,
        List.Distinct(UnpivotOtherThanNames[Attribute]),
        "Attribute", "Column1"
    )
in
    PivotBack

[#4]

@Riny_van_EekelenThanks for your quick reply!

[#5]

@Sergei Baklan Thanks for your quick reply! 

[#6]

Hi @Riny_van_Eekelen 

One additional question: what if I want to have a second field to be checked here, e.g; to check A12 and A14?

=IFERROR(INDEX($A$2:$A$5;MATCH($A12;B$2:B$5;0);1);"")

[#7]

@phildot And what would be the result you would want to see then?

[#8]

@Riny_van_Eekelen 

My problem is precisely regarding resource planning crossed tables.

I have this origin table, filled in by the Conso team lead.

where is for On duty and / is for absent/off duty

I have to transpose it like this for a table that gathers several teams, where Name means "is working/present" and red means "absent"

Your formula helps me getting the name, by using P as the matching variable, but I'd need to have the "/" variable checked as well to render another result (red box or anything else, or name with in grey/red...

Not an easy one, I guess.

Anyway, thanks Riny for your time.

Phil

[#9]

@phildot Perhaps like in the attached file (columns G:J)?

Done with a simple IF statement that picks up the name from the left column in case of a "P", and leave blank if there is anything else but a "P". Then use Conditional Formatting to color blank cells red.

If this is not what you are looking for, please extend your example with a few more possible situations, as I don't really understand where the "Conso team lead" comes in here. But I guess you have several teams and you want to, somehow, summarise all workers' schedules into one table.

[#10]

Hi @Riny_van_Eekelen,

Thank you for your commitment to help me. Indeed I'm looking for aggregation of multiple teams plannings in one sheet.

I'm working on Google Sheets and finally found the right solution thanks to you:

=IFS(B18=$A$61;$A18;B18="/";"/"; B18="";"")

where A18 has the name of the person in row 18; B18 is his/her duty on the day of the B column, A61 is the duty reference (in this case P). So when P is in the B18, it shows the name of the person, when / is in B18 (off duty), it shows /, when B18 is blank (status forgotten), it shows a blank cell (that can be hightighted if red as an warning, through Conditional formatting).
Really nice I could count on you.

I'm philippe dautrebande, working for a media company in Belgium. Join me on LinkedIn if it pleases you. https://www.linkedin.com/in/philmobil/

Have a nice day, and take care of yourself and your dear ones.