MichaelLFC96
New Member
- Joined
- Apr 10, 2024
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
Hi all, first time poster, bit of an excel novice but I can manipulate and adapt formulas to fit
I have a scrap spreadsheet that contains values in cells B4:AA69, with parts listed in columns A2:A69, and reasons for scrapping in rows B4:B69.
I have made a table below that would return the top 5 parts that have resulted in the highest amounts of scrap,
Now as you can see it's resulting in duplicate values producing the same results, whereas I would like it to pick up the next instance of 10 in the spreadsheet, the formula for part column is =INDEX($A$4:$A$69,SUMPRODUCT(MAX(($B$4:$AA$69=B104)*(ROW($A$4:$A$69))))-ROW(A4)+1), and the formula for the result column is =INDEX($B$2:$AA$2,SUMPRODUCT(MAX(($B$4:$AA$69=B104)*(COLUMN($B$4:$AA$69))))-COLUMN($B$2)+1).
If the data in B4:AA69 has sequential numbers i.e 10,9,8,7 and 6 in the lookup value column, it works perfectly fine, but I have racked my brains to try and adjust it to return different results for the same value but to no avail, could anybody point me in the right direction?
I have a scrap spreadsheet that contains values in cells B4:AA69, with parts listed in columns A2:A69, and reasons for scrapping in rows B4:B69.
I have made a table below that would return the top 5 parts that have resulted in the highest amounts of scrap,
Top 5 by Part/Fault | ||
Part | Lookup Value | Result |
P33B Dash outer RHD E power | 10.00 | Torn / Incomplete Trim |
P33B Dash outer RHD E power | 10 | Torn / Incomplete Trim |
P33B Dash outer RHD E power | 7 | Raw material blank unprocessed |
P33B Dash outer RHD E power | 7 | Raw material blank unprocessed |
P33B Boot insulator HT | 6 | Processed blank |
Now as you can see it's resulting in duplicate values producing the same results, whereas I would like it to pick up the next instance of 10 in the spreadsheet, the formula for part column is =INDEX($A$4:$A$69,SUMPRODUCT(MAX(($B$4:$AA$69=B104)*(ROW($A$4:$A$69))))-ROW(A4)+1), and the formula for the result column is =INDEX($B$2:$AA$2,SUMPRODUCT(MAX(($B$4:$AA$69=B104)*(COLUMN($B$4:$AA$69))))-COLUMN($B$2)+1).
If the data in B4:AA69 has sequential numbers i.e 10,9,8,7 and 6 in the lookup value column, it works perfectly fine, but I have racked my brains to try and adjust it to return different results for the same value but to no avail, could anybody point me in the right direction?