How to extract info from one column based on another's data

Hi,

The way to do this is by using Data Filter. However first you will need to demerge all the column A cells and ensure that there is a person's name on each row.

Once you've done that you may find that the straightforward 'filter in place' is sufficient. This will simply filter all the jobs for any particular job that you select. If you want the filtered data on another sheet then just use Data Filter advanced and use a criteria cell to enter a job number and then filter the data to a separate worksheet.

However you may find that once you've unmerged all column A cells and added names on every row that a Pivot Table will be more useful, and easier to use. This will display job numbers and people in a 2D matrix.

Regards

Originally Posted by Bremus

I hear what your saying however based on what I have created not the best option. I have attached the file. The worksheet titled "Planner by Team Member" is where I enter the Jobs. The worksheet titled "Planner by Jobs" is where I want to bring over the names of the people. Thoughts?

Yes.

You are making the mistake that so many others make. You have designed a layout for final reporting in an attractive and well laid out way and are using this for data entry. However therein lies your problem.

If you want to do more than just capture data in one place and nothing more, i.e. go on to analyse, filter and summarise it (as you do), then the first priority is to ensure that you capture it in a database layout that Excel can work with. What you have at the moment is not that. You can't expect Excel to be able to filter job data held row by row if it doesn't know who's attached to each job - and by that I mean if it doesn't have an associated name on each job row.

Given what you have the starting point will be to associate every job with a name. Now you could write a macro to do that and in effect create a 2D table first, but it is really complicating the process.
As I say you should first capture data in a neat 2D table, then use that for extracting and producing nice neatly laid out and formatted reports.

I can't stress this too much. I've lost track of the number of times I've seen users, flushed with enthusiasm in their oh so perfect design masterpiece, make the same mistake and realise the hard way that they have boxed themselves in a corner.

Time spent planning these basic aspects is never wasted. And one of the golden rules is never merge cells. They are just too much trouble. Not only because of the limitations mentioned above, but also in several other aspects of Excel, particularly when you may want to paste data into a range that contains merged cells.

Hope this advice helps even if it's not what you wanted to hear.

Regards

Inserting a new column B on both sheets then using array formulae that give each row on the team member sheet the team members code and on the job sheet the job code makes it a bit easier.
See attached workbook.

Beau Nydal

Hi,

Whilst that will indeed address the strict layout provided if ever more than four people work on a job the layout won't accommodate it. It's an inflexible layout.

Filtering records is future proof and will also cut down the amount of white space where jobs have less than 4 people working on them. This of course still requires either the new column B you mention or the 2D database I mentioned earler - which would be a 3 column table containing a Date, Member number and Job number. Given that then the whole world of Pivot tables is opened which may well assist in providing other summaries / analysis.


Regards