gravanoc
Board Regular
 Joined
 Oct 20, 2015
 Messages
 157
 Office Version

 365
 Platform

 Windows
 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.
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.