FrozenPeas
New Member
- Joined
- Sep 19, 2011
- Messages
- 1
Hi all, I was hoping you could help me with a problem.
I have a spreadsheet that is made up of 3 tabs, Original, Match and Difference. It's purpose is to return the difference between 2 pasted reports (One is pasted in the Original Tab and the other in the Match Tab and the differences are shown in the Difference Tab).
I currently use this formula to accomplish this =IF(Original!W11=Match!W11,"Match",Original!W11-Match!W11) which does it's job well and accurately reports the differences across the 17 columns.
However it happens often that there is a mismatch between the Original and Match tabs because of an extra record that does not appear in both sheets, this of course creates a string of mismatches.
What I would like the spreadsheet to do is look at the ID field, if the ID's match then I would like it to continue to show the differences as it is currently. If the ID's don't match I would like it to show the ID field that does not match on it's own in the differences spreadsheet.
I can do these 2 operations fine, what I can't solve is that when they ID fields don't match the Differences spreadsheet needs to look at the ID that didn't match as it may then match the one below. I think this could be achieved by a loop but I can't get my head around it.
Example of what I would like it to do.
ORG MAT DIFF
123 123 Match
1234 5678 1234
5678 9123 Match
(Because the 2nd row didn't match the 3rd row looks at the 2nd row to check for a match).
I hope that I have explained this clearly enough to understand, if further clarification is needed please ask below and I will do my best to provide you with the information.
At the very least, thank you for reading this post, I hope you can help me.
I have a spreadsheet that is made up of 3 tabs, Original, Match and Difference. It's purpose is to return the difference between 2 pasted reports (One is pasted in the Original Tab and the other in the Match Tab and the differences are shown in the Difference Tab).
I currently use this formula to accomplish this =IF(Original!W11=Match!W11,"Match",Original!W11-Match!W11) which does it's job well and accurately reports the differences across the 17 columns.
However it happens often that there is a mismatch between the Original and Match tabs because of an extra record that does not appear in both sheets, this of course creates a string of mismatches.
What I would like the spreadsheet to do is look at the ID field, if the ID's match then I would like it to continue to show the differences as it is currently. If the ID's don't match I would like it to show the ID field that does not match on it's own in the differences spreadsheet.
I can do these 2 operations fine, what I can't solve is that when they ID fields don't match the Differences spreadsheet needs to look at the ID that didn't match as it may then match the one below. I think this could be achieved by a loop but I can't get my head around it.
Example of what I would like it to do.
ORG MAT DIFF
123 123 Match
1234 5678 1234
5678 9123 Match
(Because the 2nd row didn't match the 3rd row looks at the 2nd row to check for a match).
I hope that I have explained this clearly enough to understand, if further clarification is needed please ask below and I will do my best to provide you with the information.
At the very least, thank you for reading this post, I hope you can help me.
