Swap unique cell data with rows?

[SS_HELP]

Someone gives me a spreadsheet of a schedule. The columns are the days of the month, and the rows are the workers names. The cells hold the names of the places they are scheduled. Two people cannot be scheduled at the same place on the same day.

It might looks like this:

I would like to convert the spreadsheet so that the rows are the places, and the cells hold the peoples names. I would like the output to look like this:

I could do this manually but was wondering if there was some functionality in Calc that would make automating this process easier?

I have tried writing a formula to fill in the first cell for the ‘Bathroom’ row in my desired solution:

=INDIRECT(ADDRESS(MATCH($A$11,B3:B6,0)+2,1))

Is there a simpler way?

[gregors15]

Hi, have a look at the attached, using index/match and $ on the static cell refs, seems to populate the way you want, I have only done 7 days, but you can copy B10 to B13 across to however many days are in the month. Let us know if it helps.LOQ_20191001.ods

[SS_HELP]

Thanks! Just curious, you give the following formula for the first cell in the ‘bathroom’ row solution:

=INDEX($A$3:$A$6,MATCH($A10,B$3:B$6,0),0)

and the following formula in the first cell in the ‘den’ row solution:

=INDEX($A$3:$A$6,MATCH($A11,B$3:B$6,0),0)

The only difference is the first argument to the match function($A10 then $A11).

Is there a way to have the formula use the ‘current row’ instead of manually specifying it? Something like $A-CurrentRow? That way, a single formula could be used across all cells?

[SS_HELP]

I think I answered my own question!!

It looks like a combination of ‘INDIRECT’ and ‘ADDRESS’ can be used to construct a fully generic formula, as follows:

=INDEX($A$3:$A$6,MATCH(INDIRECT(ADDRESS(ROW(),1,3)),B$3:B$6,0))

Thanks again!

[gregors15]

Hi, I only $ the Column($A10) not the row, so to enable copy any where.