Convert column values into a single cell

G

gldweb

I have a worksheet that contains a column of data - almost 2000 rows.
need to combine/concatenate data from multiple rows of the column int
a single cell. I have been using the "&" function - i.e. a2&", "&a3&"
"&a4&", "&a5&", "&etc - to accomplish this. This works fine if I jus
have a few cells to convert but unfortunately I have over one hundre
and it becomes very time consumming, even with copy and paste.

It is not consitent how many rows I may have to extract the data from
In so cases it is only one row other time it can be over 10.

I was thinking of using a macro, but I am not familiar with writin
macros so I have tried using the macro recorder but I am not gettin
the results I expect.

Any help in pinting me in the write direction would be helpful.

thank
 
A

AlfD

Hi!

It would be helpful to see your data structure. How do you decide
whether to concatenate 3 or 4 or 10 items?

Alf
 
G

gldweb

Okay, here is the data structure:


Code
-------------------

OEM Product
----- ---------
C3909A TC_HP_5Si
TC_HP_5Si MOPIER
TC_HP_5Si/MX
TC_HP_5Si/NX
TC_HP_8000
TC_HP_8000 DN
TC_HP_MOPIER 240
1382140 TC_IB_4324
TC_IB_NETWORK PRINTER 24
TC_IB_NETWORK PRINTER 24 PS
140109A TC_LX_OPTRA N240
TC_LX_OPTRA N245
TC_LX_OPTRA N
C3909A TC_HP_5Si
TC_HP_5Si MOPIER
TC_HP_5Si/MX
TC_HP_5Si/NX
TC_HP_8000
TC_HP_8000 DN
TC_HP_MOPIER 240
C3909A TC_HP_5Si


-------------------

The OEM column defines how many of the rows in the Products column
would have to concatenate. For example, for OEM C3909A I woul
concatenate 7 rows from the Product column up to the next populated OE
row. Does that make sense?

Thank
 
G

gldweb

JE McGimpsey's MultiCat macro works great!
:)
It's going to save me a ton of time!

After doing an Autofilter on the "non-blank" values in the OEM column
I copied and pasted the MultiCat macro/function in each of the rows.
still need to manually modify most of the rows in the worksheet t
select the correct rows for the fucntion, but all I need to do is dra
the cell identifier to the correct row. Alot simpler than manull
selecting each cell and entering the ", " between each value.

Thank
 
A

AlfD

Hi!

Another possible approach:

Sub concat()
For i = 1 To 21 'or however many rows you have
Count = 1
Range("C" & i) = Range("B" & i)
While Range("A" & i + Count) <> ""
Range("C" & i) = Range("C" & i) & " " & Range("B" & i + Count)
Count = Count + 1
Wend
i = i + Count
Next i
End Sub

No refinements

Al
 
G

gldweb

Just a little follow-up.

Using JE McGimpsey's MultiCat macro I just realized that it inserts a
leading space in front of the values it creates. Any chance of
modifiying this macro to eliminate the leading space?

Thanks
 
D

Dave Peterson

It sounds like you're using a space for the delimiter and you have an empty cell
in the first spot.

You could add this right before the end:
MultiCat = Application.Trim(Mid(MultiCat, Len(sDelim) + 1))
that removes leading/trailing/multiple internal spaces--including the spaces in
the cells in the range.

If you want to avoid the empty cells, maybe something like:

Public Function MultiCat( _
ByRef rRng As Excel.Range, _
Optional ByVal sDelim As String = "") _
As String
Dim rCell As Range
For Each rCell In rRng
If IsEmpty(rCell) Then
'do nothing
Else
MultiCat = MultiCat & sDelim & rCell.Text
End If
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function



You may want to
 
G

gldweb

I actually figured out that I can surround the MultiCat macro/functio
with a trim() function which would remove the leading space

i.e. TRIM(MultiCat(N123:N129, ", ")
 
Top