Forum Discussion
Brirack77
Dec 07, 2022Copper Contributor
Vertical Data to Horizontal Data for mail merge
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 🙂
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))
- mathetesSilver Contributor
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))
- mathetesSilver Contributor
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