Extracting unique values from a column in a table

diversification

New Member
Joined
Jun 24, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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:

blahblahAccount #ValuesSymbolblahShort or Long TermAcct, Symbol, LongShortUniqueSum
blahblah123455RRblahLong12345, RR, Long12345, RR, Long15
blahblah123457AAblahShort12345, AA, Short12345, AA, Short7
blahblah1234510RRblahLong12345, RR, Long12345, RR, Short14
blahblah1234514RRblahShort12345, RR, Short11111, RR, Short19
blahblah1111119RRblahShort11111, RR, Short22222, AA, Short41
blahblah2222240AAblahShort22222, AA, Short22222, AA, Long8
blahblah222221AAblahShort22222, AA, Short
blahblah222228AAblahLong22222, 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!
 

Some videos you may like

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
Joined
Jun 24, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
You cannot have spill ranges in a structured table.
 

diversification

New Member
Joined
Jun 24, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,130
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,021
Messages
5,545,542
Members
410,690
Latest member
navneetr
Top