Columns, All Data In

[Bill Oertell]

I have a spreadsheet with X, Y & Z in column A, rows 1, 2 & 3 on Sheet1. IOW,
A1 has the value X, A2 the value Y, and A3 the value Z. On Sheet2 I have
columns labeled X, Y & Z. In each of those columns I have a list of entries,
the quantity of which varies in each column, so the column labeled X on Sheet2
might have 3 rows of data, row Y might have 7, etc. I can I get into column B
on Sheet1, all the entries corresponding to that column heading on Sheet2? IOW,
if A1 is Z on Sheet1 and column A on Sheet2 with the heading Z has data: This,
That, and Theotherthing, how can I get This, That, and TheOtherThing into cell
B1 on Sheet1?

 

[Dave Peterson]

I'd use a UserDefinedFunction that just concatenates the text in the cells.

Option Explicit
Function CombineCells(rng As Range) As String

Dim myStr As String
Dim myCell As Range

myStr = ""
For Each myCell In rng.Cells
If Len(myCell.Text) = 0 Then
'do nothing
Else
myStr = myStr & ", " & Trim(myCell.Text)
End If
Next myCell

If Len(myStr) = 0 Then
'do nothing
Else
myStr = Mid(myStr, 3)
End If

CombineCells = myStr

End Function

And you'd use it in your worksheet like:

=combinecells(sheet2!a2:a7)


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

=======

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Type =combinecells(sheet2!a2:a7)

 

[Bill Oertell]

Thanks, Dave. I was thinking along the lines of a user defined function but
wasn't sure how to do it. But doesn't the function need to know what value to
lookup? IOW, if A3 on sheet1 has the value "X" and in B3 I want all the entries
under the heading "X" on sheet2, doesn't the function need to know that it needs
to find the column with the "X" heading on sheet2?

 

[Dave Peterson]

Oh. I thought you were going to give it the correct range.


Option Explicit
Function CombineCells(rng As Range, myMatchStr As Variant) As Variant

Dim myStr As String
Dim myCell As Range
Dim res As Variant

res = Application.Match(myMatchStr, rng.Resize(1), 0)

If IsError(res) Then
CombineCells = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For Each myCell In rng.Offset(1, 0) _
.Resize(rng.Rows.Count - 1).Columns(res).Cells
If Len(myCell.Text) = 0 Then
'do nothing
Else
myStr = myStr & ", " & Trim(myCell.Text)
End If
Next myCell

If Len(myStr) = 0 Then
'do nothing
Else
myStr = Mid(myStr, 3)
End If

CombineCells = myStr

End Function

But now you pass it the range and the value to match on:
=combinecells(Sheet2!A199,G1)
or
=combinecells(Sheet2!A199,"x")

 

[Bill Oertell]

Thanks, Dave. I think that's what I was looking for. I'll give it a try
tomorrow. (The spreadsheet's at work, and I'm at home). Can you think of any
way of getting a carriage return and/or line feed between entries? I tried
adding Chr(10) and Chr(13), but that didn't work. Also tried Chr(11).

FWIW, I used to dabble in the old Microsoft BASIC back in the 80's, but I've
forgotten a lot of what I learned back then. This VB thing is really
interesting.

 

[Dave Peterson]

Chr(10) will work in VBA
(and you could play around with: vbLF or vbCr or vbCRLF or vbNewLine)

But I bet you want char(10) in your formula:
(and remember to set wrap text on)

=a1&char(10)&b1

will do the same as alt-enter manually.

(It'll look like a funny square if you don't have wraptext enabled, though.)

Thanks, Dave. I think that's what I was looking for. I'll give it a try
tomorrow. (The spreadsheet's at work, and I'm at home). Can you think of any
way of getting a carriage return and/or line feed between entries? I tried
adding Chr(10) and Chr(13), but that didn't work. Also tried Chr(11).

FWIW, I used to dabble in the old Microsoft BASIC back in the 80's, but I've
forgotten a lot of what I learned back then. This VB thing is really
interesting.