Pull multiple column data using one lookup value

[Jmschu10]

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.

 

[alansidman]

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.

 

[Jmschu10]

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.

.

 

[alansidman]

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.