diversification
New Member
- Joined
- Jun 24, 2020
- Messages
- 40
- Office Version
- 365
- Platform
- 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:
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
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:
. 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
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!
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]]))
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)
3. Once I do isolate the unique values, I'm planning to use
Excel Formula:
=SUMIF([Acct, Symbol, LongShort], @[Unique], [Values])
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!