Index and Match ??? Help needed

[dtwyford]

Good day everyone. I could use some guidance. I have table that is used for people to pick dates they want to volunteer their time for. I need to translate the date

from

	Date 1	Date 2	Date 3	Date 4	Date 5	Date 6
Bob			x	x		x
Sue	x	x		x
John			x		x	x
Frank	x	x			x
Mary		x		x		x
Gail	x		x			x
Keith		x		x	x
Glenda	x		x		x

<tbody>
</tbody>

to

	Person 1	Person 2	Person 3	Person 4
Date 1	Sue	Frank	Gail	Glenda
Date 2
Date 3
Date 4
Date 5
Date 6

<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>
</tbody>

I am looking for a dynamic formula for the "TO" table. Any thoughts?

D

 

[jim may]

Copy cell L3 to all other cells in the Range(L3:O8).


Excel 2010

	K	L	M	N	O
1		Person 1	Person 2	Person 3	Person 4
2		Sue	Frank	Gail	Glenda
3	Date 1	x	x	x	x
4	Date 2	x	x
5	Date 3			x	x
6	Date 4	x
7	Date 5		x		x
8	Date 6			x

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula L3 =IF(INDEX($B$3:$G$10,MATCH(L$2,$A$3:$A$10,0),MATCH($K3,$B$2:$G$2,0))="x","x","") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[dtwyford]

Jim,

Thanks, but not quite. The green names are an example of what I want the "TO" table to look like.
The "FROM" Tables top row is the dates, the left most column are the names, the cells under and to the right are the various selections I need to translate / pivot into the "TO" table.
Any tweaks you can recommend?
D

Worksheet Formulas Cell Formula L3 =IF(INDEX($B$3:$G$10,MATCH(L$2,$A$3:$A$10,0),MATCH($K3,$B$2:$G$2,0))="x","x","") <tbody> </tbody>

<tbody>
</tbody>

[/QUOTE]

 

[jim may]

To accomplish the bringing over of the Names I am unable to do it in using a formula. But I can via VBA.
So here's the (Standard Module) Code.

Sub EliminateBlankRowsWhenCopying()
lr = Range("B" & Rows.Count).End(xlUp).Row 'Your from table Date 1 Column
Set Rng = Range("B3:B" & lr)
Application.Calculation = xlCalculationManual
    Rng.SpecialCells(xlCellTypeConstants).Offset(0, -1).Copy
    Worksheets("Sheet1").Range("L2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Fluff]

ADVERTISEMENT

With a formula

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Date 1	Date 2	Date 3	Date 4	Date 5	Date 6				Person 1	Person 2	Person 3	Person 4
2	Bob			x	x		x			Date 1	Sue	Frank	Gail	Glenda
3	Sue	x	x		x					Date 2	Sue	Frank	Mary	Keith
4	John			x		x	x			Date 3	Bob	John	Gail	Glenda
5	Frank	x	x			x				Date 4	Bob	Sue	Mary	Keith
6	Mary		x		x		x			Date 5	John	Frank	Keith	Glenda
7	Gail	x		x			x			Date 6	Bob	John	Mary	Gail
8	Keith		x		x	x
9	Glenda	x		x		x
Appendix

Cell Formulas
Range	Formula
K2	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,ROW($A$2:$A$9)-ROW($A$2)+1/(($B$1:$G$1=$J2)*($B$2:$G$9="x")),COLUMNS($A:A))),"")

 

[dtwyford]

Jim ,Thank you.
Darren

To accomplish the bringing over of the Names I am unable to do it in using a formula. But I can via VBA.
So here's the (Standard Module) Code.

Sub EliminateBlankRowsWhenCopying()
lr = Range("B" & Rows.Count).End(xlUp).Row 'Your from table Date 1 Column
Set Rng = Range("B3:B" & lr)
Application.Calculation = xlCalculationManual
    Rng.SpecialCells(xlCellTypeConstants).Offset(0, -1).Copy
    Worksheets("Sheet1").Range("L2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, Transpose:=True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[dtwyford]

'Fluff',
Thank you. I have never used the aggregate function before.
Darren

 

[Fluff]

You're welcome & thanks for the feedback