Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Array Formulas - Unique List from List with Duplicates

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default Array Formulas - Unique List from List with Duplicates

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"Johnny Meredith" wrote in message
ups.com...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default Array Formulas - Unique List from List with Duplicates

Why not just sort it?

--
HTH

Bob Phillips

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

"Johnny Meredith" wrote in message
ups.com...
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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Array Formulas - Unique List from List with Duplicates

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Array Formulas - Unique List from List with Duplicates

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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Array Formulas - Unique List from List with Duplicates

On 27 Oct 2006 10:17:01 -0700, "Johnny Meredith" wrote:

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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default Array Formulas - Unique List from List with Duplicates

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Array Formulas - Unique List from List with Duplicates

On 27 Oct 2006 12:33:51 -0700, "Johnny Meredith" wrote:

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.


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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
merge list while removing duplicates stef Excel Worksheet Functions 1 October 14th 06 07:52 PM
merge list while removing duplicates stef Excel Discussion (Misc queries) 0 October 13th 06 10:23 PM
Unique drop down list Saintsman Excel Worksheet Functions 0 January 20th 06 02:10 PM
Applying formulas only to the subtotals of a data list KG Excel Discussion (Misc queries) 5 September 10th 05 11:16 PM
Counting Repeated text or duplicates in a list Repeatdude Excel Discussion (Misc queries) 5 November 26th 04 08:10 PM


All times are GMT +1. The time now is 05:42 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"