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]

Can you post the sample data using XL2BB?

 

[SanjayGMusafir]

Welcome to the forum

Though the whole thing is not so clear and @Cubist said it right to share sample data using XL2BB.

But to the extent I have understood, there has to be a better way of addressing your needs being Excel 365 in your hand

Look at this thread it can be of some use to you to filter your data

FILTER on top n values

To filter a set of data to show the top n values you can use the FILTER function together with the LARGE function. In the example shown, the formula in F5 is: =FILTER(data,score>=LARGE(score,3)) where data (B5:D16) and score (D5:D16) are named ranges.

exceljet.net

 

[MichaelLFC96]

Hi all,

I guess the issue I have is that I can't return the nth amount of a number, as you can see in the table, it's returning the value of 7 twice, which is correct, but the data it is displaying isn't matching with the second pick up of data from the field, only the first.

For example, it is picking up the number 7 from cell B5 and also the number 7 from cell C5, but it is returning data from the corresponding row and column for B5 for both, instead of recognising the duplicate value is coming from a different cell.

I am on a work system so unable to download any extra packages!

 

[SanjayGMusafir]

For example, it is picking up the number 7 from cell B5 and also the number 7 from cell C5, but it is returning data from the corresponding row and column for B5 for both, instead of recognising the duplicate value is coming from a different cell.

@MichaelLFC96 I can only drop you an idea under current circumstances...

XLOOKUP is the solution for you

1. XLOOKUP1 - to lookup the Max value and return part/result Searching Top to Bottom
2. XLOOKUP2 - to lookup the Max Value and return part/result Searching Bottom to Top
3. Assuming Lookup value(s) start from cell B3

Formula Format being -

=Let(a, XLOOKUP1, b, XLOOKUP2, Ifs(B3=B2, b, true, a))

• This way it shall check if the value is same as before, it shall return results from Bottom to top
• Only challenge shall be if there are 3 Same Max Values - Until 2 it shall work absolutely fine.

Hope it helps you in some useful way.

 

[MichaelLFC96]

@MichaelLFC96 I can only drop you an idea under current circumstances...

XLOOKUP is the solution for you

1. XLOOKUP1 - to lookup the Max value and return part/result Searching Top to Bottom
2. XLOOKUP2 - to lookup the Max Value and return part/result Searching Bottom to Top
3. Assuming Lookup value(s) start from cell B3

Formula Format being -

=Let(a, XLOOKUP1, b, XLOOKUP2, Ifs(B3=B2, b, true, a))

• This way it shall check if the value is same as before, it shall return results from Bottom to top
• Only challenge shall be if there are 3 Same Max Values - Until 2 it shall work absolutely fine.

Hope it helps you in some useful way.

I have tried to download XL2BB and have encountered some issues, is there a way I can share the sheet with you to gain a better understanding? It is really difficult to explain and when I paste the table has number 0's where its blank, I'll try and create a mini example for you, here goes....

Apr-24	Short mould (not foam)	Heelmat / Footrest OOP	Supplier liability
P33B Floor carpet RHD ICE (FC3)	60.72
P33B Floor carpet LHD ICE (FC3)	91.08
P33B Floor carpet LHD E power (FC2)		42.44	31.86
P33B Floor carpet RHD E Power (FC2)		42.44
P33B Floor carpet Rear E power (FC2)	48.25		48.25

So if we class the above table as A1:D6 as a whole, with data in B2:D6, and columns in A2:A6, rows B1:D1. I have another table that returns the top 5 results, what row header it falls under and what column header it falls under, as below.

Top 5 by Part/Fault
Part	Lookup Value	Result
P33B Floor carpet LHD ICE (FC3)	91.08	Short mould (not foam)
P33B Floor carpet RHD ICE (FC3)	60.72	Short mould (not foam)
P33B Floor carpet Rear E power (FC2)	48.25	Supplier liability
P33B Floor carpet Rear E power (FC2)	48.25	Supplier liability
P33B Floor carpet LHD E power (FC2)	42.48	Heelmat / Footrest OOP

The formula for lookup value is a simple =LARGE(B2:D6,1) through to 5.

