PDA

View Full Version : Solved: Check Group Rows For Non Mathes



Marcster
02-10-2010, 12:05 PM
Hi People,

Been a while since I've written VBA and so in need of some help please... :help

In an Excel 2007 file I have over 150,000 rows of data.
Column A: ID
Column B: Desc
Column C: Ref1
Column D: Ref2

The data is sorted by Column A then B.

What I'm after is:
Check that all Ref1 and Ref2 are the same for each ID (Column A).
If they are not, write the ID to Column G.
The first ID row can be used to compare the Ref1 and Ref2.

Some ID's have only 1 row so this row can be ignored.
Some ID's have more than 1 row in the list.

If this can be done without VBA, please explain.
Tried a Pivot Table but with no luck.

I've attached an Excel file with sample data.
Sheet1 is before. I.e. before analysis.
Sheet2 is after, I.e. what it should look like after analysis.

Hope this makes sense.


Thanks, : pray2:

Bob Phillips
02-10-2010, 12:11 PM
Try this formula

=IF(SUMPRODUCT(--($A$2:$A2=$A2),--($C$2:$C2=$C2),--($D$2:$D2=$D2))=COUNTIF($A$2:$A2,$A2),"",$A2)

Marcster
02-10-2010, 12:49 PM
WOW, thanks XLD :beerchug:.

Works as I would of hoped.
Will try it tomorrow on the full dataset.

Thanks again.