ASCAN

ASCAN(a,[d])
a
array
[d]
direction argument ; 0 or omitted scan by array, -1 by rows, 1 by clms
Array SCAN, 3 in 1 function, SCAN by row, by column, by array
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.
Excel Formula: 
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)
3123413610136101234
45678152128365111826681012
5910111245556678919304215182124
613141516911051201361327425828323640
7
8d,omitted (by array)d,-1 (by rows)d,1 (by clms)
9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)
10123413610136101234
115678152128365111826681012
12910111245556678919304215182124
1313141516911051201361327425828323640
14171819201531711902101735547445505560
15212223242312532763002143669066727884
16
17Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post
Cell Formulas
RangeFormula
F2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)
F3:I6F3=ASCAN(A3:D6)
K3:N6K3=ASCAN(A3:D6,-1)
P3:S6P3=ASCAN(A3:D6,1)
A10:D15A10=SEQUENCE(6,4)
F10:I15F10=ASCAN(A10#)
K10:N15K10=ASCAN(A10#,-1)
P10:S15P10=ASCAN(A10#,1)
Dynamic array formulas.
 
Upvote 0
Example, 2 ways of using expandable ranges in single cell formulas, inspired by Leila's latest YT Excel LOOKUP Function Only PROS Use (simple to complex examples)
Cell by cell solution:
Lookup-Function-XelPlus.xlsx
ABCDE
1BOM LevelPart NumberImmediate Parent PartXLOOKUP Version
2010420-1001--
3110112-100110420-100110420-1001
4210409-100110112-100110112-1001
5210800-100110112-100110112-1001
6210800-100210112-100110112-1001
7110410-100110420-100110420-1001
8210410-100310410-100110410-1001
9310107-100110410-100310410-1003
10310800-100310410-100310410-1003
11310800-100410410-100310410-1003
12310108-100010410-100310410-1003
13210410-100210410-100110410-1001
14310107-100110410-100210410-1002
15310800-100310410-100210410-1002
16310800-100410410-100210410-1002
17310108-100010410-100210410-1002
18
19
20
Lookup Advanced
Cell Formulas
RangeFormula
C2:C17C2=IFERROR(LOOKUP(2,1/(A2-1=$A$2:A2),$B$2:B2),"-")
D2:D17D2=XLOOKUP(A2-1, $A$2:A2, $B$2:B2, "-", 0, -1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:F17Cell Value=$B$13textNO
B2:F17Cell Value=$B$8textNO
B2:F17Cell Value=$B$7textNO
B2:F17Cell Value=$B$3textNO
B2:F17Cell Value=$B$2textNO
 
Single cell formula C2, 1st method using SCAN
Excel Formula: 
=LET(b,A2:A17,p,B2:B17,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(v,i,LET(s,SEQUENCE(i),XLOOKUP(INDEX(b,i)-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Lookup-Function-XelPlus.xlsx
ABCD
1BOM LevelPart NumberImmediate Parent Part
2010420-1001-
3110112-100110420-1001
4210409-100110112-1001
5210800-100110112-1001
6210800-100210112-1001
7110410-100110420-1001
8210410-100310410-1001
9310107-100110410-1003
10310800-100310410-1003
11310800-100410410-1003
12310108-100010410-1003
13210410-100210410-1001
14310107-100110410-1002
15310800-100310410-1002
16310800-100410410-1002
17310108-100010410-1002
18
19
Lookup Advanced (2)
Cell Formulas
RangeFormula
C2:C17C2=LET(b,A2:A17,p,B2:B17,SCAN(0,SEQUENCE(ROWS(b)),LAMBDA(v,i,LET(s,SEQUENCE(i),XLOOKUP(INDEX(b,i)-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:C17,B2,G2Cell Value=$B$13textNO
B3:C17,B2,G2Cell Value=$B$8textNO
B3:C17,B2,G2Cell Value=$B$7textNO
B3:C17,B2,G2Cell Value=$B$3textNO
B3:C17,B2,G2Cell Value=$B$2textNO
 
Single cell formula C2, 2nd method, using MAP with 2 arguments.
Manual expandable ranges are ok if the array lives in a spreadsheet. If the array lives or is calculated inside a function or formula, we need single cell formulas.
Excel Formula: 
=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Lookup-Function-XelPlus.xlsx
ABCD
1BOM LevelPart NumberImmediate Parent Part
2010420-1001-
3110112-100110420-1001
4210409-100110112-1001
5210800-100110112-1001
6210800-100210112-1001
7110410-100110420-1001
8210410-100310410-1001
9310107-100110410-1003
10310800-100310410-1003
11310800-100410410-1003
12310108-100010410-1003
13210410-100210410-1001
14310107-100110410-1002
15310800-100310410-1002
16310800-100410410-1002
17310108-100010410-1002
18
19
Lookup Advanced (2)
Cell Formulas
RangeFormula
C2:C17C2=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:C17Cell Value=$B$13textNO
B2:C17Cell Value=$B$8textNO
B2:C17Cell Value=$B$7textNO
B2:C17Cell Value=$B$3textNO
B2:C17Cell Value=$B$2textNO
 
This is my take of solving Jon's Excel Campus YT challenge: Excel Challenge: Baby Shower Guessing Game
followed by the next YT with the solutions to the Guessing Game challenge: Excel Formula Training: IF, ABS, RANK, ROUND, LET & more
I have raised the stakes of the challenge and wrote a function that can solve not only the particular initial scenario, but any array of any guessing game that complies with the general set of rules.
The function is named GUESS and consists in a main REDUCE formula built around 2 other lambdas (EN extract numbers and PC points calculation) that are not defined or called separately, are embedded in the main function.
Can deal with any data types, numeric integers or with decimals, dates or time format, any text (any units, wrote correctly or even misspelled)
GUESS(a,v,p,s,b,[ti])
a: estimations array
v: values actual stats array
p: penalties array
s: starting points (50 in our case)
b: bonus points (100)
[ti]: column index of time column only if is in numeric format. (if there is a time column in text format, ti can be omitted)
Excel Formula: 
=LAMBDA(a,v,p,s,b,[ti],
    LET(
        EN, LAMBDA(c,
            LET(
                b, IFERROR(--MID(c, SEQUENCE(, MAX(LEN(c))), 1), " "),
                d, TRIM(BYROW(b, LAMBDA(x, TEXTJOIN(, , x)))),
                --SUBSTITUTE(d, " ", ".")
            )
        ),
        PC, LAMBDA(c,v,p,
            LET(
                x, ABS(INT(c) - INT(v)),
                y, ABS(MOD(c, 1) - MOD(v, 1)),
                IF((x = 0) + (y = 0) = 2, b, s - x * p)
            )
        ),
        REDUCE(0, SEQUENCE(COLUMNS(a)),
            LAMBDA(x,i,
                LET(
                    y, INDEX(a, , i),
                    z, INDEX(v, i),
                    x +PC(EN(IF(OR(i = ti), y * 24, y)),EN(IF(OR(i = ti), z * 24, z)),INDEX(p, i))
                )
            )
        )
    )
)
Excelλambda Guessing Game.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1Excelλambda Guessing Gamesingle cell F7:=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)
2Array :↓
3penalties2152↓Top resultsRankCategoryResults distribution: single cell formula V7
4Actual Stats05-07-2219:3010lbs 2oz22.00↓(Ranking,19 ← input cell DVTime of Birth ← DVpenalties2152sum
5↓ties incl.)Actual Stats05-07-2219:3010lbs 2oz22.00byrow
6NameDate of BirthTime of BirthWeightLength (in)PointsWinnerNr.PointsName MatchRankNamePointsTime of BirthNameNameDate of BirthTime of BirthWeightLength (in)
7Dasie04-07-223:139lbs 10oz22.0022771299Annmarie1Annmarie29934DasieDasie483445100227
8Holly10-07-2211:429lbs 10oz21.40175132296Grayce2Grayce29642HollyHolly40424548175
9Katheryn10-07-228:429lbs 11oz21.00172143289Dotti3Dotti28939KatherynKatheryn40394548172
10Doralynne01-07-221:309lbs 5oz23.50167154281Mariya4Mariya28132DoralynneDoralynne42324548167
11Grayce03-07-2219:3010lbs22.0029625278Carolee5Carolee278100GrayceGrayce4610050100296
12Micki03-07-2223:5510lbs23.0019096230Janice6Janice23046MickiMicki46465048190
13Carrie04-07-229:309lbs 7oz18.00175137227Dasie7Dasie22740CarrieCarrie48404542175
14Brianna04-07-223:379lbs 3oz21.75175138226Gillian8Gillian22634BriannaBrianna48344548175
15Dotti05-07-228:2010lbs 8oz22.0028939190Micki9Micki19039DottiDotti1003950100289
16Annmarie05-07-2218:3510lbs22.00299110186Shandra10Shandra18649AnnmarieAnnmarie1004950100299
17Helene18-07-221:2010lbs 4oz20.001521811184Brianne11Brianne18432HeleneHelene24325046152
18Krystalle07-07-226:009lbs23.001761212176Krystalle12Krystalle17637KrystalleKrystalle46374548176
19Brianne04-07-227:0410lbs 7oz21.001841113175Brianna,Carrie,Holly13Brianna17538BrianneBrianne48385048184
20Cathi09-07-2211:308 lbs23.001721414172Cathi,Katheryn13Carrie17542CathiCathi42424048172
21Gillian10-07-225:0010lbs 8oz22.00226815167Doralynne13Holly17536GillianGillian403650100226
22Sherill12-07-223:479lbs 9oz22.501651616165Rosabelle,Sherill14Cathi17234SherillSherill36344550165
23Carolee05-07-222:309lbs 14oz22.00278517154Sheree14Katheryn17233CaroleeCarolee1003345100278
24Janice10-07-229:3010lbs 5oz22.00230618152Helene15Doralynne16740JaniceJanice404050100230
25Eveline18-07-222:2311lbs 3oz21.001501919150Eveline16Rosabelle16533EvelineEveline24334548150
26Sheree03-07-2219:536lbs 3oz33.001541716Sherill16550ShereeSheree46503028154
27Rosabelle03-07-220:288lbs 7 oz23.001651617Sheree15431RosabelleRosabelle46314048165
28Mariya05-07-2210:538lbs 9oz22.00281418Helene15241MariyaMariya1004140100281
29Shandra08-07-2220:459lbs 4oz21.001861019Eveline15049ShandraShandra44494548186
30
31
Challenge (2)
Cell Formulas
RangeFormula
G1G1=FORMULATEXT(F7)
Q6Q6=Q4
F7:F29F7=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)
G7:G29G7=XMATCH(F7#,SORT(UNIQUE(F7#),,-1))
I7:I25I7=SEQUENCE(I4)
J7:J25J7=INDEX(SORT(UNIQUE(F7#),,-1),SEQUENCE(I4))
K7:K25K7=BYROW(J7#,LAMBDA(x,TEXTJOIN(",",,SORT(IF(ISNUMBER(XMATCH(F7#,x)),A7:A29,"")))))
M7:M29M7=XMATCH(O7:O29,SORT(UNIQUE(O7:O29),,-1))
N7:O29N7=SORT(HSTACK(A7:A29,F7#),{2,1},{-1,1})
Q7:Q29Q7=LET(x,XMATCH(Q6,B6:E6),a,INDEX(B7:E29,,x),v,INDEX(B4:E4,x),p,INDEX(B3:E3,x),ti,IF(x=2,1,0),GUESS(a,v,p,50,100,ti))
R7:R29R7=A7:A29
T7:T29T7=A7:A29
U7:X29U7=REDUCE(0,SEQUENCE(,4),LAMBDA(v,i,IF(SEQUENCE(,i)=i,GUESS(INDEX(B7:E29,,i),INDEX(U5:X5,i),INDEX(U4:X4,i),50,100,IF(i=2,1,0)),v)))
Z7:Z29Z7=BYROW(U7#,LAMBDA(x,SUM(x)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I7:K25Expression=ROW($I7)-ROW($I$6)=$I$4textNO
Cells with Data Validation
CellAllowCriteria
U13List1,2,3,4
I4Whole numberbetween 1 and ROWS(UNIQUE(O7:O29))
Q4List=$B$6:$E$6
 
All formulas used to reveal all kinds of stats of the game, only single cell formulas:
Excelλambda Guessing Game.xlsx
BCD
32Single cell formulas
33
34cells=FORMULATEXT(INDIRECT(B35:B46))
35F7=GUESS(B7:E29,B4:E4,B3:E3,50,100,2)
36G7=XMATCH(F7#,SORT(UNIQUE(F7#),,-1))
37I7=SEQUENCE(I4)
38J7=INDEX(SORT(UNIQUE(F7#),,-1),SEQUENCE(I4))
39K7=BYROW(J7#,LAMBDA(x,TEXTJOIN(",",,SORT(IF(ISNUMBER(XMATCH(F7#,x)),A7:A29,"")))))
40M7=XMATCH(O7:O29,SORT(UNIQUE(O7:O29),,-1))
41N7=SORT(HSTACK(A7:A29,F7#),{2,1},{-1,1})
42Q7=LET(x,XMATCH(Q6,B6:E6),a,INDEX(B7:E29,,x),v,INDEX(B4:E4,x),p,INDEX(B3:E3,x),ti,IF(x=2,1,0),GUESS(a,v,p,50,100,ti))
43R7=A7:A29
44T7=A7:A29
45U7=REDUCE(0,SEQUENCE(,4),LAMBDA(v,i,IF(SEQUENCE(,i)=i,GUESS(INDEX(B7:E29,,i),INDEX(U5:X5,i),INDEX(U4:X4,i),50,100,IF(i=2,1,0)),v)))
46Z7=BYROW(U7#,LAMBDA(x,SUM(x)))
47
Challenge (2)
Cell Formulas
RangeFormula
C34C34=FORMULATEXT(C35)
C35:C46C35=FORMULATEXT(INDIRECT(B35:B46))
Dynamic array formulas.
 
The function also can be used in the case of table format:
Excelλambda Guessing Game.xlsx
ABCDEFGHIJKLM
32
33Table :=GUESS(Bs[@[Date of Birth]:[Length (in)]],$B$35:$E$35,$B$34:$E$34,50,100,2)
34penalties2152↓
35Actual Stats05-07-2219:3010lbs 2oz22.00↓
36↓
37NameDate of BirthTime of BirthWeightLength (in)Points
38Dasie04-07-223:139lbs 10oz22.00227
39Holly10-07-2211:429lbs 10oz21.40175
40Katheryn10-07-228:429lbs 11oz21.00172
41Doralynne01-07-221:309lbs 5oz23.50167
42Grayce03-07-2219:3010lbs22.00296
43Micki03-07-2223:5510lbs23.00190
44Carrie04-07-229:309lbs 7oz18.00175
45Brianna04-07-223:379lbs 3oz21.75175
46Dotti05-07-228:2010lbs 8oz22.00289
47Annmarie05-07-2218:3510lbs22.00299
48Helene18-07-221:2010lbs 4oz20.00152
49Krystalle07-07-226:009lbs23.00176
50Brianne04-07-227:0410lbs 7oz21.00184
51Cathi09-07-2211:308 lbs23.00172
52Gillian10-07-225:0010lbs 8oz22.00226
53Sherill12-07-223:479lbs 9oz22.50165
54Carolee05-07-222:309lbs 14oz22.00278
55Janice10-07-229:3010lbs 5oz22.00230
56Eveline18-07-222:2311lbs 3oz21.00150
57Sheree03-07-2219:536lbs 3oz33.00154
58Rosabelle03-07-220:288lbs 7 oz23.00165
59Mariya05-07-2210:538lbs 9oz22.00281
60Shandra08-07-2220:459lbs 4oz21.00186
61
Challenge
Cell Formulas
RangeFormula
F33F33=FORMULATEXT(F38)
F38:F60F38=GUESS(Bs[@[Date of Birth]:[Length (in)]],$B$35:$E$35,$B$34:$E$34,50,100,2)
 
Function's Concept
Excelλambda Guessing Game.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Excelλambda Guessing Game
2Function Concept: only 2 embedded lambdas EN,PC and a REDUCE formula for iterations. No other variables needed
3Versatility
4 - can handle different dimensions of array/tables
5 - can handle data in numeric format, time format and any text format (2(or single) clusters of digits with any text in-between, before or after)
6
7GUESS(a,v,p,s,b,[ti]
8arguments =LAMBDA(a,v,p,s,b,[ti], LET( EN, LAMBDA(c, LET( b, IFERROR(--MID(c, SEQUENCE(, MAX(LEN(c))), 1), " "), d, TRIM(BYROW(b, LAMBDA(x, TEXTJOIN(, , x)))), --SUBSTITUTE(d, " ", ".") ) ), PC, LAMBDA(c,v,p, LET( x, ABS(INT(c) - INT(v)), y, ABS(MOD(c, 1) - MOD(v, 1)), IF((x = 0) + (y = 0) = 2, b, s - x * p) ) ), REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(x,i, LET( y, INDEX(a, , i), z, INDEX(v, i), x +PC(EN(IF(OR(i = ti), y * 24, y)),EN(IF(OR(i = ti), z * 24, z)),INDEX(p, i)) ) ) ) ) )
9a: estimations arrayembedded lambda EN(c) - Extract Numbers [ c: column or single value ]
10v: values actual stats array - removes any text btw 2 cluster of digits and joins them as a number, integer or with decimals
11p: penalties array (or constant array)
12s: starting points (50)← expands horiz all chars with MID, if not numbers. => spaces
13b: bonus points (100)← joins BYROW and TRIMs the result
14[ti]: time column index← replaces the middle space left by trim with "." decimal point => initial column transformed in a numeric column
15(only if time in numeric format,Note: If the column has integers or decimal numbers EN will not affect them.
16if in text format can be omitted)
17embedded lambda PC(c,v,p) - Points Calculations [ c: column ; v: reference value (actual stats) ; p: points penalty ]
18
19← variance integers
20← variance decimal part
21← points calculation, if both var. are 0 => bonus points b, if not => starting points - dif of integers*penalty points p
22
23
24REDUCE iterates PC calculations for all columns, summing the results
25 - since EN does not affect numeric columns but excludes any text wherever is found will use PC(EN(c),EN(v),p)
26Only exception is for numeric time columns that have to be multiplied by 24, hence the time index argument ti
27If time column in text format (ex:TEXT(t,"hh:mm") or 19h30m ) ti can be omitted, EN will do the job
28
29Note: EN's purpose is for creating numeric values for PC calculations, that can be separated
30in integer/decimal parts wits ease, is not an unit converter, only for variance calculations.
31
32
33
34
35
Concept
 
Proof that the function works for any arrays and any text format (any units)
To check the consistency of the results I have doubled and tripled the initial array to the right.
Excelλambda Guessing Game.xlsx
ABCDEFGHIJKLMNOPQRST
1Excelλambda Guessing Game
2Checking versatility for any arrays
3
4Doubled the array to the right
5penalties21522152=GUESS(C9:J31,C6:J6,C5:J5,50,100,{2,6})
6Actual Stats05-07-2219:3010lbs 2oz22.0005-07-2219:3010lbs 2oz22.00↓
7↓resultscheck for double
8NameDate of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Pointsinitial array=AND(O9:O31*2=M9#)
9Dasie04-07-223:139lbs 10oz22.0004-07-223:139lbs 10oz22.00454227TRUE
10Holly10-07-2211:429lbs 10oz21.4010-07-2211:429lbs 10oz21.40350175
11Katheryn10-07-228:429lbs 11oz21.0010-07-228:429lbs 11oz21.00344172
12Doralynne01-07-221:309lbs 5oz23.5001-07-221:309lbs 5oz23.50334167
13Grayce03-07-2219:3010lbs22.0003-07-2219:3010lbs22.00592296
14Micki03-07-2223:5510lbs23.0003-07-2223:5510lbs23.00380190
15Carrie04-07-229:309lbs 7oz18.0004-07-229:309lbs 7oz18.00350175
16Brianna04-07-223:379lbs 3oz21.7504-07-223:379lbs 3oz21.75350175
17Dotti05-07-228:2010lbs 8oz22.0005-07-228:2010lbs 8oz22.00578289
18Annmarie05-07-2218:3510lbs22.0005-07-2218:3510lbs22.00598299
19Helene18-07-221:2010lbs 4oz20.0018-07-221:2010lbs 4oz20.00304152
20Krystalle07-07-226:009lbs23.0007-07-226:009lbs23.00352176
21Brianne04-07-227:0410lbs 7oz21.0004-07-227:0410lbs 7oz21.00368184
22Cathi09-07-2211:308 lbs23.0009-07-2211:308 lbs23.00344172
23Gillian10-07-225:0010lbs 8oz22.0010-07-225:0010lbs 8oz22.00452226
24Sherill12-07-223:479lbs 9oz22.5012-07-223:479lbs 9oz22.50330165
25Carolee05-07-222:309lbs 14oz22.0005-07-222:309lbs 14oz22.00556278
26Janice10-07-229:3010lbs 5oz22.0010-07-229:3010lbs 5oz22.00460230
27Eveline18-07-222:2311lbs 3oz21.0018-07-222:2311lbs 3oz21.00300150
28Sheree03-07-2219:536lbs 3oz33.0003-07-2219:536lbs 3oz33.00308154
29Rosabelle03-07-220:288lbs 7 oz23.0003-07-220:288lbs 7 oz23.00330165
30Mariya05-07-2210:538lbs 9oz22.0005-07-2210:538lbs 9oz22.00562281
31Shandra08-07-2220:459lbs 4oz21.0008-07-2220:459lbs 4oz21.00372186
32
33
34Tripled the array to the right, this time messed up big time with the data (red ), and time format as text
35penalties215221522152
36Actual Stats05-07-2219:3010lbs 2oz22.0005-07-2219:3010lbs 2oz22.0005-07-2219h30m10lbs 2oz22.00
37=GUESS(C39:N61,C36:N36,C35:N35,50,100,{2,6})
38NameDate of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Date of BirthTime of BirthWeightLength (in)Points
39Dasie04-07-223:139lbs 10oz22.0004-07-223:139lbs 10oz22.0004-07-223 ; 139 l 10 o22,0681
40Holly10-07-2211:429lbs 10oz21.4010-07-2211:429lbs 10oz21.4010-07-22h11 m429l10o21;4525check for triple
41Katheryn10-07-228:429lbs 11oz21.0010-07-228:429lbs 11oz21.0010-07-228h42m9.11in21.0516=AND(O9:O31*3=P39#)
42Doralynne01-07-221:309lbs 5oz23.5001-07-221:309lbs 5oz23.5001-07-221h30m9,523 50501TRUE
43Grayce03-07-2219:3010lbs22.0003-07-2219:3010lbs22.0003-07-2219h30m1022 ;888
44Micki03-07-2223:5510lbs23.0003-07-2223:5510lbs23.0003-07-2223h55m10lbsinch23570
45Carrie04-07-229:309lbs 7oz18.0004-07-229:309lbs 7oz18.0004-07-229h30m9 bs 7oz18,,,00525
46Brianna04-07-223:379lbs 3oz21.7504-07-223:379lbs 3oz21.7504-07-223h37m9…321x75525
47Dotti05-07-228:2010lbs 8oz22.0005-07-228:2010lbs 8oz22.0005-07-228h20m10x 8y22.00867
48Annmarie05-07-2218:3510lbs22.0005-07-2218:3510lbs22.0005-07-2218h35m10 l b s22.00897
49Helene18-07-221:2010lbs 4oz20.0018-07-221:2010lbs 4oz20.0018-07-221h20m10xx420.00456
50Krystalle07-07-226:009lbs23.0007-07-226:009lbs23.0007-07-226hlbs923.00528
51Brianne04-07-227:0410lbs 7oz21.0004-07-227:0410lbs 7oz21.0004-07-227h4mlbs10oz721.00552
52Cathi09-07-2211:308 lbs23.0009-07-2211:308 lbs23.0009-07-2211h30mlbs8, 0 o z23.00516
53Gillian10-07-225:0010lbs 8oz22.0010-07-225:0010lbs 8oz22.0010-07-225h10lbs 8oz22.00678
54Sherill12-07-223:479lbs 9oz22.5012-07-223:479lbs 9oz22.5012-07-223h47m9lbs 9oz22.50495
55Carolee05-07-222:309lbs 14oz22.0005-07-222:309lbs 14oz22.0005-07-222h30m9lbs 14oz22.00834
56Janice10-07-229:3010lbs 5oz22.0010-07-229:3010lbs 5oz22.0010-07-229h30m10lbs 5oz22.00690
57Eveline18-07-222:2311lbs 3oz21.0018-07-222:2311lbs 3oz21.0018-07-222h23m11lbs 3oz21.00450
58Sheree03-07-2219:536lbs 3oz33.0003-07-2219:536lbs 3oz33.0003-07-2219h53m6lbs 3oz33.00462
59Rosabelle03-07-220:288lbs 7 oz23.0003-07-220:288lbs 7 oz23.0003-07-220h28m8lbs 7 oz23.00495
60Mariya05-07-2210:538lbs 9oz22.0005-07-2210:538lbs 9oz22.0005-07-2210h53m8lbs 9oz22.00843
61Shandra08-07-2220:459lbs 4oz21.0008-07-2220:459lbs 4oz21.0008-07-2220h45m9lbs 4oz21.00558
62
Any arrays
Cell Formulas
RangeFormula
M5M5=FORMULATEXT(M9)
Q8,R41Q8=FORMULATEXT(Q9)
M9:M31M9=GUESS(C9:J31,C6:J6,C5:J5,50,100,{2,6})
Q9Q9=AND(O9:O31*2=M9#)
P37P37=FORMULATEXT(P39)
P39:P61P39=GUESS(C39:N61,C36:N36,C35:N35,50,100,{2,6})
R42R42=AND(O9:O31*3=P39#)
Dynamic array formulas.
 
Inspired by today's ExcelIsFun YT (14Jun22): Duplicates in Row? Conditional Formatting, Counting and Logical Formula. Excel Magic Trick 1793
EMT1793.xlsx
ABCDEFGHIJKLMN
1
2Data1Data2Data3Data4Data5Data6Data7Data8Data9Dups?Count Unique
3601491322819709292TRUE8
423883514931643886FALSE9
5FredChinChantelMikiChantelJenoTyroneTyroneLimTRUE7
6FredChinChantelMikiDinoJenoBiggyTyroneLimFALSE9
7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiTRUE6
8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBFALSE9
919637022529458441FALSE9
10518852184175138047FALSE9
11298570178132848398FALSE9
12801226508862271525FALSE9
13276266593897138457FALSE9
14244340298721992619FALSE9
159537249010143471TRUE8
16901165782033654997TRUE8
17132446292338387371TRUE8
1873976191293741123FALSE9
199927195853162448FALSE9
20766228214662732214TRUE8
21874141167074766971TRUE8
22955069259350285039TRUE7
23119063172093488690TRUE8
2492533219721514367FALSE9
25
26single cell formula K3, no countifs
27=LET(a,B3:J24,c,COLUMNS(a),f,LAMBDA([k],BYROW(a,LAMBDA(x,LET(y,COUNTA(UNIQUE(x,1)),IF(k,y,y<>c))))),HSTACK(f(),f(1)))
28
29Using an embedded lambda f(k) we do not need to use 2 separate BYROWs => HSTACK(f(),f(1))
30
31Note: Using countifs for CF is ok, for formulas, if we want our constructions to be used inside other formula constructions,
32should be avoided (range argument can not work with array calculations)
33
1793
Cell Formulas
RangeFormula
K3:L24K3=LET(a,B3:J24,c,COLUMNS(a),f,LAMBDA([k],BYROW(a,LAMBDA(x,LET(y,COUNTA(UNIQUE(x,1)),IF(k,y,y<>c))))),HSTACK(f(),f(1)))
B27B27=FORMULATEXT(K3)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO
 
Extracting unique byrows in ascending order using SPILLBYROWS (this thread post #50)
EMT1793.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2Data1Data2Data3Data4Data5Data6Data7Data8Data9=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
36014913228197092921419283260709192
42388351493164388612331353849648688
5FredChinChantelMikiChantelJenoTyroneTyroneLimChantelChinFredJenoLimMikiTyrone
6FredChinChantelMikiDinoJenoBiggyTyroneLimBiggyChantelChinDinoFredJenoLimMikiTyrone
7QuadQuadMBCarlotaQuadSunshineAspenCarlotaYanakiAspenCarlotaMBQuadSunshineYanaki
8QadCarlotaSunshineAspenYanakiSunsetSunspotBellenMBAspenBellenCarlotaMBQadSunsetSunshineSunspotYanaki
91963702252945844151922294145637084
10518852184175138047131841475152758088
11298570178132848398172932708183848598
12801226508862271525121525262750628088
13276266593897138457132738575962668497
14244340298721992619192124262940438799
159537249010143471110243743479095
169011657820336549971120334965789097
171324462923383873711323242938467173
187397619129374112331123296173749197
1999271958531624482624314871859599
207662282146627322141421222846627376
218741411670747669711641697071747687
2295506925935028503925283950699395
231190631720934886901117204863869093
249253321972151436731415193253677292
25
1793 (2)
Cell Formulas
RangeFormula
L2L2=FORMULATEXT(L3)
L3:T24L3=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:J24Expression=COUNTIFS($B3:$J3,B3)>1textNO
 
You must log in or register to reply here.

Forum statistics

Threads
1,223,947
Messages
6,175,562
Members
452,652
Latest member
eduedu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top