Trouble with a formula returning duplicate values

[MichaelLFC96]

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,

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?

 

[Cubist]

Try the above.

 

[MichaelLFC96]

Try the above.

Hi, I have just tried that and it's returning a #CALC error, it's saying textjoin has too much text?

 

[Cubist]

Post your actual sheet layout. Can't diagnose the issue.

 

[Cubist]

You might have to do it in chunks. Then combine them.

 

[MichaelLFC96]

Post your actual sheet layout. Can't diagnose the issue.

Here is a screenshot, with this table below with formula to give an idea of what's happening

Thanks for your efforts so far, much appreciated

 

[Cubist]

See if this give you an error first. If it doesn't ....

TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",",")

Try this...

=LET(t,TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",","),
br,BYROW(t,LAMBDA(r,INDEX(B4:AA69,MATCH(DROP(r,0,-1),A4:A69,0),MATCH(DROP(r,0,1),B2:AA2,0)))),
TAKE(SORT(HSTACK(t,br),3,-1),5))

If the first formula gives you an error, will have to think about a different arpproach.

 

[MichaelLFC96]

See if this give you an error first. If it doesn't ....

TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",",")

Try this...

=LET(t,TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",","),
br,BYROW(t,LAMBDA(r,INDEX(B4:AA69,MATCH(DROP(r,0,-1),A4:A69,0),MATCH(DROP(r,0,1),B2:AA2,0)))),
TAKE(SORT(HSTACK(t,br),3,-1),5))

If the first formula gives you an error, will have to think about a different arpproach.

See if this give you an error first. If it doesn't ....

TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",",")

Try this...

=LET(t,TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2,3)),"|",","),
br,BYROW(t,LAMBDA(r,INDEX(B4:AA69,MATCH(DROP(r,0,-1),A4:A69,0),MATCH(DROP(r,0,1),B2:AA2,0)))),
TAKE(SORT(HSTACK(t,br),3,-1),5))

If the first formula gives you an error, will have to think about a different arpproach.

I tried both, both are returning #CALC errors

I'm surprised I can't tweak my formula that is returning the result to go to the next duplicate value row and header, it seems like a simple tweak but I can't find anything anywhere

 

[Cubist]

Is it #Calc! error or #Value! error? I don't have the sample that is representative of your data so can't do much. Can you post your sheet into a DropBox?

 

[MichaelLFC96]

Is it #Calc! error or #Value! error? I don't have the sample that is representative of your data so can't do much. Can you post your sheet into a DropBox?

It is a #calc error, but when I click on it it says text too long

Dropbox isn't a viable option for myself so I may have to just keep making amendments to the formula with +/- at the end to pick up the correct data which isn't ideal but I can work with it, thanks for your attempts though it's much apppreciated!

 

[Cubist]

Based on MS documentation, it should give a #VALUE if it's too long. Not sure what the #CALC means as I've never encountered it before.