Part formula is =INDEX($A$2:$A$6,SUMPRODUCT(MAX(($B$2:$D$6=B90)*(ROW($A$2:$A$6))))-ROW(A2)+1) - B90 is the number one highest lookup value, to return me that figure's corresponding row


Result formula is =INDEX($B$2:$B$4,SUMPRODUCT(MAX(($B$2:$D$6=B90)*(COLUMN($B$2:$B$4))))-COLUMN(B2)+1), which would return me the highest corresponding result for it being the largest number.

As you can see from the table, values 3 and 4 are duplicated, but the data in C4 & C5 are the same result but in different cells, so ideally my formula should bring me back the corresponding row and column headers in A4:C1 and A5:C1 respectively. However, it is not returning this value, and is only returning the first instance. The formula is correct, they are the 3rd and 4th highest values, but it is not recognising the different headers and columns.

I recognise I may have over complicated this but I've tried to put it forward in a way that you may be able to recreate it quickly and tweak the formulas to help

Please do let me know if there is anymore info required and I'll do my best to help! Thanks guys!

 

[Cubist]

Try this. Note the headers need to be unmerged cells to work.

Book3
	A	B	C	D
1	4/24/24
2		Short mould	Heelmat / Footrest OOP	Supplier liability
3	P33B Floor carpet RHD ICE (FC3)	60.72
4	P33B Floor carpet LHD ICE (FC3)	91.08
5	P33B Floor carpet LHD E power (FC2)		42.44	31.86
6	P33B Floor carpet RHD E Power (FC2)		42.44
7	P33B Floor carpet Rear E power (FC2)	48.25		48.25
8
9	P33B Floor carpet LHD ICE (FC3)	Short mould	91.08
10	P33B Floor carpet RHD ICE (FC3)	Short mould	60.72
11	P33B Floor carpet Rear E power (FC2)	Short mould	48.25
12	P33B Floor carpet Rear E power (FC2)	Supplier liability	48.25
13	P33B Floor carpet LHD E power (FC2)	Heelmat / Footrest OOP	42.44
Sheet5

Cell Formulas
Range	Formula
A9:C13	A9	=TAKE(SORT(TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A3:A7 & "|" & B2:D2 & "|" & B3:D7,3)),"|",","),3,-1),5)
Dynamic array formulas.

 

[MichaelLFC96]

Try this. Note the headers need to be unmerged cells to work.

Book3
	A	B	C	D
1	4/24/24
2		Short mould	Heelmat / Footrest OOP	Supplier liability
3	P33B Floor carpet RHD ICE (FC3)	60.72
4	P33B Floor carpet LHD ICE (FC3)	91.08
5	P33B Floor carpet LHD E power (FC2)		42.44	31.86
6	P33B Floor carpet RHD E Power (FC2)		42.44
7	P33B Floor carpet Rear E power (FC2)	48.25		48.25
8
9	P33B Floor carpet LHD ICE (FC3)	Short mould	91.08
10	P33B Floor carpet RHD ICE (FC3)	Short mould	60.72
11	P33B Floor carpet Rear E power (FC2)	Short mould	48.25
12	P33B Floor carpet Rear E power (FC2)	Supplier liability	48.25
13	P33B Floor carpet LHD E power (FC2)	Heelmat / Footrest OOP	42.44
Sheet5

Cell Formulas
Range	Formula
A9:C13	A9	=TAKE(SORT(TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A3:A7 & "|" & B2:D2 & "|" & B3:D7,3)),"|",","),3,-1),5)
Dynamic array formulas.

Hi, thanks for the response. When I am pasting it into my actual spreadsheet it is only returning 2 values, how could it be ammended to larger columns/data sheet?

The columns are A4:AA69, B2:AA2, and the index is B4:AA69, thanks in advance it means a lot!

 

[Cubist]

What happened to row 3? B4:AA69

=TAKE(SORT(TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2 & "|" & B4:AA69,3)),"|",","),3,-1),5)

 

[MichaelLFC96]

What happened to row 3? B4:AA69

=TAKE(SORT(TEXTSPLIT(TEXTJOIN(",",TRUE,TOCOL(A4:A69 & "|" & B2:AA2 & "|" & B4:AA69,3)),"|",","),3,-1),5)

Row 3 is a code/key we use to differentiate/breakdown each scrap issue through SAP