SOLVED

Vertical Data to Horizontal Data for mail merge

Occasional Contributor

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 NamePosition nameStart DateLine M Emp No.Line M Name
1AAAXXX1/111YYY
2BBBXXX2/112UUU
3CCCXXX3/111YYY
4DDDXXX4/112UUU
5EEEXXX5/111YYY
6FFFXXX6/112UUU
7GGGXXX7/113ZZZ
8HHHXXX8/113ZZZ
9IIIXXX9/111YYY
10JJJXXX10/113ZZZ

 

OUTPUT NEEDED

Line M NameEmp No. 1Emp NameEmp No. 2 Emp No. 3 (etc.)
YYY1AAA3CCC5
UUU2BBB4DDD6
ZZZ7GGG8HHH10

 

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

Thanks

3 Replies
best response confirmed by Brirack77 (Occasional Contributor)
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))

 

mathetes_0-1670418938276.png

 

 

@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

@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

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...