PDA

View Full Version : concatenate a range of cells with header values



stlockridge
11-28-2011, 02:07 PM
I need to concatenate a range of cells (approx. 50) across a row (for many rows in my spreadsheet) and before each cell value, insert the cell's header row value. The header value and cell value needs to be separated by [:]

Each header/value pair needs to be separated by [;]

Example: Color[:]Red[;]Size[:]Large[;]Length[:]Long

VB preferred. Many thanks!

mdmackillop
11-28-2011, 03:02 PM
Welcome to VBAX
Something like this
Sub Concat()
Dim Rng As Range
Dim i As Long

For i = 1 To 10
txt = txt & Cells(1, i) & "[:]" & Cells(2, i) & "[:]"
Next

Cells(3, 1) = Left(txt, Len(txt) - 3)
End Sub

stlockridge
11-28-2011, 03:27 PM
Thanks! I'm quite a novice (if I can even call myself that) at VB and I've tried your suggestion but I'm obviously overlooking something simple.

Does one need to "select" the row and cells then run this? Or can the macro be executed without doing this?

I'd prefer not having to select the rows as there are approx. 50,000 rows of data.

I also should have been more specific that ideally I need the end result inserted in a new column at the far right of my data with a given header value (guess I'll call it DYNAMIC).

I'm using 2003, I suppose that could be very helpful as well.

Thanks so much for your time on this.

stlockridge
11-28-2011, 03:29 PM
Also, each size/value pair is separated by [:] the colon. Each iteration of size/value pair is separated by [;] the semi colon.

Thanks again so much!

mdmackillop
11-28-2011, 03:38 PM
Option Explicit

Sub Concat()
Dim Rng As Range
Dim i As Long, j As Long
Dim Col As Long, Rw As Long
Dim txt As String

Col = Cells(1, Columns.Count).End(xlToLeft).Column
Rw = Cells(Rows.Count, 1).End(xlUp).Row


Cells(1, Col + 1) = "Dynamic"
For j = 2 To Rw
txt = ""
For i = 1 To Col
txt = txt & Cells(1, i) & "[:]" & Cells(j, i) & "[;]"
Next i
Cells(j, Col + 1) = Left(txt, Len(txt) - 3)
Next j

End Sub

stlockridge
11-29-2011, 07:37 AM
You are the absolute bomb! Thanks soooooo much!

Just a minor couple of tweaks.

Everything looks great, but could you help me with specifying the function for just columns M thru the last with data (or BL if that's far easier for you).

I'm also getting a Run-time error '13' Type mismatch. Of course I don't know why. :think:

When I click to debug, it highlights this line of code:

txt = txt & Cells(1, i) & "[:]" & Cells(j, i) & "[;]"
Again, this has been so helpful! I realize without a file or clearer expectations it's a bit like shooting in the dark, but this is definitely getting there. I can't thank you enough!

mdmackillop
11-29-2011, 01:18 PM
Can you post a small sample, particularly that bit which causes the error. You can post a file using Manage Attachments in the Go Advanced reply section

stlockridge
11-29-2011, 01:34 PM
Attached is a sample of the data. Only columns M thru BL would need to be concatenated with delimiters as your last code does so beautifully.

I'm not sure where the error is occurring... it looks like all rows that are supposed to populate, are being populated with data.

Not every row will have a value inserted in the "Dynamic" column. Perhaps this might offer a clue.

Let me know if I can be of further assistance since you've been such a huge help here.

Thanks!

mdmackillop
11-29-2011, 02:00 PM
To omit blank rows
Sub Concat()
Dim Rng As Range
Dim i As Long, j As Long
Dim Col As Long, Rw As Long
Dim txt As String
Dim Chk As Long

Col = Cells(1, Columns.Count).End(xlToLeft).Column
Rw = Cells(Rows.Count, 1).End(xlUp).Row


Cells(1, Col + 1) = "Dynamic"
For j = 2 To Rw
txt = ""
Chk = Application.CountA(Range(Cells(j, 14), Cells(j, Col)))
If Chk > 0 Then
For i = 14 To Col
txt = txt & Cells(1, i) & "[:]" & Cells(j, i) & "[;]"
Next i
Cells(j, Col + 1) = Left(txt, Len(txt) - 3)
End If
Next j

End Sub

This will concatenate only those cells with a value
Sub Concat2()
Dim Rng As Range
Dim i As Long, j As Long
Dim Col As Long, Rw As Long
Dim txt As String

Col = Cells(1, Columns.Count).End(xlToLeft).Column
Rw = Cells(Rows.Count, 1).End(xlUp).Row


Cells(1, Col + 1) = "Dynamic"
For j = 2 To Rw
txt = ""
For i = 14 To Col
If Cells(j, i) <> "" Then
txt = txt & Cells(1, i) & "[:]" & Cells(j, i) & "[;]"
End If
Next i
If txt <> "" Then Cells(j, Col + 1) = Left(txt, Len(txt) - 3)
Next j

End Sub

stlockridge
11-29-2011, 02:31 PM
So the function to omit blank rows will not delete the entire row will it? I'm not looking for it to do that... it's just that some of my items don't have purchase attributes. The items themselves still need to be in the data but items that do have attributes have to be formatted in the manner previously described. Many thanks!

mdmackillop
11-29-2011, 02:34 PM
Nothing is deleted

stlockridge
11-29-2011, 02:46 PM
Wonderful! Will give it a try!

stlockridge
11-29-2011, 03:10 PM
I apologize for my ignorance in VB, but on your last recommendations, am I to run the two different macros, one after the other?

If so, it's giving results now that look far worse than before, running these latest suggestions.

The end result is populating values (whether empty or not) of every column.

Here is an example of the result in Dynamic column #1:

size1[:]MEDIUM[;]EXCLUDED_DESTINATION[:][;]EXPIRATION_DATE[:][;]ATTACHMENT[:][;]BADGE_FINISH[:][;]BADGE_SEAL[:][;]BADGE_STYLE[:][;]BLADE[:][;]BUTTON_TYPE[:][;]CHARGER_TYPE[:][;]CLOSURE[:][;]COLOR_SIDE[:][;]COLOR_SIZE[:][;]CONTRACT_VIP[:][;]DOLLAR_AMOUNTS[:][;]FINISH1[:][;]FINISH_AND_HAND[:][;]FIT[:][;]FLASHLIGHT_TYPE[:][;]FLAVOR[:][;]FONT[:][;]HAT_SIZE[:][;]INSEAM[:][;]LENGTH1[:]REGULAR[;]LETTERS[:][;]LEVEL1[:][;]LIGHT[:][;]LOCATION1[:][;]MISC[:][;]MODEL1[:][;]MOUNT_TYPE[:][;]ORDER_QTY[:][;]PACKAGING[:][;]QTY[:][;]SERVICE_YEARS[:][;]SHAPE[:][;]SHOE_SIZE[:][;]SHOE_SIZE_WIDTH[:][;]SIDE[:][;]SIZE_CODE[:][;]STATE[:][;]STYLE1[:][;]TAYLOR[:][;]TITLE_1[:][;]TYPE1[:][;]VEHICLE_MAKE[:][;]VENDOR[:][;]WEAPON_MODEL[:][;]WEAPON_TYPE[:][;]WIDTH[:][;]YEAR1[:]

If a column has no value, I do not need the attribute[:]null_value populated in my DYNAMIC column. In the above example, I would only need size1[:]MEDIUM returned. If there are multiple attribute/value pairs, those would need the [;] between.

Also, for some strange reason, there is a second Dynamic column now in my spreadsheet after running both of these concurrently.

Your 11/28 post gave me exactly what I needed, but it only included columns A-L, which I originally failed to tell you I did not need.

stlockridge
11-29-2011, 03:32 PM
Running just the 2nd function Concat2 appears to give me what I need.

I'm no longer getting errors it seems either.

Here is a result being returned that has 3 attribute/value pairs:

color[:]DARK NAVY[;]size1[:]6X[;]LENGTH1[:]REGULAR

stlockridge
11-30-2011, 03:11 PM
@mdmackillop, can a macro be recorded of running consecutive macros? Basically what I have is 6 or 7 separate macros to massage my data with the last being your concat function. Now I would like to record a macro that when executed, will run each of these 6-7 automatically on my data. Is this even possible?

mdmackillop
11-30-2011, 04:44 PM
Sub RunAllMacros()
Call Macro1
Call Macro2
Call Macro3
'etc
End Sub