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
1	Manager Name	Supervisor Name		Manager Name	Supervisor Name
2	Fred Couple	Mark Coghlan		Fred Couple	Mark Coghlan
3	Chad Porky	Patrick O'Connor		Chad Porky	Patrick O'Connor
4	Shakra Dupti	Mitch McConnell			Glenn Peach
5	Shakra Dupti	Mark Masters		Shakra Dupti	Mitch McConnell
6	Chad Porky	Glenn Peach			Mark Masters
7

<tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula D2 {=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)),""),"")} E2 {=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)),"")} <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>

<tbody>
</tbody>

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?

Manager	Supervisor
Craig Leebosh	Andrew Szelemej
Craig Leebosh	Brent Wilks
Craig Leebosh	Chad Sach
Craig Leebosh	Glenn Kowalski
Craig Leebosh	Jordan Stevenson
Craig Leebosh	Joshua Cherry
Craig Leebosh	Sandy Johnston
Craig Leebosh	Shane Brydges
Craig Leebosh	Sheldon Cameron
Devon Pollard	Akeem Brown
Devon Pollard	Andrew Walker
Devon Pollard	Bryce Cale
Devon Pollard	Denis Ouellette
Devon Pollard	Dylan Johnstone
Devon Pollard	Jay_Cr Derrington
Devon Pollard	Norman Smyth
Devon Pollard	Rick Stephens
Devon Pollard	Ryan Huson
Devon Pollard	Sean St. Amanad
Devon Pollard	Sonja Nordman
Dominic Marleau	Frederic Foisy
Dominic Marleau	Geoffrey Paquette
Dominic Marleau	Jean-Francois Alarie
Dominic Marleau	Josee Pelletier
Dominic Marleau	Justin Schmid
Dominic Marleau	Marie-Helene Fortier
Dominic Marleau	Nathalie Perron
Dominic Marleau	Nicholas Dubois
Dominic Marleau	Rae-Anne Davidson
Dominic Marleau	Yasmine Sayegh
Luke Wasney	Ben Spyker
Luke Wasney	Bill Keller
Luke Wasney	Chris Butler
Luke Wasney	Clayton Nakano
Luke Wasney	Esteban Dominguez Londono
Luke Wasney	Greg Foreman
Luke Wasney	Luke Wasney
Luke Wasney	Marc Edgett
Luke Wasney	Patrick Melvin
Luke Wasney	Steve Okrainetz
Luke Wasney	Troy Kaczynski
Luke Wasney	Wendy Lefebvre
Melissa Moore	Garret Elmes
Melissa Moore	Heidi Harriss
Melissa Moore	Jason Marcone
Melissa Moore	Kylene Tyler
Melissa Moore	Melissa Moore
Melissa Moore	Michelle Andreacchio
Melissa Moore	Paul Walton
Melissa Moore	Rebecca Carrique
Melissa Moore	Robert Csabai
Melissa Moore	Timothy Ansdell
Roy Latham	Alan Nolan
Roy Latham	Anthony Lemoine-Hoefsmit
Roy Latham	Dave Johnson
Roy Latham	Debbie Macdougall
Roy Latham	John Gibson
Roy Latham	Jordan Lee
Roy Latham	Justin Ryan
Roy Latham	Luc Chenier
Roy Latham	Luc Jolivet
Roy Latham	Pascal Chartrand
Shannon Doucette	Anthony Sebastian
Shannon Doucette	Brian Giles
Shannon Doucette	Glenn Peach
Shannon Doucette	Krista Hamilton
Shannon Doucette	Mark Hughes
Shannon Doucette	Melanie Asselin
Shannon Doucette	Mitch Getson
Shannon Doucette	Sarah Lightbody
Shannon Doucette	Saralyn Williams
Thomas Bruce	Brian Devine
Thomas Bruce	George Harrison
Thomas Bruce	Gregory Waltham
Thomas Bruce	Jason Leonov
Thomas Bruce	John Devander Schueren
Thomas Bruce	Kevin_Cr Ford
Thomas Bruce	Michael Ostien
Thomas Bruce	Teresa Ecclestone

 

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