I want to compare the "Doc Numbers" (this is my unique key) on 2 sheets. If they appear on the "Refreshed Master" sheet, but did not appear on the "Data" sheet we consider that these documents were added, and want to compile a list of them. Initially I was using INDEX/SMALL/MATCH as an array function. This was working great, until.... a request to exclude all Revisions (Rev) other than A.1. A.1 is actually defined as a truly NEW document, where any other Rev, is the revision of an existing document.
I am attaching a sample of my file. This data would appear on a new sheet. Thanks in advanced!
=IFERROR(INDEX(tbl_Refreshed[Number], SMALL(IF(COUNTIF(tbl_data[Doc ID], tbl_Refreshed[Number])=0, MATCH(ROW(tbl_Refreshed[Number]), ROW(tbl_Refreshed[Number])), ""), ROWS($A$1:H1))),"")
tbl_data = Table on Data sheet
tbl_Refreshed = Table on RefreshedMaster sheet
New Criteria = tbl_Refreshed[Rev] = A.1
Which version of Excel do you have?
Hello,
In my opinion you should go for a non formula solution, makes things far more easier.
In attached file I have added three sheets, Pivot, PQ and Filter.
I hope this gives you some help going forward.
Br,
Anders
@Anders Sehlstedt,
Sometimes the simplest of solutions are the best way to go. That was exactly what I needed. Thanks so much!
