Formula Help, CSE & Match with Multiple Criteria

bk1702 · Jul 19, 2012

How do I get the value that matches for each of these?

Desired result in C9 is "Alpha". This is also a CSE formula.

{=IF(AND(INDEX(G3:G5,MATCH(E3&E4&E5&E6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0)),INDEX(G3:G5,MATCH(E3&C4&C5&C6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0))),"How do I show the value that matches for each of these?","false")}

Thanks for any help you can provide!

[TABLE="class: grid, width: 773"]
<TBODY>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]# Cuts[/TD]
[TD]Type 1[/TD]
[TD]Type 2[/TD]
[TD]Type 3[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]CUT 1[/TD]
[TD]CUT 2[/TD]
[TD]CUT 3[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Type 1:[/TD]
[TD]a[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Type 2:[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD]c[/TD]
[TD][/TD]
[TD]Alpha[/TD]
[TD]CUT 3[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Type 3:[/TD]
[TD]e[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta[/TD]
[TD]CUT 3[/TD]
[TD]a[/TD]
[TD]c[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD="colspan: 5"]How do I show the value that matches for each of these?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4></COLGROUP>[/TABLE]

circledchicken · Aug 2, 2012

Hello again,

I still don't understand your larger example in the other post? What answer do you expect in that example? If you have time perhaps post a sample file e.g. on DropBox, with several examples of the larger dataset and the expected outcomes for each example?

circledchicken · Aug 2, 2012

I had a look at your file. Rearranging a couple of the columns, the VBA solution works for me with the two column example.

There are still many questions, including:

I still am confused about why the '# of Cuts' column is needed or how it should be used?
Why the sequence of cuts column is needed and how it should be used?
Do you want the old or new name?
I ignored columns BJ to BR as they look like a repeat of the other data?

I think you need to provide a few more clear examples along with the expected outcomes.

bk1702 · Aug 2, 2012

circledchicken said:
I had a look at your file. Rearranging a couple of the columns, the VBA solution works for me with the two column example.

There are still many questions, including:

I still am confused about why the '# of Cuts' column is needed or how it should be used? Some only have 1 cut or 5 cuts, it's there to identify how many cuts are on each end cut. It is there to help eliminate duplicates, for example if Delta only had one cut applied to it in that column it would say CUT 1, however if that cut was the same as one in Alpha (with CUT 3 in that column) it may or may not come up with the one it really applies to.

Why the sequence of cuts column is needed and how it should be used? It was added as a 'maybe it'll help me come up with a formula that will work' using lots of IF statements... say if it returned 1.3 then tell it to look at 2.3 if the name matches, then look at 3.3 if the name matches, etc... I realize this won't work with the limitation of 7 nested IF statments not to mention messy.

Do you want the old or new name? use 'OldName' for the name of the column as an array, then I can change it easily enough

I ignored columns BJ to BR as they look like a repeat of the other data? this was to get the two cuts working, see note in B19

I think you need to provide a few more clear examples along with the expected outcomes.

The file I sent you has a 'working' formula for up to two cuts. The cut defined was for the name in AU6 & AU7. If you change D6 to "No Cut" from the drop down the answer is #N/A as there is no existing cut for the defined items in C6-C14. If you changed C6:C14 to match the data in AY4:BG4 the End Cut would be EC1.

The challange is getting 3 or more end cuts to work without adding many more rows and columns for each possible combination. Each End Cut can have multiple cuts (1-6) applied in any order. It's the Any Order part that gets tricky. I have it working for 2 cuts by adding two rows and another set of columns. IE: Cut1&Cut2 and Cut2&Cut1, however for three it turns into Cut1&Cut2&Cut3, Cut2&Cut1&Cut3, Cut3&Cut2&Cut1, Cut1&Cut3&Cut2, Cut2&Cut3&Cut1, Cut3&Cut1&Cut2.

If anyone wants a copy of the file I sent please message me your e-mail and I can send it to you. I don't have access to post it online.

circledchicken · Aug 2, 2012

Ok, I *think*

I understand now.

Here is the slightly revised code:

Code:

Option Explicit

Function FullMatch(iRng As Range, oRng As Range, lngCuts As Long) As Variant


' ---------------------------
' Find a name that matches
' the specified number of
' cuts, else show FALSE
' ---------------------------


Dim vA          As Variant
Dim vB          As Variant
Dim vC          As Variant
Dim astrA()     As String
Dim oDictA      As Object
Dim oDictB      As Object
Dim i           As Long
Dim j           As Long




With Application


    vA = .Transpose(iRng.Resize(, lngCuts)) ' the input table (without any row or column headers)
    vB = oRng ' the match table (Col1 - Name, Col 2 - # Cuts, remaining columns should match input row headers)
    vC = oRng.Offset(0, 1).Resize(, oRng.Columns.Count - 1) ' the match table without names
        
    ' build an array with the cuts we want to match
    ReDim astrA(1 To lngCuts)
    For i = 1 To lngCuts
        If lngCuts = 1 Then
            astrA(i) = "Cut " & lngCuts & " " & Join(.Index(vA, 0))
        Else
            astrA(i) = "Cut " & lngCuts & " " & Join(.Index(vA, i))
        End If
    Next i
    
    ' get a unique set of items from the table to match with
    ' and a unique set of names
    Set oDictA = CreateObject("scripting.dictionary")
    Set oDictB = CreateObject("scripting.dictionary")
    On Error Resume Next
        For i = 1 To UBound(vB, 1)
            oDictA.Add Key:=CStr(Join(.Index(vB, i), "|")), Item:=Join(.Index(vC, i))
            oDictB.Add Key:=CStr(vB(i, 1)), Item:=0
        Next i
    On Error GoTo 0
                            
    vA = oDictA.keys
    vB = oDictA.Items
    For i = 1 To lngCuts
        If IsError(.Match(astrA(i), .Index(vB, 0), 0)) Then
            ' if any of the cuts isn't found in the table
            ' then return FALSE
            FullMatch = False
        Else
            ' in the unique list of table names
            ' add one to the name(s) that match
            For j = 0 To UBound(vA)
                If (astrA(i) = oDictA.Item(vA(j))) Then
                    vC = Split(vA(j), "|")(0)
                    oDictB.Item(vC) = oDictB.Item(vC) + 1
                End If
            Next j
        End If
    Next i
        
    ' if there are 'lngCuts' matches - display the result,
    ' else show FALSE
    If .Max(.Index(oDictB.Items, 0))< lngCuts Then
        FullMatch = False
    Else
    FullMatch = .Index(.Index(oDictB.keys, 0), _
                .Match(lngCuts, .Index(oDictB.Items, 0), 0))
    End If
    
End With


End Function

In your sample file in C15, use it like this:
=FullMatch(C6:H14,AU4:BE760,2)

The first two arguments of the formula never change, the last one is the number of cuts - change this to the required number each time.
For simplicity I have moved the New Name and Sequence of Cut columns out of the way so the relevant columns are ordered like this (you must have this layout of the data table for the formula to work):

Excel Workbook

AT

AU

AV

AW

AX

AY

AZ

BA

BB

BC

BD

BE

3

#

Name

# of Cuts

Type

Direction of Cut

LocX

LocY

Cut Depth Start:

Cut Depth End:

A

B

C

Data

Excel 2010It is quite slow to calculate, but I'm sure the code can be improved, the layout can be changed to match what you want etc, but just getting it to work for now would be a good first step.

circledchicken · Aug 2, 2012

You can amend the formula slightly so you don't need to explicitly state the number of cuts each time like this:

=FullMatch(C6:H14,AU4:BE760,COUNTIF(C6:H6,"<>No cut"))

bk1702 · Aug 3, 2012

THANK YOU! Thanks so much for your help! From what I have been able to do so far it seems like it works like a gem!

Yes, it's a little slow I just added a couple 'buttons' to start and stop the calc so when entering data it doesn't keep trying to run the formula.

Thanks again!!!

circledchicken · Aug 3, 2012

bk1702 said:
THANK YOU! Thanks so much for your help! From what I have been able to do so far it seems like it works like a gem!

Yes, it's a little slow I just added a couple 'buttons' to start and stop the calc so when entering data it doesn't keep trying to run the formula.

Thanks again!!!

Your welcome! I'm glad you've got a starting point now. Although still slow, the following small tweak should speed up the code significantly:

Code:

Option Explicit

Function FullMatch(iRng As Range, oRng As Range, lngCuts As Long) As Variant


' ---------------------------
' Find a name that matches
' the specified number of
' cuts, else show FALSE
' ---------------------------


Dim vA          As Variant
Dim vB          As Variant
Dim vC          As Variant
Dim astrA()     As String
Dim oDictA      As Object
Dim oDictB      As Object
Dim i           As Long
Dim j           As Long


With Application


    vA = .Transpose(iRng.Resize(, lngCuts)) ' the input table (without any row or column headers)
    vB = oRng ' the match table (Col1 - Name, Col 2 - # Cuts, remaining columns should match input row headers)
    vC = oRng.Offset(0, 1).Resize(, oRng.Columns.Count - 1) ' the match table without names
        
    ' build an array with the cuts we want to match
    ReDim astrA(1 To lngCuts)
    For i = 1 To lngCuts
        If lngCuts = 1 Then
            astrA(i) = "Cut " & lngCuts & " " & Join(.Index(vA, 0))
        Else
            astrA(i) = "Cut " & lngCuts & " " & Join(.Index(vA, i))
        End If
    Next i
    
    ' get a unique set of items from the table to match with
    ' and a unique set of names
    Set oDictA = CreateObject("scripting.dictionary")
    Set oDictB = CreateObject("scripting.dictionary")
    On Error Resume Next
        For i = 1 To UBound(vB, 1)
[B][COLOR=#800000]            If vB(i, 2) = "Cut " & lngCuts Then[/COLOR][/B]
                oDictA.Add Key:=CStr(Join(.Index(vB, i), "|")), Item:=Join(.Index(vC, i))
                oDictB.Add Key:=CStr(vB(i, 1)), Item:=0
[COLOR=#800000][B]            End If[/B][/COLOR]
        Next i
    On Error GoTo 0
                            
    vA = oDictA.keys
    vB = oDictA.Items
    For i = 1 To lngCuts
        If IsError(.Match(astrA(i), .Index(vB, 0), 0)) Then
            ' if any of the cuts isn't found in the table
            ' then return FALSE
            FullMatch = False
        Else
            ' in the unique list of table names
            ' add one to the name(s) that match
            For j = 0 To UBound(vA)
                If (astrA(i) = oDictA.Item(vA(j))) Then
                    vC = Split(vA(j), "|")(0)
                    oDictB.Item(vC) = oDictB.Item(vC) + 1
                End If
            Next j
        End If
    Next i
        
    ' if there are 'lngCuts' matches - display the result,
    ' else show FALSE
    If .Max(.Index(oDictB.Items, 0)) < lngCuts Then
        FullMatch = False
    Else
    FullMatch = .Index(.Index(oDictB.keys, 0), _
                .Match(lngCuts, .Index(oDictB.Items, 0), 0))
    End If
    
End With


End Function

bk1702 · Aug 3, 2012

It's amazing how much of a difference those two lines made!

Thanks!!!

Formula Help, CSE & Match with Multiple Criteria

bk1702

New Member

circledchicken

Well-known Member

Excel Facts

circledchicken

Well-known Member

bk1702

New Member

circledchicken

Well-known Member

circledchicken

Well-known Member

bk1702

New Member

circledchicken

Well-known Member

bk1702

New Member

Similar threads

Forum statistics

Share this page

Formula Help, CSE & Match with Multiple Criteria

New Member

Well-known Member

Excel Facts

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock