Transposing/Index & Matching? Data from Rows to Columns

[FuriousD]

I'd like to use Index/Match or something similar to fix how a report runs for me. I have duplicate data in columns (req number) but unique data associated with it (Approver). How would I accomplish this so I can have each approver in a separate column. I've tried to transpose, but it doesn't give the results in a functional way.

Example - How it is currently formatted

[TABLE="width: 500"]
<tbody>[TR]
[TD]Req Number[/TD]
[TD]Approver Name[/TD]
[/TR]
[TR]
[TD]123
[/TD]
[TD]MacGuyver[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]BA Barakus[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]AC Slater[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]Zach Morris[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]Kelly Kapowski[/TD]
[/TR]
</tbody>[/TABLE]

How I need it formatted:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Req[/TD]
[TD]Approver[/TD]
[TD]Approver[/TD]
[TD]Approver[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]MacGuyver[/TD]
[TD]BA Barakus[/TD]
[TD]AC Slater[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]Zach Morris[/TD]
[TD]Kelly Kapowski[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


TIA

 

[Scott T]

Excel 2010
	A	B	C	D
1	Req Number	Approver Name
2	123	MacGuyver
3	123	BA Barakus
4	123	AC Slater
5	321	Zach Morris
6	321	Kelly Kapowski
7
8
9	Req	Approver	Approver	Approver
10	123	MacGuyver	BA Barakus	AC Slater
11	321	Zach Morris	Kelly Kapowski
Sheet4

Cell Formulas
Range	Formula
B10	{=IF(COLUMNS($B10:B10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:B10))))}
B11	{=IF(COLUMNS($B11:B11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:B11))))}
C10	{=IF(COLUMNS($B10:C10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:C10))))}
C11	{=IF(COLUMNS($B11:C11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:C11))))}
D10	{=IF(COLUMNS($B10:D10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:D10))))}
D11	{=IF(COLUMNS($B11:D11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:D11))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[wideboydixon]

You can use formulas to extract the unique values and also to populate the values across:

Book1
	A	B	C	D
1	Reg Number	Approver Name
2	123	MacGuyver
3	123	BA Barakus
4	123	AC Slater
5	321	Zach Morris
6	321	Kelly Kapowski
7
8	Req Number
9	123	MacGuyver	BA Barakus	AC Slater
10	321	Zach Morris	Kelly Kapowski
Sheet1

Cell Formulas
Range	Formula
A9	{=IFERROR(INDEX($A$2:$A$6,MATCH(0,COUNTIF($A$8:$A8,$A$2:$A$6),0)),"")}
B9	{=IF($A9="","",IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A9,ROW($A$2:$A$6)-ROW($A$2)+1),COLUMNS($B9:B9))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy B9 formula across then copy the entire row down as necessary.

WBD

 

[wideboydixon]

Oops. That's what happens when you disappear in the middle of answering a question for a coffee break. I wasn't trying to suggest a better answer; they're practically the same anyway!

WBD

 

[FuriousD]

THANKS! With some minor modifications to fit my actual spreadsheet, I was able to make this work!

Excel 2010

	A	B	C	D
Req Number	Approver Name
MacGuyver
BA Barakus
AC Slater
Zach Morris
Kelly Kapowski
Req	Approver	Approver	Approver
MacGuyver	BA Barakus	AC Slater
Zach Morris	Kelly Kapowski

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]123[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]321[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]321[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]123[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]321[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]{=IF(COLUMNS($B10:B10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:B10))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]{=IF(COLUMNS($B10:C10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:C10))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D10[/TH]
[TD="align: left"]{=IF(COLUMNS($B10:D10)>COUNTIF($A$2:$A$6,$A10),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A10,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B10:D10))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]{=IF(COLUMNS($B11:B11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:B11))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]{=IF(COLUMNS($B11:C11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:C11))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]{=IF(COLUMNS($B11:D11)>COUNTIF($A$2:$A$6,$A11),"",INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6=$A11,ROW($B$2:$B$6)-ROW($B$2)+1),COLUMNS($B11:D11))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]