Large Worksheet that is subtotaled, verify that certain columns have the same info in each group.

[Kelley Dorning]

I have a worksheet with 28 columns and 8700 rows of data representing unique accounts. The information is sorted by group, each group has one or more groups associated with it and each of those groups has more than one unique account associated with it. Each group is identified by a key (9 digits long). The workbook is subtotaled by the largest group. Within the large group, I need to verify that two of the other columns contain only one value. If they don't I need to see a result that will flag me to research it.

So Group "Primary" in Column F with Key 600019432 has 11 members. Within that group, I need to verify that all the entries in columns D and H are the all same. The result can be a number showing how many values are there or a word "OK" or "Does not match".

I am attaching a sample of the spreadsheet with just the relevant fields. In this case, Column D should return 1 or OK, because there is only one value that matches in all the cells, and Column H should return 2 or Does not match because there are two values in the cells.

This will save me a lot of stress if someone can help, I would be very grateful!

 

[Somendra Misra]

Hi Kelley,

Try for column D:
=IF(SUMPRODUCT(--(COUNTIF($D$2:$D$12,$D$2:$D$12)=COUNTA($F$2:$F$12)))=COUNTA($F$2:$F$12),"OK","Not OK")

For column H:
=IF(SUMPRODUCT(--(COUNTIF($H$2:$H$12,$H$2:$H$12)=COUNTA($F$2:$F$12)))=COUNTA($F$2:$F$12),"OK","Not OK")

Regards,

 

[Kelley Dorning]

That totally worked for this one group, but this is just the sample. I have 8700 rows of data that represent 6597 groups. So I would need a formula that I can paste into the blank cells, that takes the subtotaled value (F13 in the sample) showing how many rows above belong to the group and compare that many rows. Ugh... I really appreciate your help! Do you have any thoughts on a modified way to handle the bigger group?

 

[Somendra Misra]

Can you upload a sample file may be with 3 groups of varying no. of rows in each group.

Regards,

 

[Kelley Dorning]

Here is a larger sample with examples of the issues across the two columns. Thank you so much for taking the time to help me!!

 

[Somendra Misra]

Try in P4 and than copy in each blank cell: and also in AB4.

=IF(SUMPRODUCT(--(COUNTIF(OFFSET(P3,,,-$W4),OFFSET(P3,,,-$W4))=$W4))=$W4,"OK","Not OK")

Regards,

 

[Kelley Dorning]

I had to unhide my columns and now it works!!! AMAZING!! Thank you so much!!