Matching two matrices in Excel

Mikael

Quite Involved in Discussions
#1
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?

1581348417377.png
 
Elsmar Forum Sponsor

indubioush

Quite Involved in Discussions
#2
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")
 

Mikael

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

John Predmore

Trusted Information Resource
#4
I need to match two matrices to find differences
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:[email protected]@@,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.
 

Mikael

Quite Involved in Discussions
#5
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?
 
Last edited:
Thread starter Similar threads Forum Replies Date
A Attribute Analysis for colour matching scale Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 5
S My Spread Calcs/Results for Xbar R Charts not Matching Minitab's Statistical Analysis Tools, Techniques and SPC 18
1 Matching Supply with Customer Demand - Need help Manufacturing and Related Processes 5
Q Processes Map matching procedures? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
Marc GM stops matching 401(k) payments to all employees? 401(k) savings plans World News 0
K Matching ISO 9001 Clauses/Requirements to my processes ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
M Product Identifiers: 2D Matrices vs. Barcodes EU Medical Device Regulations 5
gard2372 Any websites or matrices to cross reference EN or DIN standards to SAE/ASME etc...? Other ISO and International Standards and European Regulations 3
Marc Various Comparison Matrices - TS 16949 vs. VDA 6.1 IATF 16949 - Automotive Quality Systems Standard 24
M List of FAA Part 145 Repair Stations in MS Excel format? Federal Aviation Administration (FAA) Standards and Requirements 0
V Csv, excel format - audit trail file of HPLC system ( Empower, openlab, EZchrom or any other ) Qualification and Validation (including 21 CFR Part 11) 0
J Incoming Inspection Records using Excel File ISO 13485:2016 - Medical Device Quality Management Systems 2
S 95% UCL - Attaching an excel for recovery study Statistical Analysis Tools, Techniques and SPC 0
T EXCEL - How do I make the cell stay blank until a date is entered in N7? Excel .xls Spreadsheet Templates and Tools 6
Ed Panek Validating Excel (XLS) formulas 21 CFR Part 820 - US FDA Quality System Regulations (QSR) 7
C Template for Excel Validation Reliability Analysis - Predictions, Testing and Standards 6
R Gage R&R Excel templates Gage R&R (GR&R) and MSA (Measurement Systems Analysis) 3
S Issuing of CoC to Customer in a Word or Excel format ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Ajit Basrur Track audit findings on Excel tracker Excel .xls Spreadsheet Templates and Tools 10
J Does anyone have an excel IATF 16949 Internal Audit checklist I could use? IATF 16949 - Automotive Quality Systems Standard 7
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
P Commercial excel templates that calculate overhead, hourly rates, fee proposal Excel .xls Spreadsheet Templates and Tools 0
I Cannot Export multiple sections from Excel to PDF without losing pagination Excel .xls Spreadsheet Templates and Tools 23
J Level 3 KPI Excel Template Manufacturing and Related Processes 1
bobdoering Informational New AIAG PFMEA Process - Excel Template Attached FMEA and Control Plans 23
GreatNate Excel PPAP Tracking templates or tool wanted APQP and PPAP 1
E Part 11 Compliance, Excel living documents (i.e. document master list, equipment list, approved supplier list) Pharmaceuticals (21 CFR Part 210, 21 CFR Part 211 and related Regulations) 3
J ISO 9001:2015 Excel Gap Analysis wanted ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
B AS9110 Audit Check Sheet wanted Excel or Word Document AS9100, IAQG, NADCAP and Aerospace related Standards and Requirements 2
G Excel report with pictures Excel .xls Spreadsheet Templates and Tools 5
A Query regarding MS Excel usage Excel .xls Spreadsheet Templates and Tools 2
A Excel OOT (Out of Tolerance) Formula Excel .xls Spreadsheet Templates and Tools 3
D Asterix when pasting a column of values from Excel into Minitab (V17) Using Minitab Software 5
Y Excel Automation - Printing Word Documents from within Excel Excel .xls Spreadsheet Templates and Tools 0
S Help me build an Excel Spreadsheet for tracking training Training - Internal, External, Online and Distance Learning 14
P Template or example Excel list of ISO certificates issued to clients ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 5
M Medical Device Process validation, Validation of excel spreadsheets used for process Other Medical Device and Orthopedic Related Topics 4
C Excel date format from Denmark to USA Excel .xls Spreadsheet Templates and Tools 6
G ISO 9001:2015 - Is control possible with Excel adaptable measurement sheets? ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 24
N Where I can find an Excel sample/template for recording Delivery Time and Cost Excel .xls Spreadsheet Templates and Tools 2
D How do I go about creating document logs and registers with the MS Excel Excel .xls Spreadsheet Templates and Tools 2
D Microsoft Excel database to Stand-alone software Calibration and Metrology Software and Hardware 3
Q Controlling Multi-Tab Microsoft Excel Forms Excel .xls Spreadsheet Templates and Tools 5
B Zeiss CMM Calypso output to Excel or CSV file Help Inspection, Prints (Drawings), Testing, Sampling and Related Topics 17
S used excel formula calculated the ARL's with Rule1&Rule2 for Shewhart control chart Statistical Analysis Tools, Techniques and SPC 0
N Gantt Chart / Time Tracker in Excel - Combining Tasks on the Same Line Excel .xls Spreadsheet Templates and Tools 2
M Preventive Maintenance Spreadsheet in Excel Quality Assurance and Compliance Software Tools and Solutions 2
1 KPI Dashboard Template in Excel Format wanted Excel .xls Spreadsheet Templates and Tools 9
Ajit Basrur MS Excel to study Correlation between Humidity & Differential Pressure Excel .xls Spreadsheet Templates and Tools 7
D Gage Calibration Tracking in MS Excel Calibration and Metrology Software and Hardware 9

Similar threads

Top Bottom