Vertical Data to Horizontal Data for mail merge

[Brirack77]

Hi,

I have a list of employee data in vertical rows with a unique employee number on each row. At the end of each row is the line manager (employee number and name.) I want to pull out a unique list of line manager name rows in a separate vertical list, then add columns of each of their employees, number & name, job title and start date etc - in order to build a mail merge document (that updates each time I change the raw data.)

RAW DATA

Emp No.	Full Name	Position name	Start Date	Line M Emp No.	Line M Name
1	AAA	XXX	1/1	11	YYY
2	BBB	XXX	2/1	12	UUU
3	CCC	XXX	3/1	11	YYY
4	DDD	XXX	4/1	12	UUU
5	EEE	XXX	5/1	11	YYY
6	FFF	XXX	6/1	12	UUU
7	GGG	XXX	7/1	13	ZZZ
8	HHH	XXX	8/1	13	ZZZ
9	III	XXX	9/1	11	YYY
10	JJJ	XXX	10/1	13	ZZZ

OUTPUT NEEDED

Line M Name	Emp No. 1	Emp Name	Emp No. 2		Emp No. 3 (etc.)
YYY	1	AAA	3	CCC	5
UUU	2	BBB	4	DDD	6
ZZZ	7	GGG	8	HHH	10

I can build the output quite manually with lots of IF statements - I wondered if there was a simpler way?

Thanks :)

[#2 · Accepted solution]

@Brirack77 

If you can work with just the employee names associated with each manager, then the attached shows a way to do that. I will defer to others with more expertise if there is a simple way to get both employee number AND employee name.

This formula gets unique managers: 

=SORT(UNIQUE(F2:F11))

And then this lists, horizontally, the employee names with each manager: =TRANSPOSE(FILTER($B$2:$B$11,$F$2:$F$11=J2))

[#3]

@mathetes thanks so much - this works really well - by pulling through the unique employee no. I can look up to the name and other info :thumbs_up:

[#4]

@Brirack77 

FILTER (and UNIQUE, and SORT) are all relatively new functions, and they're ones I've found to be very useful in a number of situations. I'm glad you were able to take my suggestion and improve on it for your  needs.

Here's a video you might find useful in thinking of further applications. (This video is where I first learned this set of functions a year or two ago.)

https://www.youtube.com/watch?v=9I9DtFOVPIg