Extracting unique values from a column in a table - am I doing this as efficiently as possible?

[diversification]

unrealized cap gain loss finder project.xlsx

Shared with Dropbox

www.dropbox.com

^ This is an example of the data I'm using. In reality, Sheet1 is ~30,000 Rows deep, and Sheet2 is ~800 Rows deep.

What I'm trying to accomplish:

• Table1 (in Sheet1) is the raw data showing a ton of Account#s with the Symbols they hold, and for each Symbol, it breaks out the purchase lots across multiple rows, and specifies whether each lot has an Unrealized Gain or Loss associated with it.
• Table 2 (in Sheet2) has a greatly reduced subset of the same Account#s and Symbols in Table1. Each unlike in Table1 which has the same Account# & Symbol combination appearing in multiple rows, each combination of Account# and Symbol only appears in one single row in Sheet2.
• For each row in Table2, I want the following:
  • Match the Account# and Symbol to all occurrences in Table1
  • For all matches, sum separately the Short Unrealized Gain or Loss and the Long Unrealized Gain or Loss
  • In a new column in Sheet2, combine the following items: (1) The Symbol from that row (2) The Value from that row (3) The aforementioned summed Short Unrealized Gain or Loss (4) The aforementioned summed Long Unrealized Gain or Loss

My Steps:

1. In Table1, Column V, I create a column titled "Acct, Ticker, L/S" which combines the Account #, the Symbol, and the type of Unrealized Gain (Long or Short) into one cell using the following formula:

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

