Consolidating data from two sheets, and hiding specific rows

[kspab]

[FONT=&quot]I have two sheets for my engineers that have their active projects listed. I have a third sheet for all projects that have been completed. I'm looking for a way to pull all of my projects from sheet 1 and 2, and lump them together in sheet 3. Then, if I could have a column to specify if the project has been completed, it hides from sheet 1 & 2 and is shown on sheet 3 (Completed sheet).[/FONT]

 

[mumps]

In order to do what you want, each project would need a unique identifier such as a project number or project ID. Does each project in Sheet1 and Sheet2 have a column with a unique identifier?

 

[kspab]

In order to do what you want, each project would need a unique identifier such as a project number or project ID. Does each project in Sheet1 and Sheet2 have a column with a unique identifier?

No, I don't. I could include something. If this is my only option can you walk me through how to make this work? If I have IDs C001 and T001 (Engineers first initial & Project #).

 

[mumps]

It doesn't really matter what ID's you use as long as there are no duplicates. For example, you could use the last blank column in Sheets 1 and 2 that would hold the ID's. If you can do this, what column would that be in both sheets?

 

[kspab]

Both sheets are duplicates as far as column goes. Both have a Project, Notes, Date, Status (this will be my column for marking it complete and how I plan on filtering out completed projects), and now an ID column.

 

[kspab]

What's going to be the best method to generating unique IDs for each row now?

 

[mumps]

Are you looking for the macro to generate the unique ID's?

 

[kspab]

Here's what I've done. Created a function ="C"&ROW()-1 and ="T"&ROW()-1 for Sheet 1 and Sheet 2.. so each row has an ID of T1,T2,T3, etc on one sheet and C1, C2, C3, etc on another. Now how do I get these to output into the same sheet and effectively consolidate data from two sheets?

 

[mumps]

That should work. To clarify, now you want to combine the 2 sheets into one sheet named "Completed". All 3 sheets have Project, Notes, Date, Status, ID in row 1 of columns A to E. When you enter "Completed" in column D of the "Completed" sheet, you want the row that contains that project in either Sheet1 or Sheet2 to be hidden. Is this all correct? By the way, are all the project names in column A of Sheets 1 and 2 all unique (no duplicates across both sheets)? If they are, you wouldn't need an ID.

 

[kspab]

That should work. To clarify, now you want to combine the 2 sheets into one sheet named "Completed". All 3 sheets have Project, Notes, Date, Status, ID in row 1 of columns A to E. When you enter "Completed" in column D of the "Completed" sheet, you want the row that contains that project in either Sheet1 or Sheet2 to be hidden. Is this all correct?

This is correct.

By the way, are all the project names in column A of Sheets 1 and 2 all unique (no duplicates across both sheets)? If they are, you wouldn't need an ID.

Yes. Both use identical sheets as far as columns go, and neither engineers work on the same project, so each row has a unique (non-duplicate) project name.