multiple worksheets, find exceptions and merge into new worksheet

Hello,

Can you please help me with a macro that I am trying to create. I tried recording the macro for this but it was not working and I am relatively new to macros and VBA. Have been using excel but was able to record macros so far for what I wanted to do , until this time

We get these 2 reports in different worksheets from our reps and other people working on the project, the format of the reports remains the same but the data in them changes based on the project.

What I want to do is be able to pull in the reports into one workbook (budget and tasks) as worksheets and then find the exceptions between them. I have been successfully able to write a macro to “import” these worksheets into one workbook but then I need help in finding exceptions and creating three worksheets based on that data.

1. Budget: This worksheet has the budget information assigned to each code and the person that is responsible for the tasks.
2. Tasks: This is the worksheet that has the information about the tasks and schedule, assigned to a code and the person responsible for it.
3. Common: This worksheet will have the fields that are common to both tasks and budget, the data has “codes” and the primary key and the name as the “secondary key” to associate all the data with each other.
4. budget exceptions: this worksheet will have the rows that are present in the budget worksheet but NOT in the tasks.
5. task exceptions: Similarly this will have the rows/data that are present in the tasks worksheet but NOT in the budget sheet.

My thought process that I have been trying to implement is
1. Import the worksheets (I am already done and can do this with my macro)
2. Take the first code and name in the first row of budget worksheet and then scan through the tasks sheet and if they match then put them in the “common” worksheet or else put them in the “budget exception” worksheet.
3. If the data matches and its the first time then color code the row otherwise simply paste it!
4. If the data is present in the tasks sheet but not in the budget sheet then do something similar and paste it in the “tasks exception” sheet.

Can you guys please help me with this? I dont know how to figure this out on my own. Any pointers or help with be really really helpful.

I have also attached a sample sheet with budget and tasks (these are the ones that are imported, the data changes but the format remains the same, sometimes they only have 5-0 rows and sometimes in excess of 300 rows, depending on the project). and common, budget_exceptions and tasks_exceptions as the worksheets that I am trying to get.

Thanks for the forums guys, I came here last night while googling and have been hooked, there’s so much to learn here !!

Hi stojko89,

Thanks for your reply. And sorry about the confusing post and file.

On sheet "budget" you have this code:
COLORADO-AA-01-0300 Ruiz, Frank
This is also found on sheet "Tasks"

So if they match it's ok for them to be on sheet "common"
But if it's in "common" then it's can't be on "budget_exceptions"
But in your sample file it is...

1. Yes, so if the code and names match then only they should be in the common sheet. It's a mistake in my worksheet, I have fixed it and uploaded a new sheet.

Are located one under "budget" 3 times under "tasks" and 4 times under "common" <<---- why is that?

2. The code appears 4 times under common because the first line if from "budget" sheet and then the rest 3 lines are from "tasks" sheet because that code plus name occurs 3 times in the tasks sheet.

And also how to know wich Code and Name from sheets "budget" should the macro join from sheets "tasks" if there is 1 Code/Name under "budget" and 3 Code/Name under "tasks" ?

3. in the "budget" sheet the name and code only appears once for every person plus code, but they can have multiple tasks assigned to them for each code that's why they can appear multiple times under "tasks".

The "budget" sheet will only have a code + name appear only once but then that person + code can have multiple tasks assigned to them. What I am trying to achieve is create a "common" worksheet which takes the budget information for each code, take the person(s) that are assigned to that code and then list all the "tasks" that are assigned to each person under that code. And if there are exceptions then move it to a different sheet.

Essentially what I am trying to achieve is:

1. Lookup Code and Name in “budget” worksheet and compare it with Code and Name in “task”
2. If both of them match then copy the entire row from budget to “common” (assigning brown color to it), followed by all the rows from “tasks” that match (assigning blue color to it).
3. If the Code and Name are only present in “budget” and does not match with anything in “tasks” then copy the entire row to a worksheet “budget_exceptions”
4. If there are a Code and Name does not match with anything in tasks then copy the entire row from “tasks” to the worksheet “tasks_exception”

I hope this clears it up, if there are any other questions please let me know.

Thanks a lot for your help.

letsxcel,


I have responed today to the first part, of three, for your request on MrExcel:
' hiker95, 10/24/2012
http://www.mrexcel.com/forum/excel-q...worksheet.html

letsxcel,

Cross Posted here:
http://www.mrexcel.com/forum/excel-q...worksheet.html

We keep going round and round with what is the correct data set?????