Excel 2007 : Validating data across multiple data sources

**BarnesB** · 11-03-2011, 06:06 PM

I have been tasked with validating data across 4 workbooks. The problem I am facing is that the search criteria is not consistant between the Master list and the data sources. For example: My Master list of servers may have the server name listed as ABC(123). In the 3 other data sources, the same server may be listed as ABC, 123, or ABC(123). My goal is to be able to compile the lists into a single workbook, then on a summary page, present a Y/N result for each data source. I have attached an example workbook to try to better demonstrate what I am going for.

Thanks,

Brian

**BarnesB** · 11-07-2011, 07:34 PM

Originally Posted by BarnesB

I have been tasked with validating data across 4 workbooks. The problem I am facing is that the search criteria is not consistant between the Master list and the data sources. For example: My Master list of servers may have the server name listed as ABC(123). In the 3 other data sources, the same server may be listed as ABC, 123, or ABC(123). My goal is to be able to compile the lists into a single workbook, then on a summary page, present a Y/N result for each data source. I have attached an example workbook to try to better demonstrate what I am going for.

Thanks,

Brian

I found a work around for the issue. I created a seperate sheet and broke out the names.
I then created columns to look up the alternate names.

Then ran following:

=IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)=TRUE),IF(ISNA(VLOOKUP(B2,Sheet2!A:A,1,FALSE)=TRUE),IF(ISNA(VLOOKUP(C2,Sheet2!A:A,1,FALSE)=TRUE),"N","Y"),"Y"),"Y")

I have attached an example in case someone else runs across this issue.