learning arrays

H

Holger

Board Regular
Joined
Nov 22, 2017
Messages
75
Office Version
  1. 365
Platform
  1. MacOS
Hi guys,

I'm learning array functions and while making my way through things I'm stuck in understanding something so wondering if someone could shed some light on this:

* in k34 formula I was able to nominate the array by naming J49# array and then limit it to only columns > 13 (col n, o,p,q,r,s)
* in J34 formula I wanted to do the same but can't get it to work. Instead, I refer to each col I pick from J49# via index formula (index.....col 5,6,7,8,9,10). As you can see Lamdba then has lots of variables

My questions are:
1. In k34 the column I pick are selected by column(j49#)>13. 13 refers to spreadsheet column M, not to a specific column of the array. How can this be done better?
2. K34 is much shorter than J34 so how can I apply the "shorter way" (limiting which col to add) from k34 to map function in j34. I can use isnumber as invoice col and col3 are numbers but not invoice amounts

Thanks for any assistance and direction.

Cheers H
Let Function - Split Col and Group by Sum row^Mcol.xlsx
JKLMNOPQRS
33longshort
34SumSum
35792.05792.05
361,303.951,303.95
37667.50667.50
381,411.791,411.79
392,122.282,122.28
40667.50667.50
411,561.441,561.44
421,861.791,861.79
431,411.791,411.79
441,996.581,996.58
45
46
47
48
49ProductInvoiceCol3Col4Col5Col6Col7Col8Col9Col10
50Product A12343216540000Country Origin: UK5.8539.000.0018.680.00728.52
51Product B56786541230000Country Origin: JP9.1561.000.0019.900.001,213.90
52Product C43219632541000Country Origin: UK12100.000.005.500.00550.00
53Product D87652365987000Country Origin: US9.9100.000.0012.890.001,289.00
54Product B56786541230000Country Origin: JP12.38100.000.0019.900.001,990.00
55Product C43219632541000Country Origin: US12100.000.005.500.00550.00
56Product A12343216540000Country Origin: UK7.7278.000.0018.680.001,457.04
57Product A12343216540000Country Origin: UK12.9193.000.0018.680.001,737.20
58Product D87652365987000Country Origin: US9.9100.000.0012.890.001,289.00
59Product A12343216540000Country Origin: UK9.9100.000.0018.680.001,868.00
Test
Cell Formulas
RangeFormula
J34:J44J34=VSTACK("Sum",DROP(MAP(INDEX(J49#,,5),INDEX(J49#,,6),INDEX(J49#,,7),INDEX(J49#,,8),INDEX(J49#,,9),INDEX(J49#,,10),LAMBDA(a,b,c,d,e,f,a+b+c+d+e+f)),1))
K34:K44K34=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),LAMBDA(row,SUM(row))),1))
J49:S59J49=J2#
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Test!$J$2:$S$12J49
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Holger said:
Hi guys,

I'm learning array functions and while making my way through things I'm stuck in understanding something so wondering if someone could shed some light on this:

* in k34 formula I was able to nominate the array by naming J49# array and then limit it to only columns > 13 (col n, o,p,q,r,s)
* in J34 formula I wanted to do the same but can't get it to work. Instead, I refer to each col I pick from J49# via index formula (index.....col 5,6,7,8,9,10). As you can see Lamdba then has lots of variables

My questions are:
1. In k34 the column I pick are selected by column(j49#)>13. 13 refers to spreadsheet column M, not to a specific column of the array. How can this be done better?
2. K34 is much shorter than J34 so how can I apply the "shorter way" (limiting which col to add) from k34 to map function in j34. I can use isnumber as invoice col and col3 are numbers but not invoice amounts

Thanks for any assistance and direction.

Cheers H
Let Function - Split Col and Group by Sum row^Mcol.xlsx
JKLMNOPQRS
33longshort
34SumSum
35792.05792.05
361,303.951,303.95
37667.50667.50
381,411.791,411.79
392,122.282,122.28
40667.50667.50
411,561.441,561.44
421,861.791,861.79
431,411.791,411.79
441,996.581,996.58
45
46
47
48
49ProductInvoiceCol3Col4Col5Col6Col7Col8Col9Col10
50Product A12343216540000Country Origin: UK5.8539.000.0018.680.00728.52
51Product B56786541230000Country Origin: JP9.1561.000.0019.900.001,213.90
52Product C43219632541000Country Origin: UK12100.000.005.500.00550.00
53Product D87652365987000Country Origin: US9.9100.000.0012.890.001,289.00
54Product B56786541230000Country Origin: JP12.38100.000.0019.900.001,990.00
55Product C43219632541000Country Origin: US12100.000.005.500.00550.00
56Product A12343216540000Country Origin: UK7.7278.000.0018.680.001,457.04
57Product A12343216540000Country Origin: UK12.9193.000.0018.680.001,737.20
58Product D87652365987000Country Origin: US9.9100.000.0012.890.001,289.00
59Product A12343216540000Country Origin: UK9.9100.000.0018.680.001,868.00
Test
Cell Formulas
RangeFormula
J34:J44J34=VSTACK("Sum",DROP(MAP(INDEX(J49#,,5),INDEX(J49#,,6),INDEX(J49#,,7),INDEX(J49#,,8),INDEX(J49#,,9),INDEX(J49#,,10),LAMBDA(a,b,c,d,e,f,a+b+c+d+e+f)),1))
K34:K44K34=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),LAMBDA(row,SUM(row))),1))
J49:S59J49=J2#
Dynamic array formulas.
Named Ranges
NameRefers ToCells
_FilterDatabase=Test!$J$2:$S$12J49
Click to expand...

Hi Holger,

You were almost there, here's 3 alternative to your problem, the first one seems bigger, but it is the lazy one, it will find the right column for you, the second one you have to count how many column you want to remove. The last one is a mixt of both.
2024-01-22_varcall-m-pop3-parhomo_miss_chi-0.01_5-0.076_13-0.076_..._384-0.434_over-0.436.vcf_df_str_mendel_correction_manuelle.xlsm
JKLMNOPQRS
33longshortAlternative 1Alternative 2Alternative 3
34SumSumSumSumSum
35792,05792,05792,05792,05792,05
361303,951303,951303,951303,951303,95
37667,5667,5667,5667,5667,5
381411,791411,791411,791411,791411,79
392122,282122,282122,282122,282122,28
40667,5667,5667,5667,5667,5
411561,441561,441561,441561,441561,44
421861,791861,791861,791861,791861,79
431411,791411,791411,791411,791411,79
441996,581996,581996,581996,581996,58
45
46
47
48
49ProductInvoiceCol3Col4Col5Col6Col7Col8Col9Col10
50Product A12343216540000Country Origin: UK5,8539,000,0018,680,00728,52
51Product B56786541230000Country Origin: JP9,1561,000,0019,900,001213,90
52Product C43219632541000Country Origin: UK12,00100,000,005,500,00550,00
53Product D87652365987000Country Origin: US9,90100,000,0012,890,001289,00
54Product B56786541230000Country Origin: JP12,38100,000,0019,900,001990,00
55Product C43219632541000Country Origin: US12,00100,000,005,500,00550,00
56Product A12343216540000Country Origin: UK7,7278,000,0018,680,001457,04
57Product A12343216540000Country Origin: UK12,9193,000,0018,680,001737,20
58Product D87652365987000Country Origin: US9,90100,000,0012,890,001289,00
59Product A12343216540000Country Origin: UK9,90100,000,0018,680,001868,00
Feuil3
Cell Formulas
RangeFormula
J34:J44J34=VSTACK("Sum",DROP(MAP(INDEX(J49#,,5),INDEX(J49#,,6),INDEX(J49#,,7),INDEX(J49#,,8),INDEX(J49#,,9),INDEX(J49#,,10),LAMBDA(a,b,c,d,e,f,a+b+c+d+e+f)),1))
K34:K44K34=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),LAMBDA(row,SUM(row))),1))
L34:L44L34=LET(tbl,J49#, colStart,MATCH("Col5",CHOOSEROWS(tbl,1),0), VSTACK("Sum",BYROW(CHOOSECOLS(DROP(tbl,1),SEQUENCE(COLUMNS(tbl)-colStart+1,1,colStart)),LAMBDA(r,SUM(r)))))
M34:M44M34=VSTACK("Sum",BYROW(DROP(J49#,1,4),LAMBDA(row,SUM(row))))
N34:N44N34=VSTACK("Sum",BYROW(DROP(J49#,1,MATCH("Col4",CHOOSEROWS(J49#,1),0)),LAMBDA(row,SUM(row))))
J49:S59J49=J2:S12
Dynamic array formulas.



LETS DIG IN

#1 - lazy one
Excel Formula: 
=LET(tbl,J49#,
colStart,MATCH("Col5",CHOOSEROWS(tbl,1),0),
VSTACK("Sum",BYROW(CHOOSECOLS(DROP(tbl,1),SEQUENCE(COLUMNS(tbl)-colStart+1,1,colStart)),LAMBDA(r,SUM(r)))))

This one use LET to declare one time the table (J49#) and find the column where to start as variable. colStart will find the word of the first column to SUM (in our case "Col5") in a list of element using MATCH function. Our list of element is set to the first row of our table (CHOOSEROWS(tbl,1).

Now that we have a table and we know where to start, we are ready to stack "Sum" and our SUM byrow. How can we choose the column we want, if a table is now in a variable and not in a physical sheet? It is quite simple, MATCH (colStart) return te position of the element in the list, since our list is made of the table first row, the number we got is the equivalent of column number from that table. The little proble with CHOOSECOLS is that you can't write '>x' or '<y' so you have to give one by one the number of the column we want. Fortunately, we have a function that can do that for us and it is SEQUENCE. So we need a sequence from colStart to the last column of tbl (last column - colStart + 1 = the number of column we want to return).

#2 - Smaller but i need to count
Excel Formula: 
=VSTACK("Sum",BYROW(DROP(J49#,1,4),LAMBDA(row,SUM(row))))

This one is more simple, you almost had it, but you putted your DROP function a little bit too late! This one, we take the table (J49#), drop the first row and the 4 first column, then do the sum by row. quick, small, but you always need to count where it start.

#3 - Let's make a deal and put a bit of both
Excel Formula: 
=VSTACK("Sum",BYROW(DROP(J49#,1,MATCH("Col4",CHOOSEROWS(J49#,1),0)),LAMBDA(row,SUM(row))))

This one is the one I would use, instead of counting, why not ask excel to find the LAST column that we don't want? Same as #1, we use MATCH to find the last column that i don't want ("Col4") in the first row of the table. ** PLEASE TAKE NOTE - that MATCH("Col4",CHOOSEROWS(J49#,1),0)) could be change by MATCH("Col5",CHOOSEROWS(J49#,1),0))-1 if you want to find the first row to KEEP.

Hope it helps,

Vincent
 
Upvote 0
Solution
coulombevin said:
Hi Holger,

You were almost there, here's 3 alternative to your problem, the first one seems bigger, but it is the lazy one, it will find the right column for you, the second one you have to count how many column you want to remove. The last one is a mixt of both.
2024-01-22_varcall-m-pop3-parhomo_miss_chi-0.01_5-0.076_13-0.076_..._384-0.434_over-0.436.vcf_df_str_mendel_correction_manuelle.xlsm
JKLMNOPQRS
33longshortAlternative 1Alternative 2Alternative 3
34SumSumSumSumSum
35792,05792,05792,05792,05792,05
361303,951303,951303,951303,951303,95
37667,5667,5667,5667,5667,5
381411,791411,791411,791411,791411,79
392122,282122,282122,282122,282122,28
40667,5667,5667,5667,5667,5
411561,441561,441561,441561,441561,44
421861,791861,791861,791861,791861,79
431411,791411,791411,791411,791411,79
441996,581996,581996,581996,581996,58
45
46
47
48
49ProductInvoiceCol3Col4Col5Col6Col7Col8Col9Col10
50Product A12343216540000Country Origin: UK5,8539,000,0018,680,00728,52
51Product B56786541230000Country Origin: JP9,1561,000,0019,900,001213,90
52Product C43219632541000Country Origin: UK12,00100,000,005,500,00550,00
53Product D87652365987000Country Origin: US9,90100,000,0012,890,001289,00
54Product B56786541230000Country Origin: JP12,38100,000,0019,900,001990,00
55Product C43219632541000Country Origin: US12,00100,000,005,500,00550,00
56Product A12343216540000Country Origin: UK7,7278,000,0018,680,001457,04
57Product A12343216540000Country Origin: UK12,9193,000,0018,680,001737,20
58Product D87652365987000Country Origin: US9,90100,000,0012,890,001289,00
59Product A12343216540000Country Origin: UK9,90100,000,0018,680,001868,00
Feuil3
Cell Formulas
RangeFormula
J34:J44J34=VSTACK("Sum",DROP(MAP(INDEX(J49#,,5),INDEX(J49#,,6),INDEX(J49#,,7),INDEX(J49#,,8),INDEX(J49#,,9),INDEX(J49#,,10),LAMBDA(a,b,c,d,e,f,a+b+c+d+e+f)),1))
K34:K44K34=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),LAMBDA(row,SUM(row))),1))
L34:L44L34=LET(tbl,J49#, colStart,MATCH("Col5",CHOOSEROWS(tbl,1),0), VSTACK("Sum",BYROW(CHOOSECOLS(DROP(tbl,1),SEQUENCE(COLUMNS(tbl)-colStart+1,1,colStart)),LAMBDA(r,SUM(r)))))
M34:M44M34=VSTACK("Sum",BYROW(DROP(J49#,1,4),LAMBDA(row,SUM(row))))
N34:N44N34=VSTACK("Sum",BYROW(DROP(J49#,1,MATCH("Col4",CHOOSEROWS(J49#,1),0)),LAMBDA(row,SUM(row))))
J49:S59J49=J2:S12
Dynamic array formulas.



LETS DIG IN

#1 - lazy one
Excel Formula: 
=LET(tbl,J49#,
colStart,MATCH("Col5",CHOOSEROWS(tbl,1),0),
VSTACK("Sum",BYROW(CHOOSECOLS(DROP(tbl,1),SEQUENCE(COLUMNS(tbl)-colStart+1,1,colStart)),LAMBDA(r,SUM(r)))))

This one use LET to declare one time the table (J49#) and find the column where to start as variable. colStart will find the word of the first column to SUM (in our case "Col5") in a list of element using MATCH function. Our list of element is set to the first row of our table (CHOOSEROWS(tbl,1).

Now that we have a table and we know where to start, we are ready to stack "Sum" and our SUM byrow. How can we choose the column we want, if a table is now in a variable and not in a physical sheet? It is quite simple, MATCH (colStart) return te position of the element in the list, since our list is made of the table first row, the number we got is the equivalent of column number from that table. The little proble with CHOOSECOLS is that you can't write '>x' or '<y' so you have to give one by one the number of the column we want. Fortunately, we have a function that can do that for us and it is SEQUENCE. So we need a sequence from colStart to the last column of tbl (last column - colStart + 1 = the number of column we want to return).

#2 - Smaller but i need to count
Excel Formula: 
=VSTACK("Sum",BYROW(DROP(J49#,1,4),LAMBDA(row,SUM(row))))

This one is more simple, you almost had it, but you putted your DROP function a little bit too late! This one, we take the table (J49#), drop the first row and the 4 first column, then do the sum by row. quick, small, but you always need to count where it start.

#3 - Let's make a deal and put a bit of both
Excel Formula: 
=VSTACK("Sum",BYROW(DROP(J49#,1,MATCH("Col4",CHOOSEROWS(J49#,1),0)),LAMBDA(row,SUM(row))))

This one is the one I would use, instead of counting, why not ask excel to find the LAST column that we don't want? Same as #1, we use MATCH to find the last column that i don't want ("Col4") in the first row of the table. ** PLEASE TAKE NOTE - that MATCH("Col4",CHOOSEROWS(J49#,1),0)) could be change by MATCH("Col5",CHOOSEROWS(J49#,1),0))-1 if you want to find the first row to KEEP.

Hope it helps,

Vincent
Click to expand...
So awesome Vincent! I guess it shows, very much where experience comes in. While I do many small repetitive steps to then apply one of the better-known functions, your expertise knows how to get rid of those repetitive tasks and instead use a formula. It then comes down to "artistically" 😉 combining those pieces and tying them together.

I will have to play with these alternatives around and especially learn to use choosecols&sequence within byrow to pass an array over to lambda.

Thanks for your time and providing so much more to learn.

Cheers

Holger
 
Upvote 0
Another option would be
Excel Formula: 
=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),SUM),1))
 
Upvote 0
Fluff said:
Another option would be
Excel Formula: 
=VSTACK("Sum",DROP(BYROW(FILTER(J49#,COLUMN(J49#)>13,),SUM),1))
Click to expand...
Awesome Fluff, spoilt for choices and this one is very short and concise.

Much appreciated you looking at this as well.

Cheers
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Return and rank top 3 results (including ties) from VSTACK, FILTER, CHOOSECOLS, and SORT formula
Replies
8
Views
482
Peter_SSs
Peter_SSs
P
  • Solved
Lambda and custom sequence with interference of string
Replies
3
Views
337
StephenCrump
S
B
  • Solved
Spilling Total using different ranges & If statement
2
Replies
12
Views
509
bearcub
B
S
  • Solved
Data manipulation with dynamic arrays
Replies
5
Views
133
Fluff
Fluff
JenniferMurphy
  • Solved
Is this the best way to extract the cell address from a FormulaText expression?
Replies
2
Views
265
JenniferMurphy
JenniferMurphy

Forum statistics

Threads
1,226,509
Messages
6,191,450
Members
453,658
Latest member
healmo

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