|
Formula help with a match question
Hello,
I need help with match formulas that will return discrepancies between two reports. I would like a third table that will return the account number when data from one of the adjacent columns do not match. Although 1183-06 matches, some of them do not. I gave you a sampling of the spreadsheet, there are several dozens rows. For example: Report One: . ..577 386-05..... 1 .... 1 ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Report Two: . ..577 386-05..... 1 .... ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Return . ..577 386-05..... .... 1 ..... .... .... ...... The result will be a third table that shows me that 386-05 did not have a count for column 325. I hope this example helps. and thanks so much. Jim |
Formula help with a match question
Assuming the below
1) Report 1 in Sheet1 A1:J100 2) Report 2 in Sheet2 A1:J100 3) Sheet3 has the header fields in Row1 and Column 1 use this formula in Sheet3 B2 which will return a 1 if there is a mismatch between Sheet1 and Sheet2 for the corresponding headers.. =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sh eet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),M ATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) Alternatively, if you have the headers in the same cell location in all sheets you can try out this formula in Sheet3 B2 =IF(Sheett1!B2=Sheet2!B2,"",1) If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: Hello, I need help with match formulas that will return discrepancies between two reports. I would like a third table that will return the account number when data from one of the adjacent columns do not match. Although 1183-06 matches, some of them do not. I gave you a sampling of the spreadsheet, there are several dozens rows. For example: Report One: . ..577 386-05..... 1 .... 1 ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Report Two: . ..577 386-05..... 1 .... ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Return . ..577 386-05..... .... 1 ..... .... .... ...... The result will be a third table that shows me that 386-05 did not have a count for column 325. I hope this example helps. and thanks so much. Jim |
Formula help with a match question
Jacob,
Thanks for the help. I especially like: =IF(Sheett1!B2=Sheet2!B2,"",1) I would never had thought of this. The primary formula you provided appears to be two formula's and I need help with how to use each. The way I read your note it sounds like your saying the formula is one: =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0)) =INDEX(Sheet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A $100,0),MATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) Thanks for the clarification "Jacob Skaria" wrote: Assuming the below 1) Report 1 in Sheet1 A1:J100 2) Report 2 in Sheet2 A1:J100 3) Sheet3 has the header fields in Row1 and Column 1 use this formula in Sheet3 B2 which will return a 1 if there is a mismatch between Sheet1 and Sheet2 for the corresponding headers.. =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sh eet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),M ATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) Alternatively, if you have the headers in the same cell location in all sheets you can try out this formula in Sheet3 B2 =IF(Sheett1!B2=Sheet2!B2,"",1) If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: Hello, I need help with match formulas that will return discrepancies between two reports. I would like a third table that will return the account number when data from one of the adjacent columns do not match. Although 1183-06 matches, some of them do not. I gave you a sampling of the spreadsheet, there are several dozens rows. For example: Report One: . ..577 386-05..... 1 .... 1 ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Report Two: . ..577 386-05..... 1 .... ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Return . ..577 386-05..... .... 1 ..... .... .... ...... The result will be a third table that shows me that 386-05 did not have a count for column 325. I hope this example helps. and thanks so much. Jim |
Formula help with a match question
Dear Jim
It is a single formula. Please find the explanasion. I am not good at explaining things. However will give it a try =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sh eet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),M ATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) MATCH($A2,Sheet1!$A$1:$A$100,0) will try and match the header value in Col A with the Col A header value of Sheet1 and returns the row number MATCH(B$1,Sheet1!$A$1:$J$1,0) will try and match the header value in Row1 with the header value in Sheet1 and returns the column number INDEX(Sheet1!$A$1:$J$100,Row,Column) will then return the value of the corresponding row and column Similarly for Sheet2. These values will be compared....and result displayed using IF condition IF Sheet1value=Sheet2value then Blank else One the = equal sign in between the INDEX functions is the one you are referring to as two formulas. It is not it is just one formula. Hope the above helps.... If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: Jacob, Thanks for the help. I especially like: =IF(Sheett1!B2=Sheet2!B2,"",1) I would never had thought of this. The primary formula you provided appears to be two formula's and I need help with how to use each. The way I read your note it sounds like your saying the formula is one: =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0)) =INDEX(Sheet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A $100,0),MATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) Thanks for the clarification "Jacob Skaria" wrote: Assuming the below 1) Report 1 in Sheet1 A1:J100 2) Report 2 in Sheet2 A1:J100 3) Sheet3 has the header fields in Row1 and Column 1 use this formula in Sheet3 B2 which will return a 1 if there is a mismatch between Sheet1 and Sheet2 for the corresponding headers.. =IF(INDEX(Sheet1!$A$1:$J$100,MATCH($A2,Sheet1!$A$1 :$A$100,0),MATCH(B$1,Sheet1!$A$1:$J$1,0))=INDEX(Sh eet2!$A$1:$J$100,MATCH($A2,Sheet2!$A$1:$A$100,0),M ATCH(B$1,Sheet2!$A$1:$J$1,0)),"",1) Alternatively, if you have the headers in the same cell location in all sheets you can try out this formula in Sheet3 B2 =IF(Sheett1!B2=Sheet2!B2,"",1) If this post helps click Yes --------------- Jacob Skaria "Jim" wrote: Hello, I need help with match formulas that will return discrepancies between two reports. I would like a third table that will return the account number when data from one of the adjacent columns do not match. Although 1183-06 matches, some of them do not. I gave you a sampling of the spreadsheet, there are several dozens rows. For example: Report One: . ..577 386-05..... 1 .... 1 ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Report Two: . ..577 386-05..... 1 .... ..... .... .... 1 ...... 455-05..... 1 .... 1 ..... .... .... 1 ...... Return . ..577 386-05..... .... 1 ..... .... .... ...... The result will be a third table that shows me that 386-05 did not have a count for column 325. I hope this example helps. and thanks so much. Jim |
| All times are GMT +1. The time now is 03:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com