+ Reply to Thread
Results 1 to 14 of 14

How to replace the character in one cell with the value in another column

  1. #1
    Registered User
    Join Date
    09-17-2015
    Location
    Los Angeles, USA
    MS-Off Ver
    2013
    Posts
    5

    How to replace the character in one cell with the value in another column

    Hello,

    I need help figuring out a formula that can replace the cells that contain an "X" with the number in the last column of each row. I'm looking to leave the cells that are blank as they are. Below is an example of what my table looks like:

    Tom x 1
    Tom x 2
    Tom x 3
    Ben x 4
    Ben x 5
    Ben x 6
    Ana x 7
    Ana x 8
    Ana x 9


    Eventually I would like to group all the results for Tom, Ben, and Ana in one row as below:

    Tom 1 2 3
    Ben 4 5 6
    Ana 7 8 9

    Can anyone please help me figure out how to do this? Thanks!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    Maybe this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    ------
    2
    Tom
    x
    1
    Tom
    1
    2
    3
    3
    Tom
    x
    2
    Ben
    4
    5
    6
    4
    Tom
    x
    3
    Ana
    7
    8
    9
    5
    Ben
    x
    4
    6
    Ben
    x
    5
    7
    Ben
    x
    6
    8
    Ana
    x
    7
    9
    Ana
    x
    8
    10
    Ana
    x
    9


    This array formula** entered in G2:

    =IFERROR(INDEX($C:$C,SMALL(IF($A$2:$A$10=$F2,ROW(A$2:A$10)),COLUMNS($G2:G2))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down to G4 then across until you get a column full of blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to replace the character in one cell with the value in another column

    You may also need a formula to get unique names

    Enter this formula in F2 and copy down

    =IFERROR(INDEX(A$2:A$10,MATCH(0,INDEX(COUNTIF(F$1:F1,A$2:A$10),0,),0)),"")

    and since you using Excel 2013 you can take advantage of AGGREGATE function

    in F2 and pull formula to the right and then down

    =IFERROR(INDEX($C:$C,AGGREGATE(15,6,ROW($C$2:$C$10)/(($A$2:$A$10=$F2)),COLUMNS($G$2:G2))),"")

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    Nmaes
    Values
    Unique Name List
    2
    Tom
    x
    1
    Tom
    1
    2
    3
    3
    Tom
    x
    2
    Ben
    4
    5
    6
    4
    Tom
    x
    3
    Ana
    7
    8
    9
    5
    Ben
    x
    4
    6
    Ben
    x
    5
    7
    Ben
    x
    6
    8
    Ana
    x
    7
    9
    Ana
    x
    8
    10
    Ana
    x
    9
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    Quote Originally Posted by AlKey View Post
    and since you using Excel 2013 you can take advantage of AGGREGATE function
    What advantage?

    The array formula is slightly faster to calculate.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to replace the character in one cell with the value in another column

    Quote Originally Posted by Tony Valko View Post
    What advantage?

    The array formula is slightly faster to calculate.
    In my experience:
    1. AGGREGATE 15,6 works much faster on large data sets.
    2. Doesn't require CTRL+SHIFT+ENTER

  6. #6
    Registered User
    Join Date
    09-17-2015
    Location
    Los Angeles, USA
    MS-Off Ver
    2013
    Posts
    5

    Re: How to replace the character in one cell with the value in another column

    Thanks for all the help!
    Last edited by bayala87; 09-18-2015 at 12:33 AM.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to replace the character in one cell with the value in another column

    Quote Originally Posted by AlKey View Post
    1. AGGREGATE 15,6 works much faster on large data sets.
    Interesting. I would have thought that, if anything, the reciprocation required in this type of construction would mean that AGGREGATE is fractionally slower than the equivalent array formula. Certainly not faster, though?

    Do you perhaps have a file with some testing results that you can share?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    Quote Originally Posted by AlKey View Post
    In my experience:
    1. AGGREGATE 15,6 works much faster on large data sets.
    My experience is just the opposite.

    Using the timer code found here:

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Timed 5 calculations for each range size and got the average of those 5 calculations.

    Here are the results:

    Data Range
    A
    B
    C
    D
    E
    1
    Rows
    100
    1000
    10000
    20000
    2
    AGGREGATE
    0.000792
    0.001000
    0.003034
    0.004944
    3
    ARRAY
    0.000786
    0.000992
    0.003002
    0.004692


    Tested in Excel 2010. The array formula was faster to calculate in each test.

  9. #9
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to replace the character in one cell with the value in another column

    @Tony,@XOR LX

    In my experience, where I deal with very large excel files 90,000+ rows almost daily, AGGREGATE performed much better than standard array formula. When I recommended to my co-workers to use AGGREGATE function, the feedback was always positive in respect of speed. The positive feedback about AGGREGATE performance was received from an OP of this forum.
    http://www.excelforum.com/excel-gene...ml#post4050633

    Regards,

    Alex

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How to replace the character in one cell with the value in another column

    @AlKey

    But have you done your own testing? Are you saying that Tony's results are somehow to be discredited/ignored?

    I'm afraid I simply cannot believe that the praise to which you refer, which "accelerated Excel performance from 2.5 min to 30 sec", can be down to simply replacing a CSE formula with the identical AGGREGATE construction.

    Think about it - why would AGGREGATE perform better? The same array has to be processed: in one case via an IF statement, in the other via (in general) a reciprocation. There simply cannot be much difference between the two forms in terms of processing. In fact, if anything, as I suggested (and as Tony's testing seems to prove), that reciprocation appears to be more slightly more costly in terms of calculation speed.

    Or it could be that the additional parameters with AGGREGATE cause a very slight increase. I don't know for sure. But, either way, it would seem that, unless you can produce some firmer evidence to the contrary, AGGREGATE performs slightly worse than the equivalent CSE construction.

    Regards

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: How to replace the character in one cell with the value in another column

    Quote Originally Posted by XOR LX View Post
    @AlKey

    Are you saying that Tony's results are somehow to be discredited/ignored?

    Regards
    I my reply to Tony and you I simply expressed my own observations and not scientific analysis. As far as "discredited" or otherwise "ignored" Tony's results, I did not express such an opinion.

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    Tested again on very large ranges...

    Data Range
    A
    B
    C
    D
    1
    Rows
    100,000
    200,000
    300,000
    2
    AGGREGATE
    0.017550
    0.031360
    0.043790
    3
    Array
    0.017334
    0.031848
    0.041133


    The array formula was faster in 2 out of 3 tests.

  13. #13
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    Back on topic...

    To the OP...

    Is your data sorted or grouped together as is demonstrated in the top post?

    Also, are there always 3 entries per name?

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to replace the character in one cell with the value in another column

    FWIW...

    I would consider the time differences in this application to be not significant. You don't need to know that this formula takes x amount of time to calculate while that formula takes y amount of time. However, it's a good thing to know that one syntax is more efficient than another and based on that fact is a better option. But, that knowledge takes a long time to acquire.

    If you ran the tests you would get different results based on the configuration of your own machine.

    I do this kind of testing extensively so I'm confident in my assessment.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace cell in Column A with value in Column B if contains similar character string
    By oybombadil in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 08:49 AM
  2. Search and replace character with cell value
    By rfcapinto in forum Excel General
    Replies: 2
    Last Post: 03-24-2014, 05:31 PM
  3. How to vlookup a Column contain special character and replace it?
    By miraclesuki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2013, 05:03 PM
  4. Change Cell Strings in Column to Replace Specifically the Second Character
    By Steve794421 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2013, 09:36 AM
  5. replace first character of cell content
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-26-2009, 11:33 AM
  6. I want to replace the first character in a column of text
    By Biredndra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-28-2005, 06:05 PM
  7. [SOLVED] HELP - How to replace CRLF with a character withing an excel column
    By Dino Buljubasic in forum Excel General
    Replies: 1
    Last Post: 08-25-2005, 03:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1