Generate number by using Dynamic array w o VBA

0 votes

From the raw data, I want to create a number based on the Name in A1# and check it against B1# and C1# for each row up to the maximum value of D1#. Each row must include 0 up to the value of D; if C is more than D, do not add 0.

All raw data are Array

I use Excel 365

Example image

Can anyone help?

Feb 10, 2023 in Others by Kithuzzz
• 38,010 points
253 views

1 answer to this question.

0 votes

Try:

enter image description here

The formula in F2:

=IFERROR(REDUCE(HSTACK("Name",SEQUENCE(,MAX(D2#))),UNIQUE(A2#),LAMBDA(a,b,LET(c,BYROW(HSTACK(C2#,D2#),LAMBDA(d,MIN(d))),e,FILTER(HSTACK(B2#,B2#&"-"&c,D2#),A2#=b),VSTACK(a,HSTACK(b,XLOOKUP(SEQUENCE(,MAX(DROP(e,,2)))-1,TAKE(e,,1),INDEX(e,,2),0,1)))))),"")

If you really happen to have 4 dynamic arrays as input with headers, then maybe 'drop' those:

=IFERROR(REDUCE(HSTACK("Name",SEQUENCE(,MAX(DROP(D1#,1)))),UNIQUE(DROP(A1#,1)),LAMBDA(a,b,LET(c,BYROW(HSTACK(DROP(C1#,1),DROP(D1#,1)),LAMBDA(d,MIN(d))),e,FILTER(HSTACK(DROP(B1#,1),DROP(B1#,1)&"-"&c,DROP(D1#,1)),DROP(A1#,1)=b),VSTACK(a,HSTACK(b,XLOOKUP(SEQUENCE(,MAX(DROP(e,,2)))-1,TAKE(e,,1),INDEX(e,,2),0,1)))))),"")

But best to ditch these prior to your original formulae.

answered Feb 10, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Type mismatch error when referring to array element by location vba

We utilise MID to parse the string. ...READ MORE

answered Feb 11, 2023 in Others by Kithuzzz
• 38,010 points
629 views
0 votes
1 answer

How to concatenate elements of a single-dimensional array using VBA?

Using Microsoft 365's UNIQUE and SORT in VBA ' This is a ...READ MORE

answered Feb 16, 2023 in Others by narikkadan
• 63,720 points
483 views
0 votes
1 answer

VBA - Build an Array by Looping Through Another Array with Specific Criteria

Please attempt the next option. It is ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,720 points
450 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
833 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,683 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,720 points
1,520 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,720 points
1,137 views
0 votes
1 answer

Generate VCards from Excel using VBA

Solution  Create a class called CContact with getters ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 63,720 points
643 views
0 votes
1 answer

Excel VBA - Out of memory when create a dynamic array formula

Try this: Sub MyArray() Range("A1").Formula2 = "=R[2]C[2]:R[2]C[6]" End Sub But that ...READ MORE

answered Feb 9, 2023 in Others by narikkadan
• 63,720 points
620 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP