Vlookup or If Statement help.. I am LOST!!

[armour919]

So I am trying to do the following and I am open to ideas on how to solve the dilemma.

Here is what I currently have for data in Excel:

Column A has the project #, The options are '1', '2', '3', '4', '5', and '1 & 2'
Column B has the Task name
Column C has the Estimated work
D has the actual work
E has the remaining work
F has the start date
G has the finish date

Since this data feeds other sheets, I was hoping to break it out by project # and feed the appropriate project Sheet. To do this I was hoping to create five data tables in the data sheet that breaks out the data by project. So I would have a table for project 1, a table for project 2, Etc.. In these tables the remaining columns would need to be pulled also, so they all have the task name, work(est, actual, remaining) and dates. If the project is 1 & 2, then the work would be divided a 1/3 to project 1 and 2/3 to project 2 in the appropriate data tables while still pulling the remaining columns data.

Can anyone help me???

 

[RudiS]

To confirm; You want to split the list of records each to their own sheet based on the project number?
In other words; All project #1's to their own sheet, all project 2's to their own sheet, etc...All project 1&2 to their own sheet initially (but then take first third of this list and move to project 1 sheet and take bottom 2/3's and move to project 2 sheet)?

This sounds more like a job for a macro...

 

[armour919]

Rudi,

Not to their own sheet, I was going to have one sheet with all the data.. A table in which you dump the data and then use formulas to break it into different tables by projects.

 

[RudiS]

Then I'm mistaken... sorry.

Could you not just sort the data on its Project Number and copy/paste the data to new positions; even get a macro to do that!? There is only 5 groups?
I'm just still trying to understand the context of your request...

If the main table is columns A:G the can the table for Project 1 be dumped into Column I:O and project 2 table be dumped into Q:W etc.....
I'll send you a workbook with a trial macro to do this...you can see if it will work for you!

 

[RudiS]

See this sample workbook.....

Let me know if it will work for you...

 

[armour919]

I have a few questions Rudi, The data is going to change month to month and will need to update the initial data dump. I do not know that much about macros but was wondering if this would change the process. I am trying to make it as automated as possible in excel. Another question, I assume you can display the query any where in the sheet? And I understand you are still thinking about the split of the project 1&2 aspect...

I would like to say thank you for taking your time to help me on this matter. It is well appreciated.

Kyle

 

[RudiS]

Hi,

Firstly, the macro will respond to any size dump data, no matter how many rows or columns it has.

Second, the macro uses the Advanced Filter feature in Excel to filter (collect) the projects based on the number in column A. It will them dump the collected data with matching project numbers to the right of the list always separating the dumps with a single blank column. The macro is low maintenance and will adjust to the initial dimensions of the first original data block automatically. The only condition being that the project numbers of the original block must be in column A.

Thirdly, the conditions for the filter is on the Filter sheet. If there are more groups, they can be slotted in based on the filter structure. The macro will respond to these conditions as listed.

The macro is fast and needs less resources to work than using 100's of formulas (probably VLOOKUPS) to generate the lists as you first specified. I do however require you to test if in the manner you plan to use the separate project lists...you say they in turn will supply info to other lists in other sheets. If you think the macro can work and you have confirmed it with tests, then we can finish the code to also divide the 1&2 list to transfer the records the the project 1 and project 2 lists.

Let me know how the tests run and if this whole idea is viable.

Cheers