SOLVED

# Vertical Data to Horizontal Data for mail merge

Copper 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 :)

3 Replies
best response confirmed by Brirack77 (Copper Contributor)
Solution

# Re: Vertical Data to Horizontal Data for mail merge

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))

# Re: Vertical Data to Horizontal Data for mail merge

@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:

# Re: Vertical Data to Horizontal Data for mail merge

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

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 ...
1 best response

Accepted Solutions
best response confirmed by Brirack77 (Copper Contributor)
Solution

# Re: Vertical Data to Horizontal Data for mail merge

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))