Return Numeric Labels that have different Numeric Values

[Sam via OfficeKB.com]

Hi All,

I would like to compare two sets of Numeric Values: if their values differ
have their corresponding Numeric Label returned across a single row.

I have 4 columns of data spanning 60 rows. The columns of data work in pairs:

Column A Numeric Label "A17:A76"
Column B Numeric Values "B17:B76"

Column D Numeric Label "D1776"
Column E Numeric Values "E17:E76"

If a column "B" Numeric Label's value is (NOT equal) <> to its corresponding
Numeric Label's value in column "E", then return the Numeric Label of that
Numeric Value.

The Numeric Values in columns "B" and "E" are in descending order. All
Numeric Labels should be returned across a single row.

Sample Data Layout:
Col "A" Col "B" Col "D" Col "E"
Labels Values Labels Values
10 8 15 6
20 4 8 4
15 3 11 3
11 2 4 2

Required Results:
Labels 11 and 15 from column "A" should be returned across a single row as
their numeric values differ - column "B" Value is NOT equal to their
corresponding Value in column "E".

Thanks
Sam

 

[T. Valko]

Try this (array entered: CTRL,SHIFT,ENTER):

=INDEX($A$17:$A$76,SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<>SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),ROW(A$17:A$76)-ROW(A$17)+1)),COLUMNS($A:A)))

Copy across until you get #NUM! errors. If you want an error trap, maybe use
conditional formatting to hide them.

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much. Great Formula!

Is it possible to have the Numeric Labels that are in columns "A" and "D"
returned in ascending order across the row?

Cheers,
Sam

 

[T. Valko]

That actually makes it easier (still array entered):

=SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<>SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),COLUMNS($A:A))

Biff

 

[T. Valko]

You can clean that up and shorten it considerably (to where an error trap
could be added) if you use defined names:

Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76)
Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)
Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)
Labels: =$A$17:$A$76

Then:

=SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))

With an error trap:

=IF(ISERROR(SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))),"",SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A)))

Biff

That actually makes it easier (still array entered):

=SMALL(IF(COUNTIF($D$17:$D$76,$A$17:$A$76),IF(SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)<>SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)),COLUMNS($A:A))

Biff

 

[T. Valko]

Ooops!

Made a mistake in copying/pasting:

Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)

Should be:

Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76)

Biff

You can clean that up and shorten it considerably (to where an error trap
could be added) if you use defined names:

Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76)
Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)
Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)
Labels: =$A$17:$A$76

Then:

=SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))

With an error trap:

=IF(ISERROR(SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))),"",SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A)))

Biff

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much for your assistance. Formula works Great!

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Abbreviated, error trapped version much appreciated. Brilliant!

Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76)
Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)
Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)
Labels: =$A$17:$A$76


With an error trap:

=IF(ISERROR(SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))),"",SMALL(IF
(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A)))

Cheers,
Sam

Ooops!

Made a mistake in copying/pasting:
Should be:

Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76)

Biff

You can clean that up and shorten it considerably (to where an error trap
could be added) if you use defined names:

[quoted text clipped - 40 lines]

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Abbreviated, error trapped version much appreciated. Brilliant!

Counts: =COUNTIF($D$17:$D$76,$A$17:$A$76)
Sum1: =SUMIF($A$17:$A$76,$A$17:$A$76,$B$17:$B$76)
Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76),$A$17:$A$76)
Labels: =$A$17:$A$76


With an error trap:

=IF(ISERROR(SMALL(IF(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A))),"",SMALL(IF
(Counts,IF(Sum1<>Sum2,Labels)),COLUMNS($A:A)))

Cheers,
Sam

Ooops!

Made a mistake in copying/pasting:
Should be:

Sum2: =SUMIF($D$17:$D$76,$A$17:$A$76,$E$17:$E$76)

Biff

You can clean that up and shorten it considerably (to where an error
trap
could be added) if you use defined names:

[quoted text clipped - 40 lines]

Biff