Unique Distinct List comparing the sum from two columns

[pto160]

I have two lists. I want to get a unique distinct list by comparing the sums of the unique values in list 1 that don't match the sums of the unique values in list 2.
Is that possible?

Sheet1

	B	C	D
2		List 1
3	Cat	10
4	Cat	10
5	Dog	20
6	Dog	10
7	Dog	10
8	House	5
9
10		List 2
11	Cat	10
12	Cat	10
13	Dog	10
14	Dog	10
15
16
17	Answer is Dog and House.

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

	List 1			D-List
Cat	10	20		Dog
Cat	10	20		House
Dog	20	40
Dog	10	40
Dog	10	40
House	5	5
	List 2
Cat	10	20
Cat	10	20
Dog	10	20
Dog	10	20

<TBODY>
</TBODY>

C3, copied down:

=SUMIF($A$3:$A$8,$A3,$B$3:$B$8)

C11, copied down:

=SUMIF($A$11:$A$14,$A11,$B$11:$B$14)

Define Ivec using Formulas | Name Manager as:

=ROW(Sheet1!$A$3:$A$8)-ROW(Sheet1!$A$3)+1

E3, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",
  IF(1-ISNUMBER(MATCH($A$3:$A$8&"|"&$B$3:$B$8,
      $A$11:$A$14&"|"&$B$11:$B$14,0)),
  MATCH($A$3:$A$8,$A$3:$A$8,0))),Ivec),Ivec),ROWS(E$3:E3))),"")

 

[pto160]

Thank you so much. I think it's probably a good idea to use the sum if in C3 and C11 and copy down, because it probably speeds up the operation more quickly than trying not to use sum if column to get the same result. Is that correct?
Why are we using the sum if in column c when it's not referenced in the formula in E3?
Is there any way to use this formula with advanced filter to achieve the same result, where dog and house will be filtered?

 

[Aladin Akyurek]

Thank you so much. I think it's probably a good idea to use the sum if in C3 and C11 and copy down, because it probably speeds up the operation more quickly than trying not to use sum if column to get the same result. Is that correct?
Why are we using the sum if in column c when it's not referenced in the formula in E3?
Is there any way to use this formula with advanced filter to achieve the same result, where dog and house will be filtered?

E3 meant to include the C ranges...
Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$3:$A$8,SMALL(IF(FREQUENCY(IF($A$3:$A$8<>"",
   IF(1-ISNUMBER(MATCH($A$3:$A$8&"|"&$C$3:$C$8,
          $A$11:$A$14&"|"&$C$11:$C$14,0)),
  MATCH($A$3:$A$8,$A$3:$A$8,0))),Ivec),Ivec),ROWS(E$3:E3))),"")

 

[pto160]

Thank you. Is there a way to use this formula with advanced filter to filter list A?

 

[Aladin Akyurek]

Thank you. Is there a way to use this formula with advanced filter to filter list A?

List A? The formula creates a list...

 

[pto160]

I remember in one of your posts you had used isna(match....m) using advanced filter to figure out what's not in one list. It filtered the results very quickly.I'm just thinking that advanced filter would make this operation faster. I'm dealing with 6000 rows.

 

[Aladin Akyurek]

I remember in one of your posts you had used isna(match....m) using advanced filter to figure out what's not in one list. It filtered the results very quickly.I'm just thinking that advanced filter would make this operation faster. I'm dealing with 6000 rows.

Perhaps something like below...

X1	Z1	Y1		X2	Y2	Z3
Cat	Cat|20	10		Cat	10	Cat|20		FALSE
Cat	Cat|20	10		Cat	10	Cat|20		X1	Z1
Dog	Dog|40	20		Dog	10	Dog|20		Dog	Dog|40
Dog	Dog|40	10		Dog	10	Dog|20		House	House|5
Dog	Dog|40	10
House	House|5	5

<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 881" width=25><COL style="WIDTH: 48pt" span=4 width=64><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3214" width=90><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><TBODY>
</TBODY>

B2, copied down:

=$A2&"|"&SUMIF($A$2:$A$7,$A2,$C$2:$C$7)

G2, copied down:

=$E2&"|"&SUMIF($E$2:$E$5,$E2,$F$2:$F$5)

Criteria range: I1:I2

I1 is left empty.

I2 houses:

=ISNA(MATCH(B2,$G$2:$G$5,0))

Run Advance Filter with:

Copy to another location checked
List range set to: $A$1:$B$7
Criteria range set to: $I$1:$I$2
Copy to is set as: $I$3

And the option for Unique records only checked.

 

[pto160]

Thank you so much Aladin. Both solutions work great. I'll probably use the formula method more. I was surprised how fast it extracted the list. It's saving me a lot of time at work.

 

[Aladin Akyurek]

Thank you so much Aladin. Both solutions work great. I'll probably use the formula method more. I was surprised how fast it extracted the list. It's saving me a lot of time at work.

Glad to help. Thanks for providing feedback.