Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
cchristensen
 
Posts: n/a
Default Can vlookup return multiple matches in a single cell?

I have a list of contract numbers (column A) and associated account numbers
(column B). Some contract numbers are listed more than once because there
may be multiple associated account numbers.

My goal is to have one row/record for each contract number, with all the
associated account numbers in a single cell, comma delimited.

Can Excel do this?
  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

One more option: How about a UserDefinedFunction?

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


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
Dim ubound_myRes 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


cchristensen wrote:

I have a list of contract numbers (column A) and associated account numbers
(column B). Some contract numbers are listed more than once because there
may be multiple associated account numbers.

My goal is to have one row/record for each contract number, with all the
associated account numbers in a single cell, comma delimited.

Can Excel do this?


--

Dave Peterson
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

I copied from a previous post and didn't delete enough!

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

(mvlookup() worked that way--not mvlookup2())


Dave Peterson wrote:

One more option: How about a UserDefinedFunction?

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

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
Dim ubound_myRes 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

cchristensen wrote:

I have a list of contract numbers (column A) and associated account numbers
(column B). Some contract numbers are listed more than once because there
may be multiple associated account numbers.

My goal is to have one row/record for each contract number, with all the
associated account numbers in a single cell, comma delimited.

Can Excel do this?


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM


All times are GMT +1. The time now is 03:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"