CODEWITHSUNDEEP
excelcomparematchrange
user2382310
user2382310

Reputation: 11

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

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)

Upvotes: 1

Views: 1404

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

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()

Upvotes: 1

Related Questions