Forum Discussion

Brirack77's avatar
Brirack77
Copper Contributor
Dec 07, 2022

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

  • 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's avatar
    mathetes
    Silver Contributor

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

     

     

     

    • Brirack77's avatar
      Brirack77
      Copper Contributor

      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 👍

      • mathetes's avatar
        mathetes
        Silver Contributor

        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

         

Resources