INDEX/MATCH & SUMIF (with one and/or two comma separated reference numbers)

M

Morey

New Member
Joined
Sep 24, 2018
Messages
37
Hello All,

The following formulas work with a single reference number in a cell. The issue is that some cells contain two reference numbers separated by a comma (example: 123, 345). How can I modify formulas to account for one and/or both reference numbers?. Any help would be greatly appreciated.

INDEX/MATCH:

Code: 
=IFERROR(INDEX('Report'!C:C,MATCH(N31+0,'Report'!F:F,0))&"", "Pending")

SUMIF:

Code: 
=SUMIFS('Report'!I:I,'Report'!C:C,P31)

Trying to avoid using something like:

Code: 
=SUMIF('Report'!C:C,"123",'Report'!H:H)+SUMIF('Report'!C:C,"345",'Report'!H:H)



Regards,

Morey
 
Sort by date Sort by votes
Dante,

This works great thank you. Quick question, why does renaming a module (example: Module1) break the UDF code?


Regards,

Morey

DanteAmor said:
TRy this

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
                Exit For
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function
Click to expand...
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Dante,

UDF only returns two values. What if, there are more than two return values? Some return values, can up to 10 values.

DanteAmor said:
TRy this

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
                Exit For
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function
Click to expand...


Regards,

Morey
 
Last edited:
Upvote 0
Morey said:
Dante,

This works great thank you. Quick question, why does renaming a module (example: Module1) break the UDF code?
Regards,

Morey
Click to expand...

I did not understand the question very well.
But changing the name of the module does not affect the "return_prr" UDF
 
Upvote 0
Morey said:
Dante,

UDF only returns two values. What if, there are more than two return values? Some return values, can up to 10 values.
Regards,

Morey
Click to expand...

The UDF returns one or more values, only to update some value you must press F9 or change the following function to be automatic:

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
[COLOR=#0000ff]    Application.Volatile[/COLOR]
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
                Exit For
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function
 
Upvote 0
Dante,

Added Application.Volatile to the UDF Code but, only two values are still returned.

DanteAmor said:
The UDF returns one or more values, only to update some value you must press F9 or change the following function to be automatic:

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
[COLOR=#0000ff]    Application.Volatile[/COLOR]
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
                Exit For
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function
Click to expand...


Regards,


Morey
 
Upvote 0
Morey said:
Dante,
Added Application.Volatile to the UDF Code but, only two values are still returned.
Regards,
Morey
Click to expand...

It works for me, these are my test data

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:157.78px;" /><col style="width:153.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >PR#</td><td >PRR</td></tr><tr style="height:56px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >20106310, 20110661, 20110650, 20110688, 20110699</td><td >770, 1652, 555, 888, 99</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#fafafa; color:#333333; font-family:Verdana; font-size:9pt; text-align:left; ">20106310</td><td style="text-align:right; ">770</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">123456</td><td >No data</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B2</td><td >=return_prr(A2)</td></tr><tr><td >B3</td><td >=return_prr(A3)</td></tr><tr><td >B4</td><td >=return_prr(A4)</td></tr></table></td></tr></table>
-----------

<b>Data</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >PRR</td><td >PR #</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">770</td><td style="text-align:right; ">20106310</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1652</td><td style="text-align:right; ">20110661</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">555</td><td style="text-align:right; ">20110650</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">888</td><td style="text-align:right; ">20110688</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">99</td><td style="text-align:right; ">20110699</td></tr></table>
 
Upvote 0
DanteAmor said:
It works for me, these are my test data

Sheet1

AB
PR#PRR
20106310, 20110661, 20110650, 20110688, 20110699770, 1652, 555, 888, 99
No data

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fafafa]#fafafa[/URL] , align: left"]20106310[/TD]
[TD="align: right"]770[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]123456[/TD]

</tbody>

Formeln der Tabelle
ZelleFormel
B2=return_prr(A2)
B3=return_prr(A3)
B4=return_prr(A4)

<tbody>
</tbody>

<tbody>
</tbody>

-----------

Data

AB
PRRPR #

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]770[/TD]
[TD="align: right"]20106310[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]1652[/TD]
[TD="align: right"]20110661[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]555[/TD]
[TD="align: right"]20110650[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]888[/TD]
[TD="align: right"]20110688[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]20110699[/TD]

</tbody>
Click to expand...


Dante,

Per the example below, it seems like the UDF Code will not return more than one value per PR#. The PRR return values should include the missing 20, 20. Could the UDF Code be modified to suit?


PRR Return:

