Compare each value in a range to each value in another range

[trigo1]

I am looking for a macro that will return a comparison of each cell in
a range to each cell in another range.

example. the first range would have vales of 1,2,3 and the second
range would have values of 5,6,7 Thus the macro should return 9
possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7,
3 & 5, 3 & 6, 3 & 7,

Can anybody help me with this??

 

[Max]

Just hazarding some thoughts here ..
Perhaps using formulas would suffice ?
Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then

Compare 1st range against 2nd range
In C1:
=IF(ISNUMBER(MATCH(A1,$B$1:$B$3,0)),"Y","")
Copy C1 down to the last row of data in the 1st range, ie to C3. This
returns the comparison results: Y, if item in 1st range is found in the 2nd
range, blank: "" if not found

Compare 2nd range against 1st range
In D1:
=IF(ISNUMBER(MATCH(B1,$A$1:$A$3,0)),"Y","")
Copy D1 down to the last row of data in the 2nd range, ie to D3. This
returns the comparison results: Y, if item in 2nd range is found in the 1st
range, blank: "" if not found

 

[Dave Peterson]

If the ranges are vertical--A1:A3 and B1:B6, you could use an array formula
like:

=SUM(--(A1:A3=TRANSPOSE(B1:B6)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[trigo1]

Hey guys thanks for the quick response....I tryied both recomondations
and both were able to compare the data i needed. However how can i get
excel to return each possible comparison i.e
1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 &
7, from the two rages of 1,2,3 and the second of 5,6,7 ??

Ultimatly the end result would be a list of all the possible
combonations.

thanks again

 

[Dave Peterson]

Try this post from Myrna Larson:
http://groups.google.com/group/microsoft.public.excel.misc/msg/2150eee92452c83c?output=gplain

or
http://snipurl.com/wzbn

 

[Guest]

Here's a way to generate the combinations from column data ..

Assume a source of 3 variables per col within 6 cols in Sheet1's A1:F3, viz:
1 10 21 34 40 11
3 14 23 37 42 13
4 17 28 38 43 18
(all 18 numbers are assumed unique)

and we want to "generate" the above into a total of:
3^6 = 729 combinations in a final output Sheet4, ie:

1-10-21-34-40-11 (< in A1)
1-10-21-34-40-13
1-10-21-34-40-18
....
....
4-17-28-38-43-11
4-17-28-38-43-13
4-17-28-38-43-18 (< in A729)

Steps:

In Sheet2
-------------
Put in:
A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/3),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,3),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/3),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,3),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/3),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,3),)

Select A1:F1, copy down to F9

In Sheet3
-------------
Put in A1
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/9),)

Put in B1
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),1)

Put in C1
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),1)

Select A1:C1, copy down to C81

In Sheet4
------------
Put in A1
=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,9),)

Copy A1 down to A729. This will list all 729 (3^6) combinations from the
source data in Sheet1's A1:F3.

Adapt to suit .. The method limits a max extension for the source data
to 6 var per col in 6 cols (in Sheet1's A1:F6) which'll generate 6^6 = 46656
combinations in Sheet4 (as 7^6 = 117649, which exceeds Excel's max 65536
rows).

---

Hey guys thanks for the quick response....I tryied both recomondations
and both were able to compare the data i needed. However how can i get
excel to return each possible comparison i.e
1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 &
7, from the two rages of 1,2,3 and the second of 5,6,7 ??

Ultimatly the end result would be a list of all the possible
combonations.

Orig. post:

 

[Guest]

Here's a sample construct:
http://www.savefile.com/files/101884
Generating combinations from column data.xls


---