I have 2 spreadsheets that I need to compare. I need to run a macro that compares data sets in both spreadsheet and then outputs the differences in a new book.
It needs to check that that line PART NUMBER exists in both files, and that the QUANTITY matches. If there are differences, it should log those to the new sheet, along with a note of which spreadsheet is missing the data, or has different quantities.
How can I do this?
Spreadsheet 1
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[/tr]
[tr]
[td]PART 1[/td]
[td]2[/td]
[td]FIRST PART SAMPLE[/td]
[td]THIS IS A COMMENT[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]5[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[/table]
Spreadsheet 2
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[/tr]
[tr]
[td]PART 1[/td]
[td]2[/td]
[td]FIRST PART SAMPLE[/td]
[td]THIS IS A COMMENT[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]15[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[tr]
[td]PART 3[/td]
[td]10[/td]
[td]3RD PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[/table]
Output would be ONLY items that are different between the two spreadsheets. Something like this :
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY SHEET 1[/td]
[td]QUANTITY SHEET 2[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[td]PROBLEM[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]5[/td]
[td]15[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[td]DIFFERENT QUANTITY[/td]
[/tr]
[tr]
[td]PART 3[/td]
[td]0[/td]
[td]10[/td]
[td]3RD PART[/td]
[td]ANOTHER COMMENT[/td]
[td]MISSING FROM SHEET 1[/td]
[/tr]
[/tr]
[/table]
It needs to check that that line PART NUMBER exists in both files, and that the QUANTITY matches. If there are differences, it should log those to the new sheet, along with a note of which spreadsheet is missing the data, or has different quantities.
How can I do this?
Spreadsheet 1
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[/tr]
[tr]
[td]PART 1[/td]
[td]2[/td]
[td]FIRST PART SAMPLE[/td]
[td]THIS IS A COMMENT[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]5[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[/table]
Spreadsheet 2
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[/tr]
[tr]
[td]PART 1[/td]
[td]2[/td]
[td]FIRST PART SAMPLE[/td]
[td]THIS IS A COMMENT[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]15[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[tr]
[td]PART 3[/td]
[td]10[/td]
[td]3RD PART[/td]
[td]ANOTHER COMMENT[/td]
[/tr]
[/table]
Output would be ONLY items that are different between the two spreadsheets. Something like this :
[table="width: 500, class: grid, align: center"]
[tr]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[/tr]
[tr]
[td]PART NUMBER[/td]
[td]QUANTITY SHEET 1[/td]
[td]QUANTITY SHEET 2[/td]
[td]DESCRIPTION[/td]
[td]COMMENT[/td]
[td]PROBLEM[/td]
[/tr]
[tr]
[td]PART 2[/td]
[td]5[/td]
[td]15[/td]
[td]SECOND PART[/td]
[td]ANOTHER COMMENT[/td]
[td]DIFFERENT QUANTITY[/td]
[/tr]
[tr]
[td]PART 3[/td]
[td]0[/td]
[td]10[/td]
[td]3RD PART[/td]
[td]ANOTHER COMMENT[/td]
[td]MISSING FROM SHEET 1[/td]
[/tr]
[/tr]
[/table]