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

Can anyone help?

Feb 10, 2023 in Others 253 views

## 1 answer to this question.

Try:

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.

• 63,720 points

## In Excel Sheet how to Eliminate or Remove, Filter and copy the selected records defined in another sheet using dynamic array list (VBA Module)

I'm attempting to generate an Excel sheet ...READ MORE

## Type mismatch error when referring to array element by location vba

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

## 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

## Convert Rows to Columns with values in Excel using custom format

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

## Remove formulas from all worksheets in Excel using VBA

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

## Calculate monthly average from daily data without PivotTable

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

## Automate compound annual growth rate (CAGR) calculation

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