I'm looking for help on how to concatenate data. The attached file shows where I'm starting from and where I want to go. Any help would be greatly appreciated. Thanks
[Solved] VBA: Concatenating data
-
-
-
Sorry, can't view the file from work due to our firewall.
If you could post up EG using HTML upload i'll take a look.
Assuming you want a formula solution:
to join text in cell A1 with that in B1
in C1 place =A1&B1
to get a gap between the text
in C1 place =A1&" "&B1A VB solution (very simple EG)
Hope this helps
-
Will, thanks for looking at it. I've attached an HTML file for you to see. I'm trying to combine more than 2 cells based on other input. Hopefully you'll see from the attachment.
Regards,
Bill W. -
Bill,
I'll try to have a look at your file(s) later as can't view or download at work.
Our firewall prevents this sort of thing
:mad: sorry! -
I do not have the Excel to HTML plugin.
I can attach a (two) .jpgs of what WillR is looking for. -
-
Here is what he is looking to accomplish.
-
I've attached an amended file with the following code. Its works on the data displayed.
You'll need to adjust the ranges to suit.
HTH
Sub ConCat()
'assumes column B has been sorted
Dim c
Dim ConCatCell As String
Dim NextRowConCatCell = ""
NextRow = 0For Each c In Sheets("changes").Range("b10:b100")
'first time thru
If ConCatCell = "" Then
ConCatCell = c.Offset(0, -1)
NewPartNum = True
Else
'if the next row is thsame as this then add the cell to the concat
If c = c.Offset(1, 0) Then
ConCatCell = ConCatCell & "," & c.Offset(0, -1)
Else
'otherwise add it before copyin it across
ConCatCell = ConCatCell & "," & c.Offset(0, -1)
Sheets("changes").Range("h13").Offset(NextRow, 0) = c
Sheets("changes").Range("h13").Offset(NextRow, -1) = ConCatCell
'increment row for listing
NextRow = NextRow + 1
'start a new concat
ConCatCell = ""
End IfEnd If
Next c
End Sub
-
Neale.
Nice :cool: :cool: :cool:
(every time I think I get th hang of this, something like this pops up and shows me how much more there is to learn)
Hats off :biggrin:
-
Thanks Will, much appreciated.
BTW the
NewPartNum = True
statement is redundant and can be deleted - it will cause an error if run with option explicit as I deleted the dim for NewPartNum
Thanks again for the comments Will
-
Neale,
Thanks for the code. I’ve made some changes to make it run. Some observations, because I need to have the concat based on the NOTE field as well as the Part NUMBER field I needed to sort my input by NOTE field then by PART NUMBER field otherwise the code doesn’t work properly. Look at the data in M45 thru M56, looking closely you’ll see that C302 was incorporated into C0A24 etc. when it should have been a standalone. After my new sort the code worked. On single references a space was added to the data, I’ve added code to remove that also. The true output I ended up with is shown in the outlined box. Your incite was a great help, after further coding I may be back for more help.
Regards,
Bill W.
P.S. Used a space rather than a comma to help me format the REFERENCE cells.
:biggrin::biggrin::biggrin: -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!