[TABLE="width: 0"]
<tbody>[TR]
[TD]PR#[/TD]
[TD]PRR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20106310, 20110661, 20110699, 01C18F1393, 01C19F0152[/TD]
[TD]770, 1652, 99, 10, 10[/TD]
[TD]<---- Missing 20, 20[/TD]
[/TR]
[TR]
[TD]20106310[/TD]
[TD]770[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data:

[TABLE="width: 0"]
<tbody>[TR]
[TD]PRR[/TD]
[TD]PR#[/TD]
[/TR]
[TR]
[TD]770[/TD]
[TD]20106310[/TD]
[/TR]
[TR]
[TD]1652[/TD]
[TD]20110661[/TD]
[/TR]
[TR]
[TD]770[/TD]
[TD]20106310[/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD]20110650[/TD]
[/TR]
[TR]
[TD]888[/TD]
[TD]20110688[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]20110699[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01C18F1393[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]01C18F1393[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01C19F0152[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]01C19F0152[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Morey said:
Dante,

Per the example below, it seems like the UDF Code will not return more than one value per PR#. The PRR return values should include the missing 20, 20. Could the UDF Code be modified to suit?


PRR Return:

[TABLE="width: 0"]
<tbody>[TR]
[TD]PR#[/TD]
[TD]PRR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20106310, 20110661, 20110699, 01C18F1393, 01C19F0152[/TD]
[TD]770, 1652, 99, 10, 10[/TD]
[TD]<---- Missing 20, 20[/TD]
[/TR]
[TR]
[TD]20106310[/TD]
[TD]770[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Data:

[TABLE="width: 0"]
<tbody>[TR]
[TD]PRR[/TD]
[TD]PR#[/TD]
[/TR]
[TR]
[TD]770[/TD]
[TD]20106310[/TD]
[/TR]
[TR]
[TD]1652[/TD]
[TD]20110661[/TD]
[/TR]
[TR]
[TD]770[/TD]
[TD]20106310[/TD]
[/TR]
[TR]
[TD]555[/TD]
[TD]20110650[/TD]
[/TR]
[TR]
[TD]888[/TD]
[TD]20110688[/TD]
[/TR]
[TR]
[TD]99[/TD]
[TD]20110699[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01C18F1393[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]01C18F1393[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]01C19F0152[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]01C19F0152[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

The problem is that from the beginning, you did not comment on how your data is, how much data you are going to have per cell, if you are going to have repeated data, if you are going to have the filtered sheet.
And it's okay maybe you did not know how you're going to work with your data, but if you do not have that knowledge, definitely not us either.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

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

Try the following improved code.

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
    Application.Volatile
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    cad = ""
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(WorksheetFunction.Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function

---------------------------------------------
The result is:

770, 770, 1652, 99, 10, 20, 10, 20

Because in the data sheet you have 2 times the 770 with the 20106310
 
Upvote 0
DanteAmor said:
The problem is that from the beginning, you did not comment on how your data is, how much data you are going to have per cell, if you are going to have repeated data, if you are going to have the filtered sheet.
And it's okay maybe you did not know how you're going to work with your data, but if you do not have that knowledge, definitely not us either.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

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

Try the following improved code.

Code: 
Function return_prr(r As Range)
    Dim c As Variant, sh As Worksheet, lr As Long
    Application.Volatile
    Set sh = Sheets("Data")
    lr = sh.UsedRange.Rows(sh.UsedRange.Rows.Count).Row
    cad = ""
    For Each c In Split(r, ",")
        For i = 1 To lr
            If LCase(sh.Cells(i, "B").Value) = LCase(WorksheetFunction.Trim(c)) Then
                cad = cad & sh.Cells(i, "A").Value & ", "
            End If
        Next
    Next
    If cad = "" Then
        return_prr = "No data"
    Else
        return_prr = Left(cad, Len(cad) - 2)
    End If
End Function

---------------------------------------------
The result is:

770, 770, 1652, 99, 10, 20, 10, 20

Because in the data sheet you have 2 times the 770 with the 20106310
Click to expand...


Dante,

This UDF edit did the trick. You are correct, some of the Data Issues came to light after the fact. Regardless, I will make it a point to be a bit more descriptive in the future. Now, to use some of the SUMIF advice that was given earlier in this thread. Much appreciated...


Morey
 
Upvote 0
Morey said:
Dante,

This UDF edit did the trick. You are correct, some of the Data Issues came to light after the fact. Regardless, I will make it a point to be a bit more descriptive in the future. Now, to use some of the SUMIF advice that was given earlier in this thread. Much appreciated...


Morey
Click to expand...


I'm glad to help you. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

youbitto
  • Solved
Index match VBA taking too long
2 3
Replies
27
Views
557
Alex Blakenburg
A
U
  • Solved
Index or SumIf on here..
Replies
9
Views
299
Cubist
Cubist
M
  • Question Question
Comparing two columns with comma separated string values (match) and output: matching string/text
Replies
6
Views
440
Fluff
Fluff
K
  • Question Question
Cell Formatting When Using Index Match
Replies
4
Views
2K
kxellis26
K
U
  • Solved
Index, SumIf?, Matching
Replies
3
Views
185
smozgur
smozgur

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top