Excel 2010 Compare Ranges on two sheets

[ITALIAORIANA]

Hi,

I have to compare ranges on two worksheets in the same workbook and having difficulty. I need to compare TMG 2015 columns a through H to Doug 2015 columns A through H. I tried using the Match function but have never used it before. It's not working the way I need it to.


=MATCH(A$1:H$2700,'Doug 2015'!A$1:H$2700,0)




Thanks
Deana

 

[SkipVought]

Hi,

The MATCH() function returns the offset for ONE VALUE in a ONE ROW or ONE COLUMN range.

So what's the value/range of values that you want to match against what range? You have defind a range of 8 colums and 2700 rows!

 

[ITALIAORIANA]

Oh I need to compare all of those columns. Doug 2015 has A through H with 2421 rows. TMG 2015 has A through H with 2613 rows. I need to compare all of those rows to see what doesn't match.

 

[SkipVought]

Does your table have headings? If not why not?

Please post a small sample of both tables. From what you describe, the MATCH function is not what you ought to use, it appears.

 

[ITALIAORIANA]

Neither sheet has headers but I can add them. I tried using an IF statement but that didn't work so I though MATCH would work.

 

[SkipVought]

Need to see what you're working with.

 

[ITALIAORIANA]

Here is a sample of the data I need to compare.
Doug 2015
2015, HMM00002, 6050, IL25, DL000001, EILH0927, 0927ID, MM01
2015, HMM00002, 6045, IL28, DL000001, EILH0927, 0927ID, MM01
2015, HMM00002, 6046, IL28, DL000001, EILH0927, 0927ID, MM01


TMG 2015
2015, HMM00001, 4035, IL01, CP000001, AIDIL, ICAIDD, MM05
2015, HMM00001, 4036, IL01, CP000001, AIDIL, ICAIDD, MM05
2015, HMM00001, 4037, IL01, CP000001, AIDIL, ICAIDD, MM05

 

[SkipVought]

So NONE of this data matches!

What abut a sample of data that does match.

 

[ITALIAORIANA]

Whoops sorry about that. Here is a sample of some of the data that matches.

Doug 2015
2015, HMM00001, 4038, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4039, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4040, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4035, IL02, CP000001, MCAIDIL, ICAIDD, MM05


TMG 2015
2015, HMM00001, 4038, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4039, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4040, IL01, CP000001, MCAIDIL, ICAIDD, MM05
2015, HMM00001, 4035, IL02, CP000001, MCAIDIL, ICAIDD, MM05

 

[SkipVought]

and your headings?

 

[SkipVought]

I don't understand how people use spreadsheet tables without headings.

faq68-5184

 

[ITALIAORIANA]

Year, Plan, Subgroup1, Subgroup2, Product1, Product2, Type, GroupID

 

[ITALIAORIANA]

I guess because this is data that a co-worker had and we needed to compare the data to what we have that needs to be in the database table. We send him what doesn't match. He uploads it into the database through a text file and I am not sure but I don't think he uses headers on it. None of his data ever has headers.

 

[SkipVought]

I used MS Query. Data > Get external Data > From other sources > From Microsoft Query... drill down to your workbook


This is the SQL code
[pre]
select a.*

from
['Doug 2015$'] a
, ['TMG 2015$'] b

where a.year =b.year
and a.plan =b.plan
and a.subgroup1=b.subgroup1
and a.subgroup2=b.subgroup2
and a.product1 =b.product1
and a.product2 =b.product2
and a.type =b.type
and a.groupid =b.groupid
[/pre]

and the results where they match
[pre]
Year Plan Subgroup1 Subgroup2 Product1 Product2 Type GroupID
2015 HMM00001 4038 IL01 CP000001 MCAIDIL ICAIDD MM05
2015 HMM00001 4039 IL01 CP000001 MCAIDIL ICAIDD MM05
2015 HMM00001 4040 IL01 CP000001 MCAIDIL ICAIDD MM05
2015 HMM00001 4035 IL02 CP000001 MCAIDIL ICAIDD MM05

[/pre]

 

[ITALIAORIANA]

Hey Skip,

Sorry for the delay, I was pulled away for another project. I was looking to pull out the data that didn't match, would I just change the = to a <>?

Thanks
Deana

 

[SkipVought]

Here's a good reference for table joins.

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins