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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
1PR#PRR
220106310, 20110661, 20110650, 20110688, 20110699770, 1652, 555, 888, 99
320106310770
4123456No data

<tbody>
</tbody>

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

<tbody>
</tbody>

<tbody>
</tbody>

-----------

Data

AB
1PRRPR #
277020106310
3165220110661
455520110650
588820110688
69920110699

<tbody>
</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:

PR#PRR
20106310, 20110661, 20110699, 01C18F1393, 01C19F0152770, 1652, 99, 10, 10<---- Missing 20, 20
20106310770

<tbody>
</tbody>

Data:

PRRPR#
77020106310
165220110661
77020106310
55520110650
88820110688
9920110699
1001C18F1393
2001C18F1393
1001C19F0152
2001C19F0152

<tbody>
</tbody>
 
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:

PR#PRR
20106310, 20110661, 20110699, 01C18F1393, 01C19F0152770, 1652, 99, 10, 10<---- Missing 20, 20
20106310770

<tbody>
</tbody>

Data:

PRRPR#
77020106310
165220110661
77020106310
55520110650
88820110688
9920110699
1001C18F1393
2001C18F1393
1001C19F0152
2001C19F0152

<tbody>
</tbody>
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

K
  • Question
Cell Formatting When Using Index Match
Replies
4
Views
615
kxellis26
K
R
  • Question
Index-Match VBA Code where result requires two matches in two different columns from the same row
Replies
4
Views
370
rokgod7
R
S
  • Solved
Sum index match multiple row multiple column - referring to an old post link inside
Replies
4
Views
1K
Alex Blakenburg
A
N
  • Solved
Problem populating table based off two criteria from a different table
Replies
1
Views
657
Nade85
N
R
  • Question
lookup/index/match with two reference cells!!
Replies
8
Views
300
Fluff
Fluff

Forum statistics

Threads
1,214,668
Messages
6,120,825
Members
448,990
Latest member
rohitsomani

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