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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Date 1</td><td style=";">Date 2</td><td style=";">Date 3</td><td style=";">Date 4</td><td style=";">Date 5</td><td style=";">Date 6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Person 1</td><td style="text-align: center;;">Person 2</td><td style="text-align: center;;">Person 3</td><td style="text-align: center;;">Person 4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Bob</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 1</td><td style="font-weight: bold;text-align: center;color: #008000;;">Sue</td><td style="font-weight: bold;text-align: center;color: #008000;;">Frank</td><td style="font-weight: bold;text-align: center;color: #008000;;">Gail</td><td style="font-weight: bold;text-align: center;color: #008000;;">Glenda</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Sue</td><td style="font-weight: bold;text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 2</td><td style="font-weight: bold;text-align: center;color: #008000;;">Sue</td><td style="font-weight: bold;text-align: center;color: #008000;;">Frank</td><td style="font-weight: bold;text-align: center;color: #008000;;">Mary</td><td style="font-weight: bold;text-align: center;color: #008000;;">Keith</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">John</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 3</td><td style="font-weight: bold;text-align: center;color: #008000;;">Bob</td><td style="font-weight: bold;text-align: center;color: #008000;;">John</td><td style="font-weight: bold;text-align: center;color: #008000;;">Gail</td><td style="font-weight: bold;text-align: center;color: #008000;;">Glenda</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Frank</td><td style="font-weight: bold;text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 4</td><td style="font-weight: bold;text-align: center;color: #008000;;">Bob</td><td style="font-weight: bold;text-align: center;color: #008000;;">Sue</td><td style="font-weight: bold;text-align: center;color: #008000;;">Mary</td><td style="font-weight: bold;text-align: center;color: #008000;;">Keith</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Mary</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 5</td><td style="font-weight: bold;text-align: center;color: #008000;;">John</td><td style="font-weight: bold;text-align: center;color: #008000;;">Frank</td><td style="font-weight: bold;text-align: center;color: #008000;;">Keith</td><td style="font-weight: bold;text-align: center;color: #008000;;">Glenda</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Gail</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Date 6</td><td style="font-weight: bold;text-align: center;color: #008000;;">Bob</td><td style="font-weight: bold;text-align: center;color: #008000;;">John</td><td style="font-weight: bold;text-align: center;color: #008000;;">Mary</td><td style="font-weight: bold;text-align: center;color: #008000;;">Gail</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Keith</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Glenda</td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">x</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Appendix</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">$A$2:$A$9,AGGREGATE(<font color="Green">15,6,ROW(<font color="Purple">$A$2:$A$9</font>)-ROW(<font color="Purple">$A$2</font>)+1/(<font color="Purple">(<font color="Teal">$B$1:$G$1=$J2</font>)*(<font color="Teal">$B$2:$G$9="x"</font>)</font>),COLUMNS(<font color="Purple">$A:A</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[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