Array Formulas - Unique List from List with Duplicates

[Johnny Meredith]

Hi all,

I've seen several posts on this subject, but I have a particular
situation I can't figure out.

I have a list that looks like this:

Term Category Amount
S Cat1 100
L Cat1 50
L Cat2 25
S Cat3 10
L Cat3 30

I want this result:

Term Cateogry Amount
S Cat1 100
S Cat3 10
L Cat1 50
L Cat2 25
L Cat3 30

The totals for both lists equal 215. Basically, I need to produce a
unique list of Categories by Term. I need to do this with an array
formula is possible. So the array formula would have to look at Term
(there will always only be two Term values: S & L) and pick unique
Categories (there will be many categories and the categories will
change periodically). I can handle the Amount column with SUMIF
functions. The PivotTable option is not possible. Any ideas?

Thanks in Advance,
Johnny

 

[Bernard Liengme]

Let's assume the data to be in columns A (term), B (Cat) and C (Amount),
with labels in row 1 and data in rows 2 to 500.
Repeat the labels in E1:G1
Enter the Terms codes (S, L) in E2 and down and the Category codes (Cat1,
Cat2...) in F2 down
In G2 enter =SUMPRODUCT(--($A$2:$A$500=E2),--($B$2:$B$500=F2),$C$2:$C$500)
Enter this in the normal way, it is NOT an array function
Copy this down the G row
Done
For explanations of SUMPRODUCT see

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes

 

[Bob Phillips]

Why not just sort it?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Johnny Meredith]

Sorting it won't aggregate the duplicate categories. Besides, I'm
trying to get this to work without any "user clicks".

 

[Johnny Meredith]

I don't know what categories there will be. I need this to work in the
general case where the user can input any category they want.
Therefore I can't input the unique categories beforehand, only the term
codes.

 

[Ron Rosenfeld]

Sorting it won't aggregate the duplicate categories. Besides, I'm
trying to get this to work without any "user clicks".

In the example you posted, all the categories by term seem to be unique.

Can you post an example which demonstrates what you mean by "aggregate
duplicate categories"?

To reproduce your results from your posted data, you can use sorting or, if you
want to use a formula, you could use the VSORT function from Longre's free
morefunc.xll add-in.

But if you also want to aggregate, I think my approach would be to write a
macro that could be triggered either by having the user push a button, or by
some appropriate worksheet event.
--ron

 

[Johnny Meredith]

To reproduce your results from your posted data, you can use sorting or, if you

want to use a formula, you could use the VSORT function from Longre's free
morefunc.xll add-in.

Can you post a link for this? All the links I've found in Groups are
broken.

I inadvertantly produced an example where pairing Term and Category
always results in a unique combination. In real life, that won't
necessairly be the case.

Thanks,
Johnny

 

[Ron Rosenfeld]

Can you post a link for this? All the links I've found in Groups are
broken.
http://xcell05.free.fr/



I inadvertantly produced an example where pairing Term and Category
always results in a unique combination. In real life, that won't
necessairly be the case.

VSORT won't be the entire answer, then.
--ron