Compare data in four sheets and create new sheet.

blueskidd

New Member
Joined
Aug 28, 2014
Messages
5
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?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,196,248
Messages
6,014,226
Members
441,808
Latest member
xplainer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top