Matching two matrices in Excel

Hi

I need to match two matrices to find differences. This is just a little example from Excel, in the real case I can have like more than 200 rows(/types) and columns can be like 30. They don't have the same order, though they could be sorted it is a problem with the rows/types since they can be missing. I would like to make it work without reordering everything until it gets updated. I guess I need a formular I can just drag down for all rows.
I have tried with the usual vlookup(), match() and some combinations, but it looks like I end with a manual approach anyway or might end up using another program. If I reorder, align and sort it, no problem, but otherwise is there a smart solution in Excel to get the feedback for differences?

The IF formula should work for you. If you have data in two different Excel files, you will need to remove the "$" symbol from your formula prior to copying it over and down.

Use something like this:

=IF(A1=[Book2]Sheet1!A1,"","BAD")

Thank you, but it will only work if the matrices are symmetrical. The point is that sometimes a row can be missing.

Mikael said:

I need to match two matrices to find differences

Click to expand...

From your example, it looks like there are distinct columns, the order of columns may be scrambled, but columns are uniquely identifiable by headers. I did a similar problem some time back, and I found a simple approach using the MATCH function. To illustrate, I begin with the simple case of two lists, which could be different lengths. I inserted a confirmation column containing the formula MATCH(A1,$D$1:$D$###,0) copied down the length of list A. (The zero option at the end specifies an exact match option.) This formula calculates as an integer which indicates the location from the top of list B for the value in A1. Then I put the mirror image formula in the column next to list B - MATCH(E1,$A$1:$A$@@@,0) - and copy this alongside list B. If there is an element in list A which does not appear in list B, the MATCH formula returns the value N/A. Now you can filter the list, or sort, to see the N/A elements in each list which do not have a match in the other list. Duplicate values in a list are ignored in this method. One enhancement is to use IFNA to convert N/A values to zeros and SIGN() to convert positive integers to positive 1, so you can SUM or COUNTIF the column for tally counts of matches and no-matches.

The MATCH function also works with Excel Tables, with absolute address converting to Table column references based on column headers, and that might be a faster way to process large matrices with many columns.

Thank you, yes column-NAMES are unique. The approach only helps me to check whether the same types are there? I can do that with Power Query too

If types are in both matrix I need know if the entire row are similar. I don´'t think I can do this with match() or did I miss something?

FYI: I went to Power BI instead much easier