• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

An array formula, that will take data from two sheets of different ranges

bines53

Active Member
Hello friends,

I am looking for an array formula, that will take data from two different sheets and ranges, and displays them in the third sheet,
Seven of data from sheet A, and one data from sheet B, the eight data displays them in sheet C.


Thank you!
 

Attachments

  • TEST.8.xlsx
    8.7 KB · Views: 0
Hi Asheesh,

Just specify the range of data I want, it sounds simple, but I could not, I tried with the choose function, but it does not get different ranges.

Thank you!
 
Hi David.

Does it matter in what order the returns are given?

Also, how are you looking to generalise this set-up? A "static" solution for the values you posted would be:

=IF(ROWS($1:1)>8,"",INDEX(INDEX(CHOOSE({1,2},A!$A$1:$A$10,B!$A$1:$A$4),N(IF(1,{3,4,5,6,7,8,9,2})),N(IF(1,{1,1,1,1,1,1,1,2}))),ROWS($1:1)))

(No CSE required.)

and copied down, though until I understand what you're trying to do, I'm not sure if this is objectively any better than writing eight individual INDEX statements.

Regards
 
Last edited:
Hi XOR LX,

Happy to hear from you,

I have a function UDF ,FAIR ,
She compares the two ranges, the data which repeated several times,
I want one range will be continuous data, and the second range will be from two different sources.
IN E2 cell, I try this function
=Pair(CHOOSE({1,2,3,4,5,6,7,8},A1,A2,A3,A4,A5,A6,A7,B21),B1:B8)
IN E4 cell, this function WORK
=Pair(A1:A8,B1:B8)
If you like what version that the, FAIR, with Excel functions, I'll add it.

Thank you!
 

Attachments

  • test 8-1.xlsm
    15.3 KB · Views: 1
Last edited:
So did you try my set-up?

Just the part:

INDEX(CHOOSE({1,2},A!$A$1:$A$10,B!$A$1:$A$4),N(IF(1,{3,4,5,6,7,8,9,2})),N(IF(1,{1,1,1,1,1,1,1,2})))

will give you an array containing the values for the 8 references you specified in your previous post, and can be used appropriately in further functions as desired.

Regards
 
Hi XOR LX,

There is a problem in the function fair ,he rejects the {}.

I have a function version of FAIR, with the COUNTIF function, is very long, can you improve it?

Thank you!

David
 

Attachments

  • test 10.xlsx
    9.1 KB · Views: 0
Last edited:
Ah, you mean the range passed to your function must be an actual worksheet range, just like with e.g. COUNTIF and OFFSET?

So you cannot legitimately perform e.g.:

=FAIR({1,2,3})

but you can perform:

=FAIR(A1:A3)

?

Also, can you explain what your function does precisely?

Regards
 
Hi XOR LX,

The last file I attached, I gave an example with the function COUNTIF, version to function FAIR.
The function compares the two ranges, the ranges include repeating numbers, and gives a result, the number of matches.
For example, there are two ranges :

4 ,5,8,8,8,5
5,8,8,6,3,3

The result in this example is 3, are highlighted in color.

Regards
 
I set a challenge a few months back which appears to be quite similar in nature to this, for which I discuss my solution here:

http://excelxor.com/2014/09/18/advanced-formula-challenge-4-results-and-discussion/

