comparing Multiple Arrays Generated with Offset

[JCV]

Hi All,

I would like to generate several arrays with offset and compare them to 1 array.
All of the arrays are 6 columns wide and all are n a single row.
I am able to generate the arrays for comparison but am unable to compare them all together.
A sample workbook is enclosed.
I seem to recall using the subtotal function to work with offset and an array as a variable.
Any help and feedback is much appreciated.
Thanks john

 

[pecoflyer]

Cross post https://www.excelforum.com/excel-fo...008-comparing-several-arrays-with-offset.html

 

[vletm]

JCV
You should reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

There are clear sentences about Cross-Posting.

 

[Peter Bartholomew]

Your profile says you have 365, so using 365 methods,

calculates there to be 2 distinct block sequences, and then identifies the block grouping.

= LET(
  k,     SEQUENCE(4,6),
  stack, INDEX(data, k),
  ROWS(UNIQUE(stack)))
 
 
= LET(
  k, SEQUENCE(4,6),
  stack, INDEX(data, k),
  distinct, UNIQUE(stack),
  list, BYROW(distinct,
    LAMBDA(seq, TEXTJOIN(", ",,IF( BYROW(stack, LAMBDA(s, AND(seq=s))), {1;2;3;4}, ""))
   )),
  TRANSPOSE("{"&list&"}"))

 

[JCV]

Hi Peter,

Thanks for your reply, much appreciated.
Unfortunately your elegant solution is beyond my level of excel capabilities.
I have found a simple solution using several rows and columns, cumbersome but works.
Thank you for taking the time to assist me I appreciate your efforts.
Kindest regards John

 

[Peter Bartholomew]

Then now may be the time to learn something new! If not you, maybe someone else that reads this thread.
First type
= SEQUENCE(4, 6)
within a worksheet.

Using a Defined Name 'Data' to refer to
=Sheet1!$G$7:$AD$7

and the INDEX function , look up the terms from your data blocks
= INDEX(data, SEQUENCE(4, 6))
You should now see your blocks neatly stacked!

The new(ish) dynamic array function UNIQUE then identifies the distinct sequences
= UNIQUE(INDEX(data, SEQUENCE(4, 6)))

All ROWS does is tell you that there are two distinct sequences.
= ROWS(UNIQUE(INDEX(data, SEQUENCE(4, 6))))

Easy?

p.s. I use LET to provide a bit more structure to the formula, but it works without.

 

[JCV]

Hi Peter,

Thank you for taking the time to explain your formula.
Sequence(4,6) i see generates an array of 4 rows by 6 columns and fills the results with 1 to 24.
Index(Data , Sequence(4,6)) am not understanding, I thought index requires row and column offset so how is sequence being interpreted?
In any case I can see that the index formula returns an array of my 4 blocks with my data.
Then the Unique function, provide the unique arrays by block, in this case 2.
When adding rows it tells me the number of unique arrays with this formula ROWS(UNIQUE(INDEX(data,SEQUENCE(4,6))))
In my example this gives 2 and that tells me that 1 of the blocks is not with the same data.
I can now add to the aforementioned formula if(1=ROWS(UNIQUE(INDEX(data,SEQUENCE(4,6)))),"OK", "Check "&ROWS(UNIQUE(INDEX(data,SEQUENCE(4,6)))))
I will now expand upon this formula as a foundation and adopt it more precisely for what I am trying to do.
Basically it requires C columns as a variable and B blocks as a variable, which I should be able to accomplish on my own.
I would never have discovered these functions without your kind assistance, very clever indeed.
Peter I really appreciate you taking the time to explain this to me, thank you very much.
Kindest regards John

 

[Peter Bartholomew]

Your understanding is correct. The original range 'data' is a single row so the Excel INDEX function allows [row_num] parameter to be omitted. Otherwise, just provide two parameters but set the first one to 1 or 0,

= UNIQUE(INDEX(data, 1, SEQUENCE(4, 6)))

If you have multiple rows of data, the present formula will fail when it is applied to all the rows as a single block. Then one falls back to defining 'data' to be a row-relative reference to a single row.

There is an alternative solution using the latest release of 365, but that is somewhat more advanced in terms of its programming:

= BYROW(data, LAMBDA(d, ROWS(UNIQUE(INDEX(d, 1, SEQUENCE(4, 6))))))

The original formula has been wrapped within LAMBDA, turning it into a function that will accept a range as its parameter 'd'. The helper function BYROW splits a 2D range 'data' in to rows and feeds them into the function one 'd' at a time, collecting the results as an array. I realise this is a pretty big step in terms of its programming complexity but it is now part of Excel and provides the basis for building far more general solutions.

 

[JCV]

Thanks Peter. Will have to study this one in more detail. But for the time being, i learned something new. Much appreciated, regards john