Filter data by the department number of each employee

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
We are ordering equipment for our employees and I want the raw data of everything I have filtered by department. I could copy and paste each one to go with each department but we have too many employees throughout all three shifts and it would take forever to do it manually. I was thinking a pivot table would help but I wasn't able to get it to do what I wanted. I would like everything filtered out by department so I can send all three departments a list of the employees with the equipment they need to order. The picture I've attached is a simplified version of what I am currently working with. Thanks for the help!
 

Attachments

  • Equipment.PNG
    Equipment.PNG
    16.7 KB · Views: 13

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
In a pivot table you can place BOSS, EMPLOYEE and DEPARTMENT in the FILTER option.
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
In a pivot table you can place BOSS, EMPLOYEE and DEPARTMENT in the FILTER option.
In the pivot table field list, the only fields I currently see are Boss and Jon. Do you know how I could get boss, employee and department to appear in the field list or how I can make the two fields available do what I need them to do?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Do you have a table with the fields BOSS, EMPLOYEE, DEPARTMENT, EQUIPMENT etc.
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23

ADVERTISEMENT

I don't have a table with those fields.
Do you have a table with the fields BOSS, EMPLOYEE, DEPARTMENT, EQUIPMENT etc.

I've attached an image so you can see what I see. If you tried the same thing, are you seeing more fields than the two I see?
 

Attachments

  • pivot table.PNG
    pivot table.PNG
    10.1 KB · Views: 6

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
I assume you want to register all data. That's why you need a table with all the information. When that table is ready you can make overviews with a pivot table.
An example:
DepartmentBossEmployeeEquipmentSizeQuantityOrdered
12JonCJA
12JonCJB
12JonCJC
12JonCJD
12JonCJE
12JonCJF
12JonAlexA
12JonAlexB
12JonAlexC
12JonAlexD
12JonAlexE
12JonAlexF
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23

ADVERTISEMENT

I assume you want to register all data. That's why you need a table with all the information. When that table is ready you can make overviews with a pivot table.
An example:
DepartmentBossEmployeeEquipmentSizeQuantityOrdered
12JonCJA
12JonCJB
12JonCJC
12JonCJD
12JonCJE
12JonCJF
12JonAlexA
12JonAlexB
12JonAlexC
12JonAlexD
12JonAlexE
12JonAlexF
That is ideally how I wish I received the data, but the initial image I uploaded is the only way to receive it. Based on what I have, do you believe it'd be possible to do what I am trying to do with an index formula or vlookup.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
I might have an idea, but with VBA not formula.
so I can send all three departments a list of the employees with the equipment they need to order
Do you only have 3 department? In your example they are department 12,34, & 76?
By using your example, what the result should look like?
 

Gummyworms1234

New Member
Joined
Jul 18, 2019
Messages
23
I might have an idea, but with VBA not formula.

Do you only have 3 department? In your example they are department 12,34, & 76?
By using your example, what the result should look like?
I only have 3 departments and I'm open to any suggestion. Based off of my example, I would create 3 tabs for each department and I'd like each table to go the corresponding tab if possible with or without VBA or a formula.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.
Or at least post your samples as table instead of image, using XL2BB is preferred.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

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
Top