Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Concatenate Two Or Multiple Columns In To One

  1. #1
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11

    Concatenate Two Or Multiple Columns In To One

    Ok sure this is confusing enough, and I'm also sure I can do this without the need for a macro. What I need to do is take column B starting with row 7 and then add it to Column E starting with row 7. The results should be displayed on column J starting at row 7. The column J will NOT be displayed if possible as it is only being used to calculate other values. The values will not be numbers but rather text values. Here is an example.

    Column B = Photo
    Column E = Fail
    Column J = PhotoFail

    then on another sheet column J will be searched to calculate how many times the value "PhotoFail" is calculated and display the number. results for this example would be "1"

    This would need to be done for the entire column of "B" and column "E" and displayed on column "J"


    Thanks for any help

  2. #2
    Junior Member
    Join Date
    Dec 2012
    Posts
    6
    Rep Power
    0
    This does the first part (putting values in column J):

    Code:
    Sub PrepareJ()
        Dim N As Long
        N = Cells(Rows.Count, "B").End(xlUp).Row
        For I = 7 To N
            Cells(I, "J").Value = Cells(I, "B").Value & Cells(I, "E").Value
        Next I
    End Sub

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Another way

    Code:
    Sub PrepareJ()
    
        Dim lng As Long
        lng = Cells(Rows.Count, "B").End(xlUp).Row
        Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    Another way

    Code:
    Sub PrepareJ()
    
        Dim lng As Long
        lng = Cells(Rows.Count, "B").End(xlUp).Row
        Range("J7:J" & lng).Value = Evaluate("=B7:B" & lng & "&E7:E" & lng)
        
    End Sub
    Just pointing out... while it does not hurt anything to leave it in, the equal sign at the beginning of the argument to the Evaluate function is not needed... Evaluate will run the same with or without it.
    Last edited by Rick Rothstein; 07-03-2013 at 10:27 AM.

  5. #5
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    Is there a way to make this happen automatically, It works but only works when the code is run via macros command. I would like to know if I can have the values calculate automatically as the information is entered as the results of these combined words will be tabulated on another page and updated real time.

    Thanks

  6. #6
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    I also noticed that this code will calculate the whole workbook, can I make it only work on one sheet. even the automatic one would need the same setup if possible.

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Search for Worksheet_Change event to move value from one sheet to another. If you can't find a solution here, post back

    Regarding the calculation, your workbook seems to be quite heavy/formula intensive. Yes, the entire workbook gets calculated when there's a change. You could turn off sheet calculation for individual sheets using VBA, but it hinders the seamless calculation of data, especially when one range is linked to a range in another sheet.

    If you still need to do this, post back on this too.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    I got the values to move to another sheet called "executive summary". That was not a problem at all. It will look for keywords example "photopass" and count how many times it sees that word or several others in column J and place that value on the executive summary sheet. The issue I'm having is that the command assumes all worksheets have the same column values and heading and this is not the case. So when the macro is run it combines columns B and E on every page. The only page that is using that format is the initiating devices page. All others use different values so another formula would be needed for the other pages. Also the macro will only run when the command to run the macro is issued. It does not run live and update per entry.

    If it is too much of a pain I'm sure I could re arrange the column orders to match and change all resulting vba formulas to reflect that if needed. Depends what would be easier to do and what would work the best. Thanks again for the help.

  9. #9
    Junior Member
    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    I might be missing something here but can you just make it simple and make Column J = B1&E1, which will give you PhotoFail in J1. Concatenation at it's simplest.

  10. #10
    Member
    Join Date
    Jun 2013
    Posts
    52
    Rep Power
    11
    Yeah your right but how do you do that for the entire column?

Similar Threads

  1. Replies: 7
    Last Post: 05-15-2013, 02:56 PM
  2. Loop to two columns and Concatenate values
    By ivandgreat in forum Excel Help
    Replies: 15
    Last Post: 04-14-2013, 08:20 PM
  3. Concatenate multiple values
    By mcpizzle in forum Excel Help
    Replies: 3
    Last Post: 08-30-2012, 04:03 PM
  4. Replies: 2
    Last Post: 06-14-2012, 04:10 AM
  5. Concatenate Multiple Lookup Values in Single Cell
    By Admin in forum Download Center
    Replies: 4
    Last Post: 04-06-2012, 09:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •