Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge list while removing duplicates | Excel Worksheet Functions | |||
merge list while removing duplicates | Excel Discussion (Misc queries) | |||
Unique drop down list | Excel Worksheet Functions | |||
Applying formulas only to the subtotals of a data list | Excel Discussion (Misc queries) | |||
Counting Repeated text or duplicates in a list | Excel Discussion (Misc queries) |