Pull multiple column data using one lookup value

Jmschu10

New Member
Joined
Jun 25, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Please forgive the long description for 2 questions.

I recieve a spreadsheet from our HR of all filled and vacant positions. Each position is a row in the data and has 16 columns of information(name, title, Office). I need to create separate sheet for each division that will update automatically when I receive the new master data every month.

Each Division typically has at least three or more offices under them. For example Finance has Budget,Procurement, financial planning... The spreadsheet I receive from HR does not offer a label to group the offices to the correct division. My first question is determining how do I group the divisions together? Each office does have it's own separate code: budget(047 01), Procurement (018 01) .... I think this part should be pretty easy but I'm overthinking it.

Once I have that grouping label, I will need a separate sheet for each division and have Excel pull all rows/columns that correspond to new assigned code(Example all of Finance). I've been watching the vlookup and index match videos and think that's the way but also think I may need some type of query because again each position(row) has 16 columns of data.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Suggest you provide a sample worksheet (Dummy information only) that is representative of your actual file. Also, mock up what you want the final output to look like with completed information that aligns with your input. Use XL2BB to upload this data so that we can manipulate it. Having a sample file makes it much easier to provide you with a workable solution.
 
Upvote 0
Suggest you provide a sample worksheet (Dummy information only) that is representative of your actual file. Also, mock up what you want the final output to look like with completed information that aligns with your input. Use XL2BB to upload this data so that we can manipulate it. Having a sample file makes it much easier to provide you with a workable solution.


Thank you, please see the attached image of a very small sample of the data I receive. Unfortunately I am using a work computer and they require administrative rights to download anything to the computer. They currently have us working from home at the moment. If the image won't work, I appreciate your help anyway and will try another post at a later date once I can meet with my IT to limit my restrictions with the download of XL2BB.

The image, sheet 1 which is currently displayed in the JPG is what I receive from HR every month. the first column highlighted yellow(Org PH) is the information that I referenced above as being specific to each office and what I imagine I would use to have excel pull. The sections are each Division and I need excel to pull all of the rows for each section to the correct tab. For example I would need all of the rows that correspond to the blue highlighted sections(Dep Sec of Finance) to be pulled to the Deputy Sec of Finance tab. my goal is to simply paste the sheet 1 I receive from HR every month and have the tabs update with the positions as they move from filled to vacant

We plan to eventually use this as a tool to show total cost at full capacity and also be able to show potential for pay increases and setting a minimum wage floors etc

Not sure if that makes any more sense but thanks again for the reply.

Sample Spreadsheet 1.JPG
.
 

Attachments

  • Sample Spreadsheet 1[1].JPG
    Sample Spreadsheet 1[1].JPG
    170.5 KB · Views: 5
Upvote 0
You will need to have another Table that maps the Section to the Department so that the filtering can be to the correct worksheet. This mapping can be either the Name as shown in Blue or the code shown in yellow. But without a mapping (listing of what each section and the Department associated) there is no way to split and filter this informatio.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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