(Though I'm still convinced that I must be missing something simpler!)

Anyway, if List1 and List2 are your two vertical ranges, we can adapt that solution to match your requirements, viz (with CSE):

=SUM(0+(ISNUMBER(MATCH(List1+(COUNTIF(OFFSET(INDEX(List1,1,1),Arry1,,Arry2,),List1)/10^6),List2+(COUNTIF(OFFSET(INDEX(List2,1,1),Arry1,,Arry2,),List2)/10^6),0))))

where:

Arry1 is defined as:

=ROW(List1)-MIN(ROW(List1))

and Arry2 as:

=ROWS(List1)-ROW(List1)+MIN(ROW(List1))

just as in that post.

Regards
 
Hi XOR LX,

It's a bit complicated for me, can you give an example?
The offset function is inevitable?

Thank you!

Regards
 
Ok, well without it things are just a touch less concise. The formula would be (with CSE):

=SUM(0+(ISNUMBER(MATCH(List1+(COUNTIF(OFFSET(INDEX(List1,1,1),ROW(List1)-MIN(ROW(List1)),,ROWS(List1)-ROW(List1)+MIN(ROW(List1)),),List1)/10^6),List2+(COUNTIF(OFFSET(INDEX(List2,1,1),ROW(List1)-MIN(ROW(List1)),,ROWS(List1)-ROW(List1)+MIN(ROW(List1)),),List2)/10^6),0))))

in which you simply replace List1 and List2 with your two ranges.

I could probably make a non-CSE version of this if you really want (I recall your dislike for array formulas!), though I'm not sure I could remove the volatility, i.e. OFFSET.

Like I said, I can't quite believe that this matter requires such a complex solution. Perhaps I'm missing something obvious which someone else will come along and point out. Or perhaps your query and my post are not as similar as I thought.

Regards
 
Hi XOR LX,

This is not the correct solution, I thank you for the help, I understand that this is a very difficult task.
I have a feeling that the solution may come rather, the median function.

Regards

David
 
Hi XOR LX,

I found something from the past
=INDEX(MMULT((COUNTIF(A2:F2,A3:F3)>=COUNTIF(OFFSET(A3,,TRANSPOSE(COLUMN(A3:F3)-COLUMN(A3)),1,COLUMN(A3:F3)-COLUMN(A3)+1),A3:F3))+0,TRANSPOSE(COLUMN(A3:F3)^0)),1)

I do not remember who gave this solution, I'll check tomorrow.

Possible here without solution OFFSET ?

Regards

David
 

Attachments

  • test 8-1.xlsm
    15.6 KB · Views: 0
Hi XOR LX,

This is not the correct solution, I thank you for the help, I understand that this is a very difficult task.

With all due respect, I think it's polite to explain why a given solution doesn't work, and not just to say "This is not the correct solution".

Please clarify with some clear examples, indicating both what my formula gives and what the result should be.

Regards
 
Hi XOR LX,


You're absolutely right, I apologize, it was after midnight and very tired, again, I apologize!

I'm not at home at the computer, just come back later.

Regards

David
 
Hi XOR LX ,

I think I will try to explain , on David's behalf ; of course , this is only based on my understanding of his requirements , which may not be correct.

The way I have written the macro to satisfy his requirements , given two lists of numbers , which can be either simple one column multiple row ranges , or where the first list can be a derived array , the objective is to compare the two and return the number of matches , excluding duplicates.

To take an example , suppose the lists were :

4 ; 5 ; 8 ; 3 ; 8 ; 5 ; 9 ; 5

5 ; 8 ; 8 ; 6 ; 3 ; 3 ; 1 ; 1

Of course , the two example lists have the same number of elements , but this is not to be assumed i.e. the two lists can have a differing number of elements.

The above first list can be one of two :

1. A straightforward range A1 : A8

2. A derived array , using the formula :

CHOOSE({1,2,3,4,5,6,7,8},A1,A2,A3,A4,A5,A6,A7,B21)
or
CHOOSE({1;2;3;4;5;6;7;8},A1,A2,A3,A4,A5,A6,A7,B21)

Either way , what the function / formula should do is return the number of matches ; in this case , the matching numbers , excluding duplicates , would be 5 , 8 , 3 , and hence the number of matches would be 3.

Narayan
 
Hi Narayan ,

Yet I have not seen your macro, but the explanation you gave is completely accurate!

Regards

David
 
Hi Narayan ,

4 ; 5 ; 8 ; 3 ; 8 ; 5 ; 9 ; 5

5 ; 8 ;8 ; 6 ; 3 ; 3 ; 1 ; 1

A match of 4 !

Sorry for the mistake before, saying that it is true that there is a match of 3

Regards

David
 
Last edited:
Hi XOR LX ,

I attach the file to your solution,The result should be 3.

Regards

David
 

Attachments

  • test1.xlsx
    8.9 KB · Views: 0
@Narayan

Many thanks for your detailed explanation. Much appreciated. However, this appears to confirm my own understanding of the problem. Can you clarify where the solution I posted fails with respect to your comments?

@bines53

Not sure I understand? Why is the expected result for this latest attachment 3? I can see four values in A1:A8 which also occur in B1:B8 (8, 8, 3 and 5).

Regards
 
To take an example , suppose the lists were :

4 ; 5 ; 8 ; 3 ; 8 ; 5 ; 9 ; 5

5 ; 8 ; 8 ; 6 ; 3 ; 3 ; 1 ; 1

in this case , the matching numbers , excluding duplicates , would be 5 , 8 , 3 , and hence the number of matches would be 3.

Ah, perhaps this is where we differ in our interpretations. Look again at post#9 and David's expected result for that dataset. Or am I missing something?

Regards
 
Back
Top