I'm looking more for a overall comparison that what I have seen in the threads. I need to compare data from 4 worksheets and then pull selected data from all sheets into a new sheet. If they do not match I need to be able to easily see the differences in the new sheet and also to have the ability to sort on fields in new sheet to filter.
For example, the columns I want to pull into a new sheet and compare are:
Sheet 1 (Project List): Column A: VPMI (same as E29:G) Column B: Investment ID (same as CA:E) Column C:Project (same as E29:H and CA:F)
Sheet 2 (E29): Column A: Name Column E: Manager Column G:Project VPMI Column H: Project Name Column J: Project Role Columns K-AC for monthly hours and overall total.
Sheet 3 (CA): Column A: Resource (same as E29:A) Column B: Resource Name (same as E29:E) Column C: Investment Role Column D: ESI Application Column E: Investment ID (same as Project List:B) Column F: Investment (same as E29:H and Project List:C) Columns I-Z: monthly hours
I then take E29:J and parse the data out. Example data in a field: AAAA SE/Dev - AppSpt - 31070
I create new columns as follows:
Role 2: =FIND(" ",J2,1)
Role 3: =FIND(" ",J2,AE2+2)
Role 4: =MID(J2,AE2+1,AF2-AE2-1) - this gives me SE/DEV
App 1: =FIND(" - ",J2,1)
App2: =FIND(" - ",J2,AI2+1)
App 3: =MID(J2,AI2+3,AJ2-AI2-3) - this gives me AppSpt
I need to take Role 4 and compare it to sheet 4 that has two columns and then return on new sheet above the two compared roles and if different to visually see that there are errors:
Column A: E29 Role and Column B: CA Role where column A has a row SE/DEV that matches a field in Sheet 2 (E29):J and column B: CA Role matches a field in Sheet 3 (CA):C - Investment Role. I need to make sure for ApptSpt in Sheet 2 (E29:B) after extracting the role 4 that it matches the column B that also matches Sheet 3 (CA: C). Does that makes sense?
For example, the columns I want to pull into a new sheet and compare are:
Sheet 1 (Project List): Column A: VPMI (same as E29:G) Column B: Investment ID (same as CA:E) Column C:Project (same as E29:H and CA:F)
Sheet 2 (E29): Column A: Name Column E: Manager Column G:Project VPMI Column H: Project Name Column J: Project Role Columns K-AC for monthly hours and overall total.
Sheet 3 (CA): Column A: Resource (same as E29:A) Column B: Resource Name (same as E29:E) Column C: Investment Role Column D: ESI Application Column E: Investment ID (same as Project List:B) Column F: Investment (same as E29:H and Project List:C) Columns I-Z: monthly hours
I then take E29:J and parse the data out. Example data in a field: AAAA SE/Dev - AppSpt - 31070
I create new columns as follows:
Role 2: =FIND(" ",J2,1)
Role 3: =FIND(" ",J2,AE2+2)
Role 4: =MID(J2,AE2+1,AF2-AE2-1) - this gives me SE/DEV
App 1: =FIND(" - ",J2,1)
App2: =FIND(" - ",J2,AI2+1)
App 3: =MID(J2,AI2+3,AJ2-AI2-3) - this gives me AppSpt
I need to take Role 4 and compare it to sheet 4 that has two columns and then return on new sheet above the two compared roles and if different to visually see that there are errors:
Column A: E29 Role and Column B: CA Role where column A has a row SE/DEV that matches a field in Sheet 2 (E29):J and column B: CA Role matches a field in Sheet 3 (CA):C - Investment Role. I need to make sure for ApptSpt in Sheet 2 (E29:B) after extracting the role 4 that it matches the column B that also matches Sheet 3 (CA: C). Does that makes sense?
