Formula to pull only the differences between sheets

Hi! Attached is a sample of a workbook I am working with. I have used Index formula's to pull fields but this example has me stumped. I have 5 sheets in this workbook. The first, Sheet A "OCC-GEO" have what the OCC and GEO codes should be for employees in E and F columns. The next two sheets, sheets B and C, are what codes each employee currently has in separate sheets for each field, both in column G. The next two sheets, sheets D and E, I am trying to pull only where there are changes between the new codes on A and the existing codes in B and C sheets. Any ideas how this can be done?

Thank you! Sheet A is the after, Sheets B and C are the before and Sheets D and E would be only the differences. Does that help?

Sure, for Sheets D and E and put what the expected results should be. Sheet D has two employees with two OCC codes because those are the only two OCC codes that changed between sheet A and sheet B. Sheet E has two employees with two GEO codes because those are the only two GEO codes that changed between sheet A and sheet C. Does that help? Coffee is life!

ahh, good to know! Thank you for letting me know. I tried to remove the other post but there isn't an option so I will message the administrator to remove.

Yes, the ID's are unique in column A

Got it. My mistake!

I'm assuming with PropPlus you have the Dynamic Array formulas available to you. Your sample data shows that the order of the sheets are exactly the same (Employee Number Ascending). If this is always the case, then try this on Sheet D:

=FILTER(FILTER('(B) ExistingOCC'!A2:G9,'(A) OCC-GEO'!A2:A9&'(A) OCC-GEO'!E2:E9<>'(B) ExistingOCC'!A2:A9&'(B) ExistingOCC'!G2:G9),{1,1,1,0,0,0,1})

and in Sheet E:

=FILTER(FILTER('(C) ExistingGEO'!A2:G9,'(A) OCC-GEO'!A2:A9&'(A) OCC-GEO'!F2:F9<>'(C) ExistingGEO'!A2:A9&'(C) ExistingGEO'!G2:G9),{1,1,1,0,0,0,1})

See attached.

I do have Dynamic Arrays in my Excel although I am just now learning about them. This looks awesome, thank you! In theory if unique rows are added that meet the filter criteria they will appear in sheets D and E automatically?

Yes, as long as you are referencing the cells. I would suggest putting the sheets in an Excel table. An example of that is attached.

This is amazing and exactly what I was looking for. Thank you so much!

Quick question. If I want to add a column in sheets D and E to pull an additional field how do I do that? When I insert, the header shifts and not the results of the two examples.

Well, I don't know exactly what you are trying to do, but that's what the 1's and 0's are for at the end of the formula - a 1 means you want to display the column and a 0 means you don't - based on the columns being filtered. (in the formula above, I reference columns A-G (7 columns) so there are 7 0's and 1's at the end of the formula.

Ahhh, okay. Perfect Gregb11. Thank you again!

You're welcome. And thanks for the rep.

I guess what I was trying to do is see if I can expand the filter formula area if I add a new column and pull another field. It has a bounding box and insert column does not expand it. How is that done? Is it also possible to format sheets D and E as a table so it is in import ready format without losing the formula that pulls the information into the sheet to begin with?

If I understand you correctly, yes, you can expand the filter area if you add new columns and want to show them on the other sheets.

It has a bounding box and insert column does not expand it.

I don't know what you mean here.

You cannot use dynamic array formulas and Excel Tables at the same time.

so it is in import ready format without losing the formula that pulls the information into the sheet to begin with?

I'm not sure what you mean here either.

So how do you expand the table? Once I insert a new column between columns A and B the 1's and 0's refer to the same cells.

If you expand the table, you'll need to adjust your 0's and 1's. If you can attach the file I can look at it.

I would love to know what I did wrong. I now have 8 columns and there are 7 0's/1's. I tried highlighting the table and renaming Table 1.1 however that didn't work either. See attached.

It's filtering Table2, not Table1 and Table2 has 7 columns. If you want it to show from table1, then use this:

=FILTER(FILTER(Table1,Table1[Employee Number]&Table1[OCC]<>Table2[Employee Number]&Table2[OCC]),{1,1,1,1,0,1,0,1})
(and change the 0's and 1's appropriately.)

That's perfect, thank you! Next issue appears to be with Table1, Table2, and Table3. They don't auto-expand when I add a couple hundred rows of data. I went to the Name Manager and it will not allow me to change the cell references. I tried creating new tables that reference the entire columns but it doesn't like the formula and tells me there was a problem with it. Thoughts? Sorry!

Actually, I stand corrected. The table expanded but because I am now getting value N/A errors on the D and E sheets I presumed it was due to the tables. I am simply copying and pasting as values from reports I am running with the same columns of information and ensuring the numbers are formatted correctly. Oddly, when I paste thousands of rows of data I get N/A's but when I paste one row of data for the same employee the formula picks it up. Any ideas? I'll see if I can replicate the issue with a smaller dataset and disguise the info.

It looks like it is only an issue when I paste more rows than the current table has and I get a message that states "This Table inserted rows into your worksheet. This may cause data in cells below the table to shift down." Once I click okay it expands the table and seems to be okay until I look at the sheets with the Filter formulas and it just has #N/A's. I also just tried inserting blank rows in the middle of the table, it expands, but then the Filter formulas become #N/A's even though the data hasn't changed above and below the empty rows I added.

Can you attach the file?

Here it is with only one empty row inserted on sheet A row 4 and then I input sample data. It skewed the output on D and E sheets.

The tables have to be the same size. The assumption was to find those that have changed, assuming that the same records are in each table, just the value has changed. It also assumes they are in the same order. If these are incorrect assumptions, then this solution won't work.

Okay, so if I understand you correctly, if I paste new data in A B and C and the Tables differ in size, as long as I add additional blank rows so all the tables are the same length AND they are in ascending order by number it will work?

Yes, it should.

I'm confused. I have my dataset now in A, B, and C but you are saying if John Doe is not on all three sheets on the same rows this will not work? I thought the formula references the employee number and only displays updates if there Data from A does not equal data from B and C? Attached is a sample. Any new hires will throw off the dataset and consider everything in need of an update.

The whole premise of this solution was based on the assumption that both lists would contain the same list of employee ID's in the same order and it was to determine if the OCC (or GEO) numbers were different. Is that not the case? If you are going to have the lists in different order or have some employees on one sheet but not the other, then a different solution will be required.

That would not be the case. Sorry, I have had different employees on my lists and when we were discussing them being in the same order I thought you were referring to the sort order of the Employee number.

OK, with this formula, it doesn't matter if the number of employees match on each table (either could be larger than the other). It also doesn't matter what order they are in. It compares the Employees in Table1 to the employees in Table 2 and if the same employee ID in Table1 has a different OCC in Table2, it shows the info from table. (this would go in cell A2 of Sheet (D))

=IFERROR(FILTER(FILTER(Table1,ISNA(MATCH(Table1[Employee Number]&Table1[OCC],Table2[Employee Number]&Table2[OCC],0)),"None"),{1,1,1,1,0,1,0,0}),"All Match")