Thank you all Samir Shaker Biplab Das ❤️❤️❤️❤️❤️❤️

Ann Ann Start at D2 =AVERAGE(OFFSET($A$1,8*(ROWS(D$2:D2)-1),0,8)) Copy down

Set up a Lookup Table with All Trucks and Matching Plates in 2 Separate Columns . Use: . ✧ INDEX and MATCH ✧ VLOOKUP ✧ XLOOKUP

. ✧ INDEX and MATCH . ✧ VLOOKUP . ✧ XLOOKUP (not working with older Excel versions) https://exceljet.net/articles/index-and-match https://exceljet.net/functions/vlookup-function https://exceljet.net/functions/xlookup-function

Công thức công khai cho các bạn đây (một số người cứ lợi dụng người khác gặp vấn đề là inbox gạ... hay trục lợi gì đó) =INDEX($A$1:$C$3,MOD(ROW(1:1)-1,ROWS($A$1:$C$3))+1,INT((ROW(1:1)-1)/COLUMNS($A$1:$C$3))+1)

I2=IFERROR(SMALL(D:D,1+SUMPRODUCT(ISNUMBER(MATCH($D$2:$D$20,$I$1:I1,))*1)),"") J2=IF(OR($I2="",COUNTIF($D:$D,$I2)<COLUMN(A1)),"",OFFSET($E$1,MATCH($I2,$D$2:$D$300,)+COLUMN(A1)-1,))

Yes, strings that contain only numbers and exceed 15 significant digits will cause a problem due to the fact that the sumif(s) and countif(s) functions will apply Excel's 15 digit character limit, where the numbers after the 15th digit are converted to 0. And, these functions convert the strings to numbers internally, so it does not matter that you have them formatted as text (if the text could not be converted to a number, then these functions work fine). See screenshot for example - the last 3 digits are different, but countif is treating them as the same.

I think this is one alternative to doing what you're asking: =IFERROR(INDEX(Table1[ASNnro], SMALL(IF(MATCH(Table1[ASNnro], Table1[ASNnro], 0)=ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))), MATCH(Table1[ASNnro], Table1[ASNnro], 0)), ROW(INDIRECT("1:"&ROWS(Table1[ASNnro]))))), "")

Hi, check this out, using QUERY and RANK (which is actualy not necessary, as the names wlll always be ranked in order 1 to 9). =QUERY(B4:C14,"SELECT B, SUM(C) WHERE C IS NOT NULL GROUP BY B ORDER BY SUM(C) DESC",0).

https://docs.google.com/spreadsheets/d/1NYBfHD2nJGi7PMkaNhlp04S6sBgTtMF-INOoXCQ0xoc/copy?usp=sharing

Hope this helps !

For below <50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11<50))),"")

For Above >50 =IFERROR(ARRAYTOTEXT(FILTER(C$3:C$11,($B$3:$B$11=$F4)*($D$3:$D$11>50))),"")

Also, =HSTACK(TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,2),TAKE(HSTACK(XLOOKUP(SEQUENCE(SUM(P2:P7)),VSTACK(1,SCAN(1,$P$2:$P$7,LAMBDA(a,c,a+c))),VSTACK(D2:D7,""),,-1),TOCOL(E2:O7,0),SORTBY(HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1),TOCOL(E10:O15,0)),HSTACK(XLOOKUP(SEQUENCE(SUM(P10:P15)),VSTACK(1,SCAN(1,$P$10:$P$15,LAMBDA(a,c,a+c))),VSTACK(D10:D15,""),,-1)),1)),,-1))

Creo que ArrayFormula es incompatible con sum, no así con sumif

try:

I22=COUNTIFS(D22:H22,UNICHAR(10003))

J22=IF(I22=5,"✓","")

K22=IF(I22<>5,"✓","")

B29=IFERROR(INDEX($C$22:$C$26,AGGREGATE(15,6,(ROW($J$22:$J$26)-ROW($J$22)+1)/($J$22:$J$26=UNICHAR(10003)),ROWS(B$28:B28))),"")

C29=IFERROR(INDEX($C$22:$C$26,AGGREGATE(15,6,(ROW($K$22:$K$26)-ROW($K$22)+1)/($K$22:$K$26=UNICHAR(10003)),ROWS(C$28:C28))),"")

D29=TEXTJOIN(",",1,REPT($D$21:$H$21,INDEX($D$22:$H$26,MATCH($C29,$C$22:$C$26,0),0)<>UNICHAR(10003)))

First, you should remove the function iferror, so that we can see the real function outcome.

Second, i guess you input "row($G$49:$G$68) which will return the number from 49 to 68, means only 20 rows, so i think this should be the cause of your problem.

Hope this helps.

you mean GKXLOOKUP??? Xlookup works like all the other lookup function, it returns the FIRST (or LAST if doing a bottom up search) it is NOT THE FILTER function to return a whole array of values

ALSO YOU NEED TO SHOW THE FORMULA you are using otherwise there is no way to help at all

The formula that I have used is mentioned here (in case it is not clear in the photo):

=IF(ROWS($FF$3:$FG$3)<=$CN$7,(IFERROR(INDEX(WeeklyAll[WBS Description],IFERROR(AGGREGATE(15,3,(WeeklyAll[Sat]=$CQ$7)/(WeeklyAll[Sat]=$CQ$7)*(ROW(WeeklyAll[Sat])-ROW(WeeklyAll[[#Headers],[Sat]])),ROWS($FF$3:$FG$3)),"")),"")),"")

Gaetano Sportelli if you are not using MS365 then use INDEX() & MATCH() Function

• Formula used in cell C3

=IFERROR(INDEX($K$3:$K$26,MATCH(1,($A3=$I$3:$I$26)($B3=$J$3:$J$26)(C$2=$L$3:$L$26),0)),"")

And Fill Down & Right for the cells accordingly !!!

Gaetano Sportelli if you are not using MS365 then use INDEX() & MATCH() Function

• Formula used in cell C3

=IFERROR(INDEX($K$3:$K$26,MATCH(1,($A3=$I$3:$I$26)($B3=$J$3:$J$26)(C$2=$L$3:$L$26),0)),"")

And Fill Down & Right for the cells accordingly !!!

Assuming you have office 365 (or a dynamic array version of excel that has these functions), I would first separate the names and values into separate columns (may be able to use text to columns feature).

Then, say the data is in A1:B7, you could try: =LET(names, UNIQUE(A1:A7), values, BYROW(TRANSPOSE(IF(A1:A7=TRANSPOSE(names), B1:B7, "")), LAMBDA(rw, TEXTJOIN(" ", TRUE, SORT(UNIQUE(rw, TRUE))))), names&" "&values)

Or, you might check out power query and see if this is something it could do.

can you explain the logic behind this?

thêm cột phụ để xác định các mã sát nhau vd a1 = 1, if a2 = a1 then a1 else a1+1 sau đó dùng pivot table xem sao b.

Mình làm chuyên về xây dựng các hệ thống và xử lý dũ liệu liên kết, đồng bộ tự động trên Google Sheet và Excel, nếu okay mất phí thì inbox mình tư vấn và làm cho.