Creating a pivot table for employee data and am getting stuck

futureproof1

New Member
Joined
Aug 21, 2023
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
Hi all. I am working with a spreadsheet with approximately 4500 rows of employee data, with each row representing a unique employee. Within the spreadsheet are various columns showing the following: a) job title; b) tenure in current role; c) region currently located; d) previous employer; e) previous title; f) tenure in previous role. Columns d through f repeat five times, i.e. you could see up to five previous roles, job titles, and dates of employment for each employee.

I have created a pivot table to analyze this data. My goal is to be able to see a list, by previous employer, of each of the employees at my/our current company. For example, a list of all employees who previously worked at Gizmo Corp, whether it was their immediately previous role to their current one, or whether it was three jobs ago. My challenge is it seems I need to do this "role by role," i.e. looking at the list of prior experiences one by one, and then manually amalgamating each of these disparate lists together. This is proving to be time consuming, as you might imagine. I currently have each prior role as a filter. Is there a way to combine these filters into one "mega filter" to see ALL experience over their past 5 jobs at a particular employer such as Gizmo Corp? And if so, how can I do this so I can still see unique job titles, dates of employment, etc for each INDIVIDUAL role (i.e. in case an employee worked a couple different jobs at Gizmo Corp over two different time periods)?

Happy to provide additional info if it helps. Any help here would be much appreciated!

Thanks :)
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
can you post some mocked up sample data using the xl2bb add in.
But, off the top of my head you seem to have a cross tab report of data and not a flat data file (columns d thru f repeating- im guessing this means there are 20 more columns of this data).
The Power Query tool can help you organize the data into a flat file, but you would need to create some kind of "previous job indicator" column. There is a forum for POWER TOOLS in Mr. Excel that probably has lots of useful information on how to accomplish that.

This is what I think you need to get accomplished to do your reporting. If I'm mistaken, please provide some mocked up data and a sample of what you would expect to be a result:


Mr excel questions 55.xlsm
ABCDEFGHIJ
1
2Job TitleCurrent TenureCurrent RegionPrev 1 EmplPrev 1 TitlePrev 1ious TenurePrev 2 EmplPrev 2 TitlePrev 2ious Tenure
3Mgr5WestABCMgr3DEFCSR2
4
5
6Job TitleCurrent TenureCurrent RegionPrevious JobPrev EmplPrev TitlePrev Tenure
7Mgr5WestPJ1ABCMgr3
8Mgr5WestPJ2DEVCSR2
futureproof1
 
Upvote 0
Hi, thanks for your response. As requested, here is a mock-up sample of the data:

EmployeeData_Sample.xlsx
ABCDEFGHIJKL
1first_namelast_namelocationjob_titleexperience_2 date_rangeexperience_2 companyexperience_2 titleexperience_2 locationexperience_3 date_rangeexperience_3 companyexperience_3 titleexperience_3 location
2RobSmithNew York, NYAccount ManagerJune 2021 - April 2023Gizmo CorpSolutions ConsultantNew York, NYSeptember 2018 - June 2021Widget CoAccount ManagerNew York, NY
3JenniferWhiteLondon, UKSolutions ConsultantAugust 2020 - October 2022Acme IncAccount ManagerLondon, UKMay 2017 - August 2020Gadget CorpSolutions ConsultantLondon, UK
4BethPaulsonTokyo, JapanAccount ManagerJanuary 2021 - February 2023Widget CoSolutions ConsultantTokyo, JapanNovember 2019 - January 2021Thingy CoAccount ManagerTokyo, Japan
5JackNeumanLos Angeles, CASolutions ConsultantNovember 2021 - May 2023Gadget CorpAccount ManagerLos Angeles, CAJune 2017 - November 2021Gizmo CorpSolutions ConsultantLos Angeles, CA
6EmilyKentNew York, NYAccount ManagerDecember 2020 - December 2022Thingy CoSolutions ConsultantNew York, NYAugust 2018 - December 2020Acme IncAccount ManagerNew York, NY
7BrianCollierChicago, ILSolutions ConsultantJuly 2019 - May 2022Gizmo CorpAccount ManagerChicago, ILJanuary 2017 - July 2019Widget CoSolutions ConsultantChicago, IL
8CodyDanversParis, FranceAccount ManagerJune 2021 - July 2023Acme IncSolutions ConsultantParis, FranceMarch 2018 - June 2021Gadget CorpAccount ManagerParis, France
9LouisBrownMiami, FLSolutions ConsultantMarch 2022 - April 2023Widget CoAccount ManagerMiami, FLApril 2018 - March 2022Thingy CoSolutions ConsultantMiami, FL
10ArnoldO'DonnellPhiladelphia, PAAccount ManagerSeptember 2020 - August 2022Gadget CorpSolutions ConsultantPhiladelphia, PANovember 2017 - September 2020Gizmo CorpAccount ManagerPhiladelphia, PA
11PatriciaJacksonMiami, FLSolutions ConsultantJuly 2020 - March 2023Thingy CoAccount ManagerMiami, FLFebruary 2018 - July 2020Acme IncSolutions ConsultantMiami, FL
12AmandaStarkLos Angeles, CAAccount ManagerJanuary 2020 - July 2022Gizmo CorpSolutions ConsultantLos Angeles, CAApril 2017 - January 2020Widget CoAccount ManagerLos Angeles, CA
13BrentCollinsLondon, UKSolutions ConsultantNovember 2019 - September 2022Acme IncAccount ManagerLondon, UKAugust 2017 - November 2019Gadget CorpSolutions ConsultantLondon, UK
14LucasHackettChicago, ILAccount ManagerMarch 2021 - February 2023Widget CoSolutions ConsultantChicago, ILDecember 2019 - March 2021Thingy CoAccount ManagerChicago, IL
15TomWilkinsonSeattle, WASolutions ConsultantFebruary 2020 - August 2022Gadget CorpAccount ManagerSeattle, WAMay 2018 - February 2020Gizmo CorpSolutions ConsultantSeattle, WA
16MikeJacobsNew York, NYAccount ManagerDecember 2019 - June 2023Thingy CoSolutions ConsultantNew York, NYAugust 2017 - December 2019Acme IncAccount ManagerNew York, NY
Sheet1
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top