Column A contains Postcodes numbers, Column B contains Suburbs
I would like a formula where Column C contain combined list of of suburbs in
a particular Post code separted by comma
for example row1- PC 4034 suburb Geebung,
Row2- 4034 Aspley,Row3 - 4034 Zillmere

Answer required - Post code 4034 - Geebung, Aspley,Zillmere

need ur help

thanks

varun

The bad news is that with so many separate threads, it's difficult to know
whether you found a solution.

And lots of people just won't take the time if they think it's gonna be a waste
of their time. If I spend time answering your question in one thread, then find
that you have other answers elsewhere, I don't like it. I imagine others feel
the same way.

Saved from a previous post:

How about a UserDefined Function?

Option Explicit
Function mvlookup2(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant

Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
mvlookup2 = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
mvlookup2 = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i

mvlookup2 = Mid(myStr, 3)

End Function

It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.

Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).

Then type in your formula:

=mvlookup2(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup2 = multiple Vlookup)
(2 because this one is different from my original. You can change it (all
spots) if you want to.

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
there.

Then close the VBE and back to excel to test it out.



varun wrote:

Column A contains Postcodes numbers, Column B contains Suburbs
I would like a formula where Column C contain combined list of of suburbs in
a particular Post code separted by comma
for example row1- PC 4034 suburb Geebung,
Row2- 4034 Aspley,Row3 - 4034 Zillmere

Answer required - Post code 4034 - Geebung, Aspley,Zillmere

need ur help

thanks

varun

--

Dave Peterson

Thank you Dave but it has not helped me

Can you please let me know if I can do it in excel using any formula ( have
no idea about VBa or macro)
sorry for creating any confusion

pl help

need to combine (merge cell & delete in column A & combined the suburbs in
Coumn b in a same roe

Thanks

varun



"Dave Peterson" wrote:

The bad news is that with so many separate threads, it's difficult to know
whether you found a solution.

And lots of people just won't take the time if they think it's gonna be a waste
of their time. If I spend time answering your question in one thread, then find
that you have other answers elsewhere, I don't like it. I imagine others feel
the same way.

Saved from a previous post:

How about a UserDefined Function?

Option Explicit
Function mvlookup2(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant

Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
mvlookup2 = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
mvlookup2 = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i

mvlookup2 = Mid(myStr, 3)

End Function

It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.

Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).

Then type in your formula:

=mvlookup2(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup2 = multiple Vlookup)
(2 because this one is different from my original. You can change it (all
spots) if you want to.

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
there.

Then close the VBE and back to excel to test it out.



varun wrote:

Column A contains Postcodes numbers, Column B contains Suburbs
I would like a formula where Column C contain combined list of of suburbs in
a particular Post code separted by comma
for example row1- PC 4034 suburb Geebung,
Row2- 4034 Aspley,Row3 - 4034 Zillmere

Answer required - Post code 4034 - Geebung, Aspley,Zillmere

need ur help

thanks

varun

--

Dave Peterson

Take a look at that link to David McRitchie's site and read those "short course"
instructions.

After you try that, post back with your results.

varun wrote:

Thank you Dave but it has not helped me

Can you please let me know if I can do it in excel using any formula ( have
no idea about VBa or macro)
sorry for creating any confusion

pl help

need to combine (merge cell & delete in column A & combined the suburbs in
Coumn b in a same roe

Thanks

varun

"Dave Peterson" wrote:

The bad news is that with so many separate threads, it's difficult to know
whether you found a solution.

And lots of people just won't take the time if they think it's gonna be a waste
of their time. If I spend time answering your question in one thread, then find
that you have other answers elsewhere, I don't like it. I imagine others feel
the same way.

Saved from a previous post:

How about a UserDefined Function?

Option Explicit
Function mvlookup2(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant

Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
mvlookup2 = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
mvlookup2 = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i

mvlookup2 = Mid(myStr, 3)

End Function

It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.

Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).

Then type in your formula:

=mvlookup2(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup2 = multiple Vlookup)
(2 because this one is different from my original. You can change it (all
spots) if you want to.

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
there.

Then close the VBE and back to excel to test it out.



varun wrote:

Column A contains Postcodes numbers, Column B contains Suburbs
I would like a formula where Column C contain combined list of of suburbs in
a particular Post code separted by comma
for example row1- PC 4034 suburb Geebung,
Row2- 4034 Aspley,Row3 - 4034 Zillmere

Answer required - Post code 4034 - Geebung, Aspley,Zillmere

need ur help

thanks

varun

--

Dave Peterson


--

Dave Peterson