Pull unique values based on a different set of unique values (No macro)

[StewCrew]

I am trying to create a list of unique values that relate to another list of unique values.

Column A has a list of managers and column B has a list of supervisors that report to the manager in column A. There will be multiple supervisors that report to a mange, but no 1 supervisor will report to more that 1 manager. I am trying to create a formula that will be able to simplify this data, so that if a supervisor quits and another person gets hired, it will auto populate the change. An example below of what I mean

A B
1 Manager Name Supervisor Name

2 Fred Couple Mark Coghlan

3 Chad Porky Patrick O'Connor

4 Shakra Dupti Mitch McConnell

5 Shakra Dupti Mark Masters

6 Chad Porky Glenn Peach

So i Can simplify the list of managers in column A in lets say column D with the below array formula

{=IFERROR(INDEX($A$2:$A$50,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$50),0)),"")}

But how can I do something similar in column E for the supervisor so that in the end I can get a sheet that would look like this

D E
1 Manager Name Supervisor Name

2 Fred Couple Mark Coghlan

3 Chad Porky Patrick O'Connor

4 Glenn Peach

5 Shakra Dupti Mitch McConnell

6 Mark Masters

Thanks in advance

 

[Eric W]

Welcome to the Board.

Something like this?

Excel 2012

	A	B	C	D	E
Manager Name	Supervisor Name	Manager Name	Supervisor Name
Fred Couple	Mark Coghlan	Fred Couple	Mark Coghlan
Chad Porky	Patrick O'Connor	Chad Porky	Patrick O'Connor
Shakra Dupti	Mitch McConnell		Glenn Peach
Shakra Dupti	Mark Masters	Shakra Dupti	Mitch McConnell
Chad Porky	Glenn Peach		Mark Masters

<tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IF(ROWS($D$2:$D2)=SUM(IF($D$1:$D1="",0,COUNTIF($A$1:$A$50,$D$1:$D1))),IFERROR(INDEX($A$1:$A$50,SMALL(IF($A$1:$A$50<>"",IF(COUNTIF($D$1:$D1,$A$1:$A$50)=0,ROW($A$1:$A$50))),1)),""),"")}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$1:$B$50,SMALL(IF($A$1:$A$50=LOOKUP(2,1/($D$1:$D2<>""),$D$1:$D2),ROW($A$1:$A$50)),ROW(D2)-LOOKUP(2,1/($D$1:$D2<>""),ROW($D$1:$D2))+1)),"")}[/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]



