search for missing matches

I looked through the forms a lot for an answer but couldn’t really find anything.

I have an array of information (I have attached a sample to help with the explanation) with a set total included in it. I then pull all of this information onto another worksheet using the index and match formulas that NBVC was so kind to help me out with.

My problem is that I need the total form the column on sheet 2 to tie out to the total in the array on sheet 1. I know that I am missing 12 entries of positive numbers I just can’t think of a way to figure out which numbers.

I was looking for a way to test which combinations of row 2 (X) and column A (Y) exist on sheet 1 as (x,y) but do not exist on sheet 2 in a combination pair (x,y) in column A and column B.

I did my best to explain it, I can try and make it clearer but I’m hoping that the example helps out. As a side note; the formulas didn’t all copy to the new sample book I created but there is an index and match formulas creating the numbers in the total column on sheet 2 as well as formulas generating the information in the array from other sheets.



Sample1.xlsx

I used the countif formula to determine there are 32 negative and 722 posative numbers in the array that give me my total 11,877... I then tried to bring all of the numbers that combine into that onto sheet 2.

I would expect H1415 to be the same number. It isn't and I was able to determine that there are 12 less poitive numbers in Sheet 2 column C than there are on the array on Sheet 1. I am just looking to see if there is a way, other than manually looking into it, to produce a list of missing x,y combinaitons (or possible just cell locations) that appear on Sheet 1 array but are not present on the Sheet 2 list since sheet 2 column A and B should be the same as Row 2 and Colum A from sheet 1 (They aren't or I wouldn't have this problem).

As of yet I dont have any working solutions so I can't post expected reults because I have never run into or attempted to solve a problem like this is excel.

I started working through them all. I added the ones I figured out using a rather lenghty workaround but a work around non-the-less.

It is important for me to determine that everything is included becuase I need the overall values to tie out. Basically I am not interested in the total unless it is the same as the total form sheet 1.
I hope this clarifies my question.


Sample1.xlsx

Yea, that would work great, jsut needed a way to identify discrepencies between both lists. How did you accomplish the highlighting I dont see any formulas

Wow, thanks for all your help!

I am having trouble recreating it within a workbook. I have attached a copy of the leadsheet, this holds the numbers I wanted ot check against, and a copy of month 1, eventually there will be 12.
The idea is to drop a system generated sheet each month onto the corresponding month page. The month pages feed the leadsheetes and hte graphs/charts. The lead sheet then totals whatever column heading I insert into C1. The idea was to have a quick eyesight check, I was hoping to get the condiotnal formatting to highlight all the numbers on Month1! to make sure all of the appropriate numbers were pulled over, in case any accounts were missing on the leadsheet.



highlight unused numbers problem.xlsx

I think part of the confusion is coming from the way I have it set up, that is my fault. I am making this to be put into use July 1, as such I used a singles months printout and pasted it into all 12 months worth of worksheets. This is producing the same data in each month of the lead sheet. In actuality all months should have zeros or differnt numbers on the leadsheet due to each month inherintly having different productivity levels.

I am trying to get the Month1! sheet to highlight all of the numbers that do not appear on the Leadsheet which should be all numbers not in the Coumn marked actual YTD as well as any numbers in Actual YTD that do not shot up on Leadsheet, either due to the account number missing of through my own faulty formula.

Does that make any more sense?

I renamed and inserted the fomulas like you posted but I didn't get anything that came up highlighted?and did you mean G15 or K15? Col K is hte last Col in the data set not G, thats the only reason I ask, wasn't sure if there was a formula reason for this or not?

Meant to attach it last post, sorry. highlight unused numbers problem.xlsx

O, I misunderstood part of your last post, I just renamed the actual cell, I didn't create the reference name, now it works perfectly, thanks