Page 2 of 3 FirstFirst 123 LastLast
Results 13 to 24 of 29

Thread: Excel expert needed

  1. #13

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    Quote Originally Posted by tsptalk View Post
    That will work. Thanks!
    Great, give me several minutes or so.
    [COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]

  2.  
  3. #14

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    Quote Originally Posted by tsptalk View Post
    That will work. Thanks!
    Any headers on columns A & B? Just the top row, if any?
    [COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]

  4.  
  5. #15

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    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]

  6.  
  7. #16

    Default Re: Excel expert needed

    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.

  8.  
  9. #17

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    Quote Originally Posted by tsptalk View Post
    Thanks. Sorry... had to run a minute. Nothing pretty. I just need something to create the list.
    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]

  10.  
  11. #18

    Default Re: Excel expert needed

    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.


  12.  
  13. #19

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    Quote Originally Posted by tsptalk View Post
    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
    Sure, I'll send it to you. But, if you COPY the data (not whole column) from F to bottom of E. Your column to work with is now E. I'm not sure how G entered the picture or exactly what you mean by concatenate as you are using it.?

    Stand by for example sheet.
    [COLOR=#0000ff][FONT=comic sans ms][I]"In the land of idiots, the moron is King."--Unknown[/I][/FONT][/COLOR]

  14.  
  15. #20

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    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]

  16.  
  17. #21

    Default Re: Excel expert needed

    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.

  18.  
  19. #22

    Join Date
    Jan 2014
    Posts
    7,885
    Blog Entries
    10

    Default Re: Excel expert needed

    Quote Originally Posted by tsptalk View Post
    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)
    Nope. That red 100 is for column B (Note $B$2)

    Quote Originally Posted by tsptalk View Post
    And if column B has 50 rows, would D1 be:

    =IFERROR(IF(MATCH(B1,OFFSET($A$1,0,0,50,1),0),"",B1),B1)
    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]

  20.  
  21. #23

    Default Re: Excel expert needed

    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!

  22.  
  23. #24

    Default Re: Excel expert needed

    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.

  24.  
Page 2 of 3 FirstFirst 123 LastLast

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
S&P500 (C Fund) (delayed)
Excel expert needed
(Stockcharts.com Real-time)
DWCPF (S Fund) (delayed)
Excel expert needed
(Stockcharts.com Real-time)
EFA (I Fund) (delayed)
Excel expert needed
(Stockcharts.com Real-time)
BND (F Fund) (delayed)
Excel expert needed
(Stockcharts.com Real-time)

Yahoo Finance Realtime TSP Fund Tracking Index Quotes