Find value that doesn't match and bring back column heading

[Keelin]

I have data for 2 years across 2 tabs
5 columns of data to match

How do I match the values in each column for both years (across 2 tabs) and then bring back the title of the column with the non matching value?

Spreadsheet example attached

Please let me know if its not clear enough

Its going to be a large spreadsheet so an array formula would be best

In my actual data I am using: =IF(ISNA(INDEX('2013 ADL'!$AZ$2:$AZ$18365,MATCH(1,INDEX((E2='2013 ADL'!$A$2:$A$18365)*(O2='2013 ADL'!$J$2:$J$18365)*(I2='2013 ADL'!$D$2:$D$18365)*(K2='2013 ADL'!$F$2:$F$18365)*(M2='2013 ADL'!$H$2:$H$18365),0),0))),"No Match","Match")

This only tells me when everything matches or not

[newdoverman]

Here is one way of doing what you want. A range is setup to verify that the values match (I'm assuming that you are matching cell for cell like your example) Matches return TRUE mismatches return FALSE. Conditional formatting is then used on the data range.

I don't understand where you want the mismatches to appear other than in the listing. Anything else seems to be redundant.

[Keelin]

Hi Thankyou NewDoverMan,

And thank-you for raising some very good questions.

The check is not cell to cell exactly, it is cell to range. This makes it much more complicated and why I needed to use the formula I copied in above.

I have updated my spreadsheet with the specific answer I need

[newdoverman]

This version still uses a helper range to determine if a value is not in the same range in Year 1. This checks every value in Year 2 in the same range of Year 1. If there is ONE difference the column header for that difference will be returned.

This formula on worksheet Year 2 will return a 0 for all matches and an error for non matches:

Please Login or Register  to view this content.

To determine the column header, enter this Array formula in 'Year 2'!F2 and fill down: Helper range is L1:P6.

Please Login or Register  to view this content.

[Keelin]

Thanks again NewDoverMan, its a beautiful solution.

I now have a need for one final element for it to work in my real data. As the real data will unlikely include row to row matches, so searching for the year 2 (A2) value in row 2 of year 1 may not bring back the result, the actual row we need to compare data might be 22, for example. This is why my lengthy formula above was column orientated.

I believe the next step is to integrate a table or further range? Do you think you could help?

I did attempted using: IF(ISERROR(INDEX($A$1:$E$1,MATCH(A2,'Year 1'!$A2:$E6,0))),MATCH(A2,'Year 1'!$A2:$E6,0),0) even though I knew this wouldnt work.

Thanks so much for your help

[Keelin]

Thanks again NewDoverMan, its a beautiful solution.

I now have a need for one final element for it to work in my real data. As the real data will unlikely include row to row matches, so searching for the year 2 (A2) value in row 2 of year 1 may not bring back the result, the actual row we need to compare data might be 22, for example. This is why my lengthy formula above was column orientated.

I believe the next step is to integrate a table or further range? Do you think you could help?

I did attempted using: IF(ISERROR(INDEX($A$1:$E$1,MATCH(A2,'Year 1'!$A2:$E6,0))),MATCH(A2,'Year 1'!$A2:$E6,0),0) even though I knew this wouldnt work.

Thanks so much for your help

[newdoverman]

First of all, an alternate version of the formula in column F of Year 2

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Here is one possible way of locating mis-matches in Year 2 compared to Year 1 regardless of location.

[Keelin]

I am absolutely blown away by that Newdoverman!

I have never even seen the 'Ceiling' formula being used before, nor have I ever used 'Mod'. Do you happen to know of a throrough and comprehensive guide/book/set tips/training somewhere that would help me understand them better? I dont want to hound you for an explanation of what it is in fact actually doing!

I am ever so grateful, I simply would never have been able to tackle this.

Thanks,

[Keelin]

I am absolutely blown away by that Newdoverman!

I have never even seen the 'Ceiling' formula being used before, nor have I ever used 'Mod'. Do you happen to know of a throrough and comprehensive guide/book/set tips/training somewhere that would help me understand them better? I dont want to hound you for an explanation of what it is in fact actually doing!

I am ever so grateful, I simply would never have been able to tackle this.

Thanks,

[Keelin]

FYI the add reputation doesn't appear to be functioning at the moment (tried on two different browsers) will try again later.

[newdoverman]

Thank you for the feedback.

I don't have any books to recommend but I do recommend the YouTube videos of excelisfun and Debra Dalgleish. Both are excellent.

http://www.mvps.org/links.html#Excel will take you to the Microsoft Excel MVP website where the MVPs are listed with their websites which usually have many hints, tips and tutorials.

[Keelin]

Hi Newdoverman,

I am madly trying to get this done for the boss and need to modify the format of the helper columns to be across a row rather than down a column (the reason being that the actual dataset is too big for one super long column to work well)

Is there a way to make the values in year 1 and values in year 2 across in a row instead of vertically? The real dataset I am trying to wrangle now (one of them) the smallest has 750 rows and I have spent an hour trying to get the helper tables to work across a row rather than in one vertical column. I cant get it right! Would you mind helping me reformat it a bit?

I have attached the spreadsheet, the solution is exactly what I need. But just need to reformat it slightly.

Thanks ever so much

Keelin

[newdoverman]

Going along a row for the data extraction isn't a good idea. If you were worried about the length of the data, there are fewer columns by far than there are rows. I moved all the helper columns to a new worksheet. That should make formatting a report much easier.

[Keelin]

Apologies for my stressed out flustered email....you are (unsurprisingly!!) absolutely right.

In my late night terror I was attempting to apply the formula down the whole column getting a crash every time - hence my not so wonderful idea above......obviously having read your email (thank-you for putting me back on the right track - much appreciated!)...coupled with a good nights sleep I simply multiply the number of columns by the number of records in my original dataset and copy the formula down by that number. It reduces the excel load so it no longer crashes.

You reformatted it perfectly (I simply had to copy your sheet across so thank-you)

I really appreciate all of the help.

Keelin

[newdoverman]

Glad to help.

[Keelin]

Newdoverman

I just realised that its not allocating the changed value to the correct row......I was overly focussed on applying the solution to my dataset that I overlooked that the correct changed value is not being applied to the actual row. Its showing a list of what has changed throughout the dataset, but not per record. Is there a way to fix it quickly to bring it back per record?

So for each position code, I need the correct changed value against this record. Is this a quick fix or something you know how to do?

Sincere apologies for overlooking this...I was overly excited when I saw it and don't know how I missed this.

[newdoverman]

I have made a few adjustments to the formulae to make it easier to understand and to customize to your data needs. I don't know how large your data sets are so made some obvious changes that you can amend to suite your needs. Where you see a value repeated in a formula, if you change one change the rest to be the same.

The formulas have been adjusted to accommodate 10,000 rows of data on Year 1 and Year 2.

[Keelin]

Here is the file with the expected results,

So the column of results that have changed need to match the record row
So if:
Org1 Org2 Org3
Yr 1 = 9 A2 DW2
Yr 2 = 9 A2 AQ1 Org 3

The columns being Org 1 - 3 respectively

Then against the year 2 row another column would say ORG 3 has changed

[newdoverman]

The solution to the locations of the mis-matches was designed to not have spaces in the data. Therefore the values will not line up. In addition, I had just copied existing data and pasted it in both years so there are repeated mis-matches. The chart as it exists does give the correct location of the mis-matches but not on the same row that they occur. I will have to get back to you on this. It is something that I will have to do tomorrow as it is getting too late here.

[newdoverman]

This is an attempt to get the correct Heading next to the array. If there are duplicated mis-matches, I'm not sure what the result will be other than the first of the mis-matches will be chosen. I stripped out the duplicates that I had inserted. Only real data will prove good or bad.

[newdoverman]

One thing that I should have asked and didn't is; is it possible to have more than one item mis-matched in a row? If that is possible then there has to be more than 1 indication of a mis-match which will require 3 columns to indicate that situation.