INDEX MATCH formula help with duplicates

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
157
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I've tried making my own formula, then I tried the Internet formulas, but I can't get everything straightened out.
Workbook: Doc v3.xlsx

The relevant information is in yellow or orange colored cells, and most of the workbook has been trimmed down for sensitivity reasons. Given the ISBN for a book, I need to determine how many of each book a particular business has purchased. The ISBNs duplicate frequently.

Currently I have a helper column with the following formula: ='MS - E'!F3+ROWS($1:1)*10^-4
It uses the ISBNs as a reference from the second tab.

My primary formula is: =IFERROR(IF(LEFT(INDEX('MS - E'!$D$3:$D$2923,MATCH(LARGE($A$3:$A$2923,ROWS($1:1)),$A$3:$A$2923,0)),10)=LEFT($D5,10),SUM(INDIRECT("H" & MATCH(LARGE($A$3:$A$2923,ROWS($1:1)),$A$3:$A$2923,0) & ":L" & MATCH(LARGE($A$3:$A$2923,ROWS($1:1)),$A$3:$A$2923,0))),""),"")
It uses the business names on the second tabs in the INDEX formula, then uses MATCH on the data in the helper column. The problem is, it seems to be pretty sporadic in which business it chooses with the LARGE formula. If it matches the business name on the first tab, then the SUM & INDIRECT formulas work together to sum up the number of books purchases, but there is a problem there as well. The two MATCHES in the SUM formula match the same row, but I need them to cover a range, if possible. I think that part might work if I change the second MATCH to use a SMALL formula instead, but I still have to get the first half of the formula working to consider that issue.

Thanks. The workbook is linked up top as Doc v3.xlsx.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

gravanoc

Board Regular
Joined
Oct 20, 2015
Messages
157
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I ended up using VBA to get the numbers, but even that is running slow. If anyone can help with either a formula or give me an idea of how to optimize the VBA, that would be awesome.

VBA Code:
Sub FindISBN()

    Dim sdSht As Worksheet
    Dim msSht As Worksheet
    
    Dim sdISBN As Range
    Dim sdBoard As Range
    Dim sdCBoard As Range
    Dim msISBN As Range
    Dim msSum As Range
    Dim msBoard As Range

    Dim summation As Long
    
    Set sdSht = Worksheets("Sample Data")
    Set msSht = Sheets("Mathology Sales - English")
    
    Set sdISBN = sdSht.Range("Q2:AN2")
    Set sdBoard = sdSht.Range("E5:E379")
    Set sdCBoard = sdSht.Range("A5:E379")
    Set sdWrite = sdSht.Range("Q5:AN379")
    Set msISBN = msSht.Range("F5:F2925")
    Set msBoard = msSht.Range("D5:D2925")

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    

For k = 0 To 375
    
        For i = 0 To 23
        
            For j = 0 To 2920

                If sdISBN.Cells(1, 1 + i).Value = msISBN.Cells(1 + j, 1).Value Then
                
                         If Left(sdBoard.Cells(k + 1, 1).Value, 5) = Left(msBoard.Cells(j + 1, 1).Value, 5) Then
                        
                            summation = WorksheetFunction.Sum(msSht.Range(msISBN.Cells(1 + j, 3), msISBN.Cells(1 + j, 7)))
                            sdWrite.Cells(k + 1, i + 1).Value = sdWrite.Cells(k + 1, i + 1).Value + summation
                            
                        End If
                    
                End If
                
            Next
            
        Next
        
    Next
                
    

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,113,886
Messages
5,544,868
Members
410,642
Latest member
Launayvolone
Top