Help on how to automate this formula using Alteryx

=IFERROR(SMALL(IF('Fact'!$G$2:$G$19607=1,'Fact'!$AB$2:$AB$10000),ROW()-2),"")

This formula is for a column and this is the excel formula breakdown:
The formula finds the nth smallest value in column AB where the corresponding value in column G is 1, and handles errors by returning an empty string if no such value exists.

Please check if this formulas workflow is correct? I don't have the data to check its validity, so I would really like someone's help to know if it is correct. Or suggest changes or better solution if possible.

1. Input Data:

Use an Input Data tool to bring in your dataset from the "Fact" sheet, including columns G and AB.
2. Filter Tool:

Use a Filter Tool to apply the condition specified in the formula.
Filter Expression: [G] = 1
This filter will retain rows where column G is 1.
3. Sort Tool:

Use a Sort Tool to sort the filtered data by column AB in ascending order.
This step organizes the data to facilitate finding the nth smallest value.
4. Sample Tool:

Use a Sample Tool to select the nth row from the sorted data.
Configuration: Set the tool to skip the first n-1 rows and take the next row.
5. Formula Tool for Error Handling:

Add a Formula Tool to handle cases where the nth value does not exist.

Formula Logic:
IF ISNULL([AB]) THEN "" ELSE [AB] ENDIF

This formula assigns an empty string if the nth value is not found.

Common Use Cases

Accepted answers

Qiu

@kunalkhanna1132
I add some notation but not much.
I believe if we fully understand the Excel function itself then the workflow is quite self-explained.

Below is what I got from the ChatGPT about the Excel Formula.

1. 'Fact'!$A$2:$A$19607=1
This checks if each value in the range A2:A19607 of the 'Fact' sheet is equal to 1.

The result is an array of TRUE or FALSE values of the same size.

2. IF('Fact'!$A$2:$A$19607=1,'Fact'!$B$2:$B$10000)
This is an array formula (typically entered with Ctrl + Shift + Enter in older versions of Excel).

For every row where the corresponding value in column A is 1, it returns the value in the same row from column B (B2:B10000).

If the value in column A &ne; 1, it returns FALSE or blank.

<span class="lia-unicode-emoji" title=":warning:">&#9888;&#65039;</span> Issue to note:
The range sizes don&rsquo;t match exactly:

'Fact'!$A$2:$A$19607 is 19,606 rows.

'Fact'!$B$2:$B$10000 is 9,999 rows.
This can cause errors or unexpected behavior in array formulas. Ideally, both ranges should be the same length.

3. SMALL(..., ROW()-2)
The SMALL function returns the k-th smallest number from the array returned by the IF.

ROW()-2 gives a dynamic value depending on which row the formula is in.

If the formula is in row 3, ROW()-2 = 1 &rarr; returns the smallest.

If it's in row 4, ROW()-2 = 2 &rarr; returns the 2nd smallest, etc.

This effectively helps create a list of the smallest values matching the filter condition.

4. IFERROR(...,"")
If there&rsquo;s an error (e.g. not enough matching values), it returns an empty string "" instead of showing an error.

0522-kunalkhanna1132-A-r1.yxzp

All comments

Qiu

@kunalkhanna1132
Please kindly provide a sample set of date including input and desired output.

kunalkhanna1132

Hey @Qiu, Sorry! I thought you were replying to another query of mine. I don't have any dataset for this but I can describe the input. Column G should have either 0 or 1, Column AB should have string output like"0105114" or "-25447". I would be really thankful if you can tell me f this workflow would be good or any changes should be made.

Raj

@kunalkhanna1132it will be very difficult to understand the logic you are looking for
please provide some sample data
will be able to help.

Qiu

@kunalkhanna1132
I did search about the Excel function used, such as SMALL(), ROW() 😁
Kindly check this is the one you need.

0522-kunalkhanna1132-A.yxzp

kunalkhanna1132

Thanks @Qiu! I will check and get back to you regarding this. Once again thank you!

kunalkhanna1132

Hey @Raj I will try. Thanks!

kunalkhanna1132

Hey @Qiu Can you explain the logic behind the workflow if possible? Im finding it a little hard to understand it.

Qiu

@kunalkhanna1132
I add some notation but not much.
I believe if we fully understand the Excel function itself then the workflow is quite self-explained.

Below is what I got from the ChatGPT about the Excel Formula.

1. 'Fact'!$A$2:$A$19607=1
This checks if each value in the range A2:A19607 of the 'Fact' sheet is equal to 1.

The result is an array of TRUE or FALSE values of the same size.

2. IF('Fact'!$A$2:$A$19607=1,'Fact'!$B$2:$B$10000)
This is an array formula (typically entered with Ctrl + Shift + Enter in older versions of Excel).

For every row where the corresponding value in column A is 1, it returns the value in the same row from column B (B2:B10000).

If the value in column A &ne; 1, it returns FALSE or blank.

<span class="lia-unicode-emoji" title=":warning:">&#9888;&#65039;</span> Issue to note:
The range sizes don&rsquo;t match exactly:

'Fact'!$A$2:$A$19607 is 19,606 rows.

'Fact'!$B$2:$B$10000 is 9,999 rows.
This can cause errors or unexpected behavior in array formulas. Ideally, both ranges should be the same length.

3. SMALL(..., ROW()-2)
The SMALL function returns the k-th smallest number from the array returned by the IF.

ROW()-2 gives a dynamic value depending on which row the formula is in.

If the formula is in row 3, ROW()-2 = 1 &rarr; returns the smallest.

If it's in row 4, ROW()-2 = 2 &rarr; returns the 2nd smallest, etc.

This effectively helps create a list of the smallest values matching the filter condition.

4. IFERROR(...,"")
If there&rsquo;s an error (e.g. not enough matching values), it returns an empty string "" instead of showing an error.

0522-kunalkhanna1132-A-r1.yxzp

kunalkhanna1132

Thanks @Qiu !

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703