Concatenate Two Or Multiple Columns In To One

[william516]

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

[Gary's Student]

This does the first part (putting values in column J):


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

[Excel Fox]

Another way


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

[Rick Rothstein]

Another way


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.

[william516]

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

[william516]

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.

[Excel Fox]

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.

[william516]

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.

[Bill Wendt]

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.

[william516]

Yeah your right but how do you do that for the entire column?

[Bill Wendt]

Just copy J1 to j2:j2000 will concatenate B2 to B2000 with E2 to E2000 in column J.