[TABLE="width: 85%"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



This is also quite easy to do with a Pivot Table. Select your table (A1:B50), go to the Insert tab, click Pivot Table, click OK on the next form. Then from the Pivot Table Fields box on the right, drag both the Manager Name and the Supervisor name down to the Rows box.

 

[StewCrew]

Hi Eric,

Really appreciate the reply.

Hmmm that does not seem to be working. Unfortunately the model I am trying to build will not allow for pivot tables . I am not sure how to attach an excel workbook to this, but I have created a dropbox folder which has a snapshot of the excel raw data that I am trying to work through this problem with.

https://www.dropbox.com/s/kym6coi2yq1pg05/excel help.xlsx?dl=0

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Manager[/td][td=bgcolor:#70AD47]Supervisor[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Craig Leebosh[/td][td=bgcolor:#E2EFDA]Andrew Szelemej[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Craig Leebosh[/td][td]Brent Wilks[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Craig Leebosh[/td][td=bgcolor:#E2EFDA]Chad Sach[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Craig Leebosh[/td][td]Glenn Kowalski[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Craig Leebosh[/td][td=bgcolor:#E2EFDA]Jordan Stevenson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Craig Leebosh[/td][td]Joshua Cherry[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Craig Leebosh[/td][td=bgcolor:#E2EFDA]Sandy Johnston[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Craig Leebosh[/td][td]Shane Brydges[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Craig Leebosh[/td][td=bgcolor:#E2EFDA]Sheldon Cameron[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Akeem Brown[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Devon Pollard[/td][td=bgcolor:#E2EFDA]Andrew Walker[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Bryce Cale[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Devon Pollard[/td][td=bgcolor:#E2EFDA]Denis Ouellette[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Dylan Johnstone[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Devon Pollard[/td][td=bgcolor:#E2EFDA]Jay_Cr Derrington[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Norman Smyth[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Devon Pollard[/td][td=bgcolor:#E2EFDA]Rick Stephens[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Ryan Huson[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Devon Pollard[/td][td=bgcolor:#E2EFDA]Sean St. Amanad[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Devon Pollard[/td][td]Sonja Nordman[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dominic Marleau[/td][td=bgcolor:#E2EFDA]Frederic Foisy[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dominic Marleau[/td][td]Geoffrey Paquette[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dominic Marleau[/td][td=bgcolor:#E2EFDA]Jean-Francois Alarie[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dominic Marleau[/td][td]Josee Pelletier[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dominic Marleau[/td][td=bgcolor:#E2EFDA]Justin Schmid[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dominic Marleau[/td][td]Marie-Helene Fortier[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dominic Marleau[/td][td=bgcolor:#E2EFDA]Nathalie Perron[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dominic Marleau[/td][td]Nicholas Dubois[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Dominic Marleau[/td][td=bgcolor:#E2EFDA]Rae-Anne Davidson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dominic Marleau[/td][td]Yasmine Sayegh[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Ben Spyker[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Bill Keller[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Chris Butler[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Clayton Nakano[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Esteban Dominguez Londono[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Greg Foreman[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Luke Wasney[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Marc Edgett[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Patrick Melvin[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Steve Okrainetz[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Luke Wasney[/td][td=bgcolor:#E2EFDA]Troy Kaczynski[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Luke Wasney[/td][td]Wendy Lefebvre[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Melissa Moore[/td][td=bgcolor:#E2EFDA]Garret Elmes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Melissa Moore[/td][td]Heidi Harriss[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Melissa Moore[/td][td=bgcolor:#E2EFDA]Jason Marcone[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Melissa Moore[/td][td]Kylene Tyler[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Melissa Moore[/td][td=bgcolor:#E2EFDA]Melissa Moore[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Melissa Moore[/td][td]Michelle Andreacchio[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Melissa Moore[/td][td=bgcolor:#E2EFDA]Paul Walton[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Melissa Moore[/td][td]Rebecca Carrique[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Melissa Moore[/td][td=bgcolor:#E2EFDA]Robert Csabai[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Melissa Moore[/td][td]Timothy Ansdell[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roy Latham[/td][td=bgcolor:#E2EFDA]Alan Nolan[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roy Latham[/td][td]Anthony Lemoine-Hoefsmit[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roy Latham[/td][td=bgcolor:#E2EFDA]Dave Johnson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roy Latham[/td][td]Debbie Macdougall[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roy Latham[/td][td=bgcolor:#E2EFDA]John Gibson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roy Latham[/td][td]Jordan Lee[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roy Latham[/td][td=bgcolor:#E2EFDA]Justin Ryan[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roy Latham[/td][td]Luc Chenier[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Roy Latham[/td][td=bgcolor:#E2EFDA]Luc Jolivet[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Roy Latham[/td][td]Pascal Chartrand[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Shannon Doucette[/td][td=bgcolor:#E2EFDA]Anthony Sebastian[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shannon Doucette[/td][td]Brian Giles[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Shannon Doucette[/td][td=bgcolor:#E2EFDA]Glenn Peach[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shannon Doucette[/td][td]Krista Hamilton[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Shannon Doucette[/td][td=bgcolor:#E2EFDA]Mark Hughes[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shannon Doucette[/td][td]Melanie Asselin[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Shannon Doucette[/td][td=bgcolor:#E2EFDA]Mitch Getson[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Shannon Doucette[/td][td]Sarah Lightbody[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Shannon Doucette[/td][td=bgcolor:#E2EFDA]Saralyn Williams[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thomas Bruce[/td][td]Brian Devine[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thomas Bruce[/td][td=bgcolor:#E2EFDA]George Harrison[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thomas Bruce[/td][td]Gregory Waltham[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thomas Bruce[/td][td=bgcolor:#E2EFDA]Jason Leonov[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thomas Bruce[/td][td]John Devander Schueren[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thomas Bruce[/td][td=bgcolor:#E2EFDA]Kevin_Cr Ford[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Thomas Bruce[/td][td]Michael Ostien[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Thomas Bruce[/td][td=bgcolor:#E2EFDA]Teresa Ecclestone[/td][/tr]
[/table]

 

[Eric W]

It's possible that you can get formulas to create the lists you want. However, when you have lists of that size they formulas will take quite a while to calculate. You're better off with a macro solution, or Power Query, or even the built-in Sort tool (copy columns A:B, paste to D:E, select the sort tool > Custom sort > by columns D:E. You can even use Conditional Formatting to hide multiple instances of the same name in column D). Regardless of which method you want to calculate your list, please respond to Sandy's question about how you want the results to look.