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

Req Number	Approver Name
123	MacGuyver
123	BA Barakus
123	AC Slater
321	Zach Morris
321	Kelly Kapowski

<tbody>
</tbody>

How I need it formatted:

Req	Approver	Approver	Approver
123	MacGuyver	BA Barakus	AC Slater
321	Zach Morris	Kelly Kapowski

<tbody>
</tbody>

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
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

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

Sheet4​

Array Formulas Cell 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))))} 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))))} 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))))} 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))))} 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))))} 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))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>