Extracting a Dynamic Sorted List from the Result of a Dynamic Formula (Non-VBA)

[LearnExcl]

Hello,

I need your assistance.

The range below in Sheet 3 is a result of a dynamic formula which extracts a unique list from two separate columns from Sheet 1 and Sheet 2. Since it is dynamic, the formula extends below the current range reflected in the below illustration to capture any additions to the raw data housed in Sheet 1 and Sheet 2. So, although there are alphanumeric data reflected only in A2:A20 below, A21:A1000 also houses formulas with no results. What I would like to do is to extract a dynamic sorted list from A2:A1000 and place it in B2:B1000 on Sheet 3 through another dynamic formula. I have tried many different formulas which would normally work with pure data, but not with result of another dynamic unique list extracted from other sources. Any and all help will truly be appreciated.

Sheet 3

Code List

1N433

3F401

4M112

03M21

067Y4

44433

54332

74421

03C0D

023K3

067A9A

090F3

290F3

33C0D

47G30

4J421

4S401

5R031

6G033

<colgroup><col></colgroup><tbody>
</tbody>

 

[Andrew Fergus]

Hi

This can definitely be done but we will first need to know what is contained in cells A21:A1000.

What is the default value per your dynamic formula if the row on Sheet 2 is not needed?

Are you able to share that part of the formula?

Andrew

 

[LearnExcl]

Hi

This can definitely be done but we will first need to know what is contained in cells A21:A1000.

What is the default value per your dynamic formula if the row on Sheet 2 is not needed?

Are you able to share that part of the formula?

Andrew

Andrew,

Thank you very much for your interest in this excel question of mine. As I mentioned, the range A2:A1000 on Sheet 3, feeds off of two separate columns via an array formula and when there is not a value to extract from either Sheets (1 and 2), it evaluates to blank. The formula, wrapped with Iferror, involves various Index, row and other formula command combinations. If you are interested in viewing the actual formula, I can post it as well.

Again, I very much appreciate your assistance in advance.

 

[Andrew Fergus]

when there is not a value to extract from either Sheets (1 and 2), it evaluates to blank.

It is this part I would like to see. Is it a space? A double ""? A null value? Given this value will be part of the formula it would be good to see the actual formula to know what is contained in the bottom-most rows. The risk is that I provide a solution and it doesn't work because of what is in the the cells blanks so if you could share the formula that would be great.

 

[LearnExcl]

I am sorry for the late reply. Here is the formula: {=IF(IFERROR(INDEX(Sheet1!$A$1:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$1:$A$1000),0),0)),INDEX(Sheet2!$A$1:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet2!$A$1:$A$1000),0),0)))=0,"",IFERROR(INDEX(Sheet1!$A$1:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet1!$A$1:$A$1000),0),0)),INDEX(Sheet2!$A$1:$A$1000,MATCH(0,INDEX(COUNTIF($A$1:A1,Sheet2!$A$1:$A$1000),0),0))))}

What would actually be a better assistance would be to figure out a way to extract a dynamically sorted unique list (A to Z) from Sheet 1 and Sheet 2 (via a non-VBA formula), instead of doing it in two steps, as I am trying to accomplish currently. Not sure if it is doable, but if it is not, then doing it in two steps would also help me out.

I truly appreciate your assistance.

 

[Andrew Fergus]

Hello

This cannot be done with a one step solution without VBA given the sequence is not known until the full list has been generated.

The two step solution requires a helper column as follows:

In cell B2 enter the following formula:
=COUNTIF(A$2:A$1000,"<="&A2)-IF(ISTEXT(A2),COUNTIF(A$2:A$1000,"")-COUNT(A$2:A$1000))
and copy this down to B1000. This gives you the dynamic sort sequence.

In cell C2 enter the following formula:
=IF(B2>0,INDEX(A$2:A$1000,MATCH(ROW(A1),B$2:B$1000,0)),"")
and copy down to C1000. This gives you the dynamically sorted list.

I trust this helps.
Andrew