Ask Your Question
0

Swap unique cell data with rows?

asked 2019-10-01 18:22:07 +0200

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:

image description

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:

image description

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?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-02 00:18:33 +0200

gregors15 gravatar image

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.C:\fakepath\LOQ_20191001.ods

edit flag offensive delete link more

Comments

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 gravatar imageSS_HELP ( 2019-10-02 14:43:06 +0200 )edit

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!

SS_HELP gravatar imageSS_HELP ( 2019-10-02 15:13:20 +0200 )edit

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

gregors15 gravatar imagegregors15 ( 2019-10-02 17:21:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-01 18:22:07 +0200

Seen: 15 times

Last updated: Oct 02