Extracting unique values from a column in a table

[diversification]

Hi there,

I'm very new to using Tables, but so far they seem to be pretty amazing but I've run into an issue. I have a large table that's about 28,500 rows by 10 columns. My goal is to identify similar rows and then sum the values of those similar rows from a particular column.

Assume the abbreviated table is something like this:

blah	blah	Account #	Values	Symbol	blah	Short or Long Term	Acct, Symbol, LongShort	Unique	Sum
blah	blah	12345	5	RR	blah	Long	12345, RR, Long	12345, RR, Long	15
blah	blah	12345	7	AA	blah	Short	12345, AA, Short	12345, AA, Short	7
blah	blah	12345	10	RR	blah	Long	12345, RR, Long	12345, RR, Short	14
blah	blah	12345	14	RR	blah	Short	12345, RR, Short	11111, RR, Short	19
blah	blah	11111	19	RR	blah	Short	11111, RR, Short	22222, AA, Short	41
blah	blah	22222	40	AA	blah	Short	22222, AA, Short	22222, AA, Long	8
blah	blah	22222	1	AA	blah	Short	22222, AA, Short
blah	blah	22222	8	AA	blah	Long	22222, AA, Long

My approach is as follows:

1. In the Acct, Symbol, LongShort column, combine that row's corresponding values residing in the "Account #", "Symbol", and "Short or Long Term" columns by using

  =TEXTJOIN(", ", TRUE,TRIM([@[Account '#]]), TRIM([@Symbol]), TRIM([@[Short or Long Term]]))

This is works as expected.

2. Isolate the unique values from the Acct, Symbol, LongShort column by using the UNIQUE function. I've tried each of the following:

=UNIQUE([Acct, Symbol, LongShort])

=UNIQUE(H:H)

. All of these SPILL. I've also tried copying / pasting the Acct, Symbol, LongShort column to a new one as-text and running these UNIQUE formulas on the text column, but I still get spilling.

3. Once I do isolate the unique values, I'm planning to use

=SUMIF([Acct, Symbol, LongShort], @[Unique], [Values])

so that hopefully the function will say for each Unique value, find all the matching values in the Acct, Symbol, LongShort column, and then sum up the corresponding entries from the Values column.

Can someone help me figure out the proper formula for Step 2?

I believe step 3 will work, but I can't test it until I figure out #2.



Thanks!

 

[diversification]

Ok, so I just ran

=UNIQUE(TableName[Acct, Symbol, LongShort])

outside of the table and it worked. I tried to make that column a separate table and it immediately spilled. Can anyone explain why this is happening? I'd really prefer to keep everything contained in a table...

 

[Fluff]

You cannot have spill ranges in a structured table.

 

[diversification]

You cannot have spill ranges in a structured table.

I guess I'm not certain I understand, so maybe if I describe the way I think it works you can pick out what I'm not getting. So my belief is that in a structured table, the function would be filled down the table. Since the unique function is isolating a much smaller subset of the values in the corresponding column, I wouldn't think that the function would spill. So for example, let's say I have a table with column A having 100 values, where 50 of those values are unique, and in column B I have the UNIQUE function as described above. The UNIQUE function should only be returning 50 values, which is well within the table length and therefore shouldn't be spill outside of the table.

Can you please tell me what it is that I'm missing here?

 

[KevCarter]

I think this will be a helpful explanation of Fluff's point.

Dynamic array formulas and spilled array behavior - Microsoft Support

Learn about how Excel's dynamic, and spilled array formulas behave.

support.microsoft.com

 

[Fluff]

A formula cannot spill if it is in a table.
It makes no difference whether the table is large enough to handle the spill range, or not.