View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default comparing two spreadsheets using vlookup and compare

Try this revised approach which still uses a new Sheet3 to compare the source
Sheets 1 and 2 while catering for the different col placements in both source
sheets ..

A sample construct is available at:
Comparing 2 sheets with different col placements.xls
http://www.savefile.com/files/68553

In Sheet3:

Paste the uniques list of ids in A2 down. Use Data Filter Advanced
Filter, unique records only to generate this list. Do this separately in
another sheet. Just copy and paste the id ranges from both sheets 1 and 2
into a single col A, one below the other, then do the Data Filter
Advanced Filter, copy to another location: B1, check "Unique records only"
OK.

Paste the list of complete* col headers from say, Sheet1 in B1 across
*assuming both Sheets 1 and 2 have complete and identical** col headers,
except that the cols are placed differently in both sheets
**ie matching col headers/labels

Then place in B2:
=IF(OR($A2="",B$1=""),"",IF(OR(ISNA(MATCH($A2,Shee t1!$B:$B,0)),ISNA(MATCH($A2,Sheet2!$C:$C,0))),"",I F(INDEX(OFFSET(Sheet1!$A:$A,,MATCH(B$1,Sheet1!$1:$ 1,0)-1),MATCH($A2,Sheet1!$B:$B,0))=INDEX(OFFSET(Sheet2! $A:$A,,MATCH(B$1,Sheet2!$1:$1,0)-1),MATCH($A2,Sheet2!$C:$C,0)),"Y","N")))
Copy B2 across and fill down to populate the table

The above should return the required results. The revised formula reads the
col headers in both source sheets (which are not identically structured in
terms of col placements) to determine the correct cols to index & compare for
the ids listed in col A.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sweetetc" wrote:
Max

You have correctly interpreted the spreadsheet. I agree this will work. I
was hoping to create a 3rd spreadsheet of just results Y or N by ids without
moving the data to the 3rd spread sheet. Do you know of any way to do that?
--
Thanks
ETC