Forum Discussion
Must Go Faster
I work in Sales. Every week I have to create an availability for our customers based off of our Availability. Our availability has multiple duplicates. I know how to highlight the duplicates. What I would like to do is run a formula for selecting 1 of the duplicates based on criteria from a different column. For Example A387-A392 are all 1gal Boxwood. I have to manually compare each row by column so that I only have 1 row left. Manually I would select Row 390 based on rooting, size, counted, and date last. I have tried IFAND, COUNTIFS, but I keep coming up with errors. Any help is appreciated.
2 Replies
So here is an option but I had to assume some stuff.
a) roots is all text (i.e. 100% is not a value of 1 but is "100%") and that 75-100% and average of 75 & 100 is good for the ranking and you want that max root value as 1st priority
b) counted is 2nd priority and you want larger number (i.e. count of 2290 and 100% root is preferred over count of 7000 but root of 75-100%)
c) Last Ver date you want 3rd priority and bigger is better?
Here is the formula i used:
=LET(s, SORTBY(HSTACK(ROW(Table1[item]),Table1[item]),BYROW(Table1[roots],LAMBDA(r,AVERAGE(--TEXTSPLIT(r,{"-","%"},,1)))),-1,Table1[counted],-1,Table1[last ver],-1), XLOOKUP(B19,TAKE(s,,-1),TAKE(s,,1),"n/a",0,1))basically it is a fancy SORT of the whole table based on the 3 criteria above then find the first occurence of the chosen item.
if the table is very large, it might be more efficient to filter first and then do the sort.
and this will just give you the row # in excel where to find it
note I added some dummy data to your set to show it works
Disabling hardware graphics acceleration and animation effects in Excel Options→Advanced will improve responsiveness.
