# Extracting unique values from a column in a table

#### diversification

##### New Member
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
Excel Formula:
``  =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:
Excel Formula:
``=UNIQUE([Acct, Symbol, LongShort])``
Excel Formula:
``=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
Excel Formula:
``=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!

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### diversification

##### New Member
Ok, so I just ran
Excel Formula:
``=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

##### MrExcel MVP, Moderator
You cannot have spill ranges in a structured table.

#### diversification

##### New Member
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

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

#### Fluff

##### MrExcel MVP, Moderator
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.

Replies
8
Views
258
Replies
3
Views
458
Replies
1
Views
95
Replies
4
Views
138
Replies
5
Views
77