Comparing and matching a range of data with another range of data

Question

Each row of 4 columns represents a set of data

A1 : D1  1,2,3,4

A2 : D2  4,2,1,5

A3 : D3  5,3,2,1

etc.

Now the column set will be compared to another column set

F1 : I1  4,6,3,1

F2 : I2  4,3,2,1

F3 : I3  2,3,5,1

The set that matches to the other column set will be marked red. So in the example F2:I2 and F3:I3 will be marked red. They just need to contain the same numbers. The order is not important but all numbers should match.

I thought of using conditional statements but can't seem to find a way to compare and match sets of range to other sets of range.

As an alternative, I thought of adding all the columns =A1&" "&A2&" "&A3&" " &A4 then sort it then match it from there but I tried sorting from left to right the sets and it doesn't seem to be sorting right if I do it all at once. The sheet will contain a large amount of rows so that will be a chore to do it 1 by 1.

So I'm out of ideas as I am not that good with excel. :( Appreciate all the help I can get. :) Also, I don't mind learning other languages if it makes things easier. (as long as I can import the data from an excel)

Do the columns need to match or do the rows? How does F2:I2 match to the other column sets? I am unclear as to what needs to match and or how F2:I2 and F3:I3 would meet your criteria. Are you saying the numbers in this row on the right match the numbers in any row on the left? — Scheballs
– Scheballs, Commented May 14, 2013 at 17:35
Yes, the numbers in the row on the right matches the numbers on the left.1 row of 4 columns is 1 combination. So A1:D1 = 1,2,3,4. So if I use what Tim Williams suggested then E1 = 4,3,2,1 combining all the data into one cell in descending order. Would do the same to the other 4 column combinations. Then match them. — user2382310
– user2382310, Commented May 14, 2013 at 23:17

Tim Williams · Accepted Answer · 2013-05-14 16:26:50Z

1

If you want to create a single column to match on then this should do it:

=CONCATENATE(LARGE(A1:D1,1),":",LARGE(A1:D1,2),":",LARGE(A1:D1,3),":",LARGE(A1:D1,4))

Then you can use the regular lookup functions such as MATCH()

answered May 14, 2013 at 16:26

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Comparing and matching a range of data with another range of data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related