2. In Sheet1, Column Z, outside of Table1, I created a column titled "Unique Acct, Ticker, L/S". This column isolates the unique values from the "Acct, Ticker, L/S" column from Step 1 (you'll recall that I said in Table1, there are multiple occurrences of the same combination of Account #, Symbol, and the 'Long' or 'Short' designation.

   =UNIQUE(Table1[Acct, Ticker, L/S])

3. In Sheet1, Column AA, outside of Table1, I created a column titled "G/L Total". Using the neighboring cell in Column Z (Step 2 - "Unique Acct, Ticker, L/S") this formula looks for matches in Column V (Step 1 - "Acct, Ticker, L/S") and sums the Unrealized Gain or Loss from Table1[Unrealized Gain Loss] (Column P).

   =SUMIF(Table1[Acct, Ticker, L/S],@Z:Z,Table1[Unrealized Gain Loss])

4. In Table2, Column M, I created a column titled "Unrealized Short G/L" looks at the Table2[Account Number], Table2[Symbol] and uses the following formula to pull in the associated "G/L Total" (Step 3 - Sheet1, Column AA) from Sheet1.

   =INDEX(Sheet1!$AA:$AA, MATCH((TRIM([@[Account Number]])&", "&TRIM([@Symbol])&", Short"),Sheet1!$Z:$Z, 0))

5. In Table2, Column N, I created a column titled "Unrealized Long G/L" and did the same exact thing as in Step 4, except this time it looks for "Long" instead of "Short." =INDEX(Sheet1!$AA:$AA, MATCH((TRIM([@[Account Number]])&", "&TRIM([@Symbol])&", Long"),Sheet1!$Z:$Z, 0))

   =INDEX(Sheet1!$AA:$AA, MATCH((TRIM([@[Account Number]])&", "&TRIM([@Symbol])&", Long"),Sheet1!$Z:$Z, 0))

6. In Table2, Column P ("Symbol, Val, G/L"), for each row, I combine the corresponding values from columns F, H, M, N ("Symbol", "Value", "Unrealized Short G/L", "Unrealized Long G/L"). This gives me what I wanted.

   =[@Symbol]&" -- Current Value: "&TEXT([@Value],"$#,##0;($#,##0)")&",   Short: "&IFERROR(TEXT([@[Unrealized Short G/L]],"$#,##0;($#,##0)"),TEXT(0,"$0"))&",   Long: "&IFERROR(TEXT([@[Unrealized Long G/L]],"$#,##0;($#,##0)"),TEXT(0,"$0"))

QUESTION:
Is there a better, more efficient, and less resource-intensive way to find the unique values in Step 2 WITHOUT a macro / VBA code? I don't understand that stuff well, so if it breaks I'm going to be back here trying to get troubleshooting help, and that could cause too much of a delay for something I already have working (albeit slowly.) I'm guessing the reason this particular formula slows down the sheet so much is because it's having to check each of the ~30,000 cells in the column against the already-found unique values, which obviously is a ton of work. I'd also like to avoid doing it manually using the "eliminated duplicates" button or whatever it is.

 

[padapinto]

its easier understood with a snapshot of the excel sheet atleast

 

[Fluff]

For M2 on sheet 2, you could use

=SUM(FILTER(Table1[Unrealized Gain Loss],(Table1[Account '#]=[@[Account Number]])*(Table1[Symbol]=[@Symbol])*(Table1[Short or Long Term]="Short"),0))

 

[diversification]

its easier understood with a snapshot of the excel sheet atleast

I included the dropbox link to the workbook at the very top. Is that not preferable to a snapshot?

 

[Fluff]

Did you see my suggestion in post#3?

 

[padapinto]

I included the dropbox link to the workbook at the very top. Is that not preferable to a snapshot?

the way i see that data, its possible to use formulas but having some cells that take raw data and the rest have formulas to receive it. for me it will take time to make it all happen but its true to say formulas are possible.

 

[Fluff]

You can get rid of cols Z & AA & col V if you don't it & just use

unrealized cap gain loss finder project.xlsx
	F	G	H	I	J	K	L	M	N	O	P
1	Symbol	Blah4	Value	Blah5	Blah6	Blah7	Blah8	Unrealized Short G/L	Unrealized Long G/L	Blah9	Symbol, Val, G/L
2	AFOJ	data	$36,514,654.00	data	data	data	data	$67.20	$0.00	data	AFOJ -- Current Value: $36,514,654, Short: $67, Long: $0
3	GIOJNS	data	$351.25	data	data	data	data	$12.15	$85.00	data	GIOJNS -- Current Value: $351, Short: $12, Long: $85
4	OAGI	data	$444.00	data	data	data	data	$115.61	($28.50)	data	OAGI -- Current Value: $444, Short: $116, Long: ($29)
5	OREUTY	data	$6,668.44	data	data	data	data	$908.98	$118.38	data	OREUTY -- Current Value: $6,668, Short: $909, Long: $118
6	RRR	data	$56,165.00	data	data	data	data	($567.45)	($2,324.85)	data	RRR -- Current Value: $56,165, Short: ($567), Long: ($2,325)
7	AOKFJ	data	$596,849,648.00	data	data	data	data	$2,171.85	$58.33	data	AOKFJ -- Current Value: $596,849,648, Short: $2,172, Long: $58
8	ALDFSKJ	data	$81,647.96	data	data	data	data	$330.26	$1,016.37	data	ALDFSKJ -- Current Value: $81,648, Short: $330, Long: $1,016
9	TOIJF	data	$56,648.11	data	data	data	data	$6,076.89	$0.00	data	TOIJF -- Current Value: $56,648, Short: $6,077, Long: $0
10	SOFK	data	$46,165.66	data	data	data	data	$1,938.09	$1,422.16	data	SOFK -- Current Value: $46,166, Short: $1,938, Long: $1,422
11	AFOJ	data	$146,161.98	data	data	data	data	$1,041.52	($955.92)	data	AFOJ -- Current Value: $146,162, Short: $1,042, Long: ($956)
12	GIOJNS	data	$546,128.00	data	data	data	data	$2,999.58	$0.00	data	GIOJNS -- Current Value: $546,128, Short: $3,000, Long: $0
13	OAGI	data	$54,773.00	data	data	data	data	$0.00	$0.00	data	OAGI -- Current Value: $54,773, Short: $0, Long: $0
14	OREUTY	data	$354.00	data	data	data	data	$39.70	$0.00	data	OREUTY -- Current Value: $354, Short: $40, Long: $0
15	ALAKO	data	$654,642.58	data	data	data	data	$0.00	$97.65	data	ALAKO -- Current Value: $654,643, Short: $0, Long: $98
16	LAFKN	data	$54.00	data	data	data	data	($96.68)	$2,060.80	data	LAFKN -- Current Value: $54, Short: ($97), Long: $2,061
17	OIUB	data	$3.00	data	data	data	data	$0.00	$22.95	data	OIUB -- Current Value: $3, Short: $0, Long: $23
18	EWOA	data	$75.00	data	data	data	data	$16.00	$15.06	data	EWOA -- Current Value: $75, Short: $16, Long: $15
19	OO	data	$67,586.56	data	data	data	data	($462.27)	$244.66	data	OO -- Current Value: $67,587, Short: ($462), Long: $245
20	WEOTUI	data	$45.00	data	data	data	data	$0.00	($250.45)	data	WEOTUI -- Current Value: $45, Short: $0, Long: ($250)
21	AFSOLIJ	data	$5,436.11	data	data	data	data	($1.86)	$173.60	data	AFSOLIJ -- Current Value: $5,436, Short: ($2), Long: $174
22	VGOI	data	$3,746,344.00	data	data	data	data	$0.00	$163.12	data	VGOI -- Current Value: $3,746,344, Short: $0, Long: $163
23	IWLD	data	$34.00	data	data	data	data	($41.23)	$2.00	data	IWLD -- Current Value: $34, Short: ($41), Long: $2
24	WOWO	data	$1,654.55	data	data	data	data	$178.58	($320.54)	data	WOWO -- Current Value: $1,655, Short: $179, Long: ($321)
25	AOFKJ	data	$43.23	data	data	data	data	$0.00	$201.75	data	AOFKJ -- Current Value: $43, Short: $0, Long: $202
26	AFOJ	data	$43,345.43	data	data	data	data	$0.00	$103.95	data	AFOJ -- Current Value: $43,345, Short: $0, Long: $104
27	GIOJNS	data	$543.00	data	data	data	data	$139.08	$247.27	data	GIOJNS -- Current Value: $543, Short: $139, Long: $247
28	OAGI	data	$354.00	data	data	data	data	$1,296.11	$2,037.20	data	OAGI -- Current Value: $354, Short: $1,296, Long: $2,037
29	OIUB	data	$434,345.34	data	data	data	data	$0.00	$103.99	data	OIUB -- Current Value: $434,345, Short: $0, Long: $104
30	EWOA	data	$65,882.96	data	data	data	data	$60.87	$0.00	data	EWOA -- Current Value: $65,883, Short: $61, Long: $0
31	OO	data	$4,354.00	data	data	data	data	$0.00	$747.97	data	OO -- Current Value: $4,354, Short: $0, Long: $748
Sheet2

Cell Formulas
Range	Formula
M2:M31	M2	=SUM(FILTER(Table1[Unrealized Gain Loss],(Table1[Account '#]=[@[Account Number]])*(Table1[Symbol]=[@Symbol])*(Table1[Short or Long Term]="Short"),0))
N2:N31	N2	=SUM(FILTER(Table1[Unrealized Gain Loss],(Table1[Account '#]=[@[Account Number]])*(Table1[Symbol]=[@Symbol])*(Table1[Short or Long Term]="long"),0))
P2:P31	P2	=[@Symbol]&" -- Current Value: "&TEXT([@Value],"$#,##0;($#,##0)")&", Short: "&TEXT([@[Unrealized Short G/L]],"$#,##0;($#,##0)")&", Long: "&TEXT([@[Unrealized Long G/L]],"$#,##0;($#,##0)")

 

[diversification]

For M2 on sheet 2, you could use

=SUM(FILTER(Table1[Unrealized Gain Loss],(Table1[Account '#]=[@[Account Number]])*(Table1[Symbol]=[@Symbol])*(Table1[Short or Long Term]="Short"),0))

You're the MVP. It wasn't working at first, so I threw in a TRIM() around all the account number and symbol references, and it fixed it. I assume there were hidden spaces / characters or the columns were formatted differently or something like that. This solution is MUCH more lightweight than my method. Thank you!!

 

[Fluff]

Glad to help & thanks for the feedback.