Function to show if a new error record exists

**Supply_Analyst811** · 12-07-2010, 04:08 PM

At work, we have error reports as part of our data governance for SAP. The reports are run bi-weekly and put into an excel file, listing in rows the Material Number, Plant Number, Product Status, etc...

I want to write a function that will look at last weeks file and this weeks file to see when a new error has been documented for a material #. The problem is that the Material Number may have multiple errors on different rows because it has occurred at different plants.

I've tried something along the lines of (replaced cell numbers with column headers):

=IF(Vlookup(Material#,OldTable,OldTable.DisplayPlant,False) = Vlookup(Material#,NewTable,NewTable.DisplayPlant,False), "", "New Record")

This returns N/A and I've tried other types of similar equations and still no luck. Hopefully I have explained it in enough detail for someone to see what I am getting at.

**Cutter** · 12-07-2010, 04:36 PM

I am not getting what your file's setup looks like but an NA result means one of the VLOOKUPs could not find what it was looking for. You can highlight each VLOOKUP one at a time in the formula bar and hit F9 to see it's result. That will show you the cause of the NA.

I think you're going to have to send a sample to clarify what it is you are trying to accomplish.

**Supply_Analyst811** · 12-07-2010, 05:49 PM

Thank you for the reply. I figured my explanation was kind of unclear. I have attached two sheets as an example of what I was referring to. What I was hoping to find out is how can I write a function so that in the Second Sheet if a Error Record is new it will populate that column with the text "New Record" otherwise leaving the column blank if the Error Record was on the First Sheet as well as the second.

**ConneXionLost** · 12-07-2010, 05:57 PM

Something like:

Please Login or Register  to view this content.

autofilled down.

**Cutter** · 12-07-2010, 06:00 PM

Maybe:

In cell G7 of 2nd sheet

=IF(COUNTIF('[First Sheet.xls]D1'!$A:$A,A7)=0,"New Record","")

**Supply_Analyst811** · 12-08-2010, 09:09 AM

Thank you both. Each work in the cells they were intended. Now is there a way to combine both formulas? The sample file is small but some will have upwards of 1500 records that I will need to do this for.

**Cutter** · 12-08-2010, 10:51 AM

Sorry but I don't know what you mean by "combine both formulas".

You can put this formula in Cell G2 of Second Sheet and drag down as far as you need. Note that the formula now checks for a value in Col A and, if blank, returns a blank.

=IF(OR(A2="",COUNTIF('[First Sheet.xls]D1'!$A:$A,A2)>0,),"","New Record")

BTW - Are these actually separate workbooks as your sample suggest, or are they merely separate sheets within the same workbook? The provided formula is for separate workbooks so you'll have to adjust it for different scenario.

**Supply_Analyst811** · 12-08-2010, 03:37 PM

Originally Posted by Cutter

Sorry but I don't know what you mean by "combine both formulas".

You can put this formula in Cell G2 of Second Sheet and drag down as far as you need. Note that the formula now checks for a value in Col A and, if blank, returns a blank.

=IF(OR(A2="",COUNTIF('[First Sheet.xls]D1'!$A:$A,A2)>0,),"","New Record")

BTW - Are these actually separate workbooks as your sample suggest, or are they merely separate sheets within the same workbook? The provided formula is for separate workbooks so you'll have to adjust it for different scenario.

By combining I meant take what you suggested as a solution combined with what the other user on the forum suggested. These will be individual workbooks that I will have exported from a file on our company Intranet bi-weekly. Some files will be large so I will need your If statement as well as the other person's solution If statement so that if it displays "New Record" based on if it is a new material number or as you suggested for cell G7 if it is for a material number already listed but at a different plant.