ok, it's ready for you to test it out...
just want to tweak it for you if you have headers.?
Last edited by userque; 06-24-2015 at 08:11 PM. Reason: typo
[COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]
Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.
Tom
Market Commentary | My Blog | TSP Talk Plus | |
I am not a Registered Investment Advisor and this is not investment advice. Please do your own due diligence.
I understand. The headers question was more about how to initialize the formulas for you; and how I explain how to use it. Trust me, there is nothing pretty about this solution Assuming no headers:
Column C; Row 1:
=IFERROR(IF(MATCH(A1,OFFSET($B$1,0,0,5,1),0),"",A1 ),A1)
Where "5" refers to the LAST ROW WITH DATA IN COLUMNS A/B
Column D; Row 1:
=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,5,1),0),"",B1 ),B1)
Where "5" refers to the LAST ROW WITH DATA IN COLUMNS A/B
Fill these formulas down to the last row with data in columns a/b
They should populate with names and blanks.
COPY AS VALUES ONLY columns c and d into columns e and f. This will remove the formulas and leave only values/blanks. Then proceed as we discussed.
Let me know if you need more information on copying as values only.
EDIT: Instead of "5" above, use YOUR value. "5" is just an example.
[COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]
OK, I must have done something wrong because I end up with a concatenation of the two columns in the final column (I'll call "G").
2 columns, A & B.
C & D filled with new formulas using final row of column as the "5"
Copy values of C & D to E & F
Concatenate F to bottom of E to create G.
Sort column G removing 0's.
Result is all email addresses in one row.
Do you have a small excel file example you can email or PM (or attach here)?
Thanks
Tom
Market Commentary | My Blog | TSP Talk Plus | |
I am not a Registered Investment Advisor and this is not investment advice. Please do your own due diligence.
[COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]
Tom, while I wait for your PM:
excel.png
E and F were copied by value from C and D.
F (data, not column) copied to bottom of E. E is twice as long now.
E is the final column to remove blanks and sort if you like. I darkened some cells for clarity.
Last edited by userque; 06-24-2015 at 09:42 PM. Reason: File sent
[COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]
So if column A has 100 rows the equation for C1 is:
=IFERROR(IF(MATCH(A1,OFFSET($B$2,0,0,100,1),0),"",A1 ),A1)
And if column B has 50 rows, would D1 be:
=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,50,1),0),"",B1),B1)
Or would it be:
=IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,100,1),0),"",B1),B1)
Tom
Market Commentary | My Blog | TSP Talk Plus | |
I am not a Registered Investment Advisor and this is not investment advice. Please do your own due diligence.
Nope. That red 100 is for column B (Note $B$2)
Naw. That number is for column A
Tom, I see $B$2 and $A$1. Does B have a header or something...and you changed it to 2? It was $B$1 when I wrote it. And if you did change it because of a header, then we have to change something else as well.
Last edited by userque; 06-24-2015 at 10:29 PM. Reason: typo
[COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]
just use the countif function. If you count more than 1 of a name, ignore it with a blank. Once you've done that, use the Filter feature to filter out the blanks. Copy the new Column(s) and paste VALUES into a new sheet. done!
to combine two columns into 1:
=INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2))
to ignore values that happen more than once:
=IF(COUNTIF(A:B,"="& Cell )=1, Cell,"")
The words Cell get replaced with the 1st equation.
end result:
=IF(COUNTIF(A:B,"="&INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2)))=1,INDEX(A:B,ROUNDUP(ROW()/2,0),2-MOD(ROW(),2)),"")
Put it in the 3rd column, 1st cell, and drag it down. must drag it down to TWICE the length of the longest column, A or B.
S&P500 (C Fund) (delayed) (Stockcharts.com Real-time) |
DWCPF (S Fund) (delayed) (Stockcharts.com Real-time) |
EFA (I Fund) (delayed) (Stockcharts.com Real-time) |
BND (F Fund) (delayed) (Stockcharts.com Real-time) |
||
Yahoo Finance Realtime TSP Fund Tracking Index Quotes |
Bookmarks