Compare two worksheets and send the row that contain the difference to a new sheet

[mklindquist0815]

I'm wondering if anyone has VBA to compare data within two worksheets and then output the row with the difference to a new sheet. Also I would like the headers to go to the new worksheet.

 

[igold]

Hi, you left out a lot of information. For example look at the questions below:

Are the two sheets always going to have the same number of rows and columns and the same headers?

If we were comparing Sheet1 to Sheet2 and row 17 was not the same. Which sheet contains the row you want copied to the new sheet. The row 17 from Sheet1 or the row 17 from Sheet2?

Will there ever be a case where more than one row is different?

The more information you give, the more likely that someone will respond, and the more likely that the response will be suitable to your needs.

 

[mklindquist0815]

Sheet 1 ("Prior") and Sheet 2 ("New") are the same number of columns. They may or may not have the same number of rows depending on the differences.

The things that could be different:
1. New students (rows)
2. Balance Amount

IF any of the above changes I want it to output the row with the difference to a new sheet "Differences"

 

[JackDanIce]

To add to @igold's comment

- Is it column A in Prior and column K in New - what column are you entering data into?
- Does data start in row 1 or different start row?
- What column in the sheet contains the New student?
- What kind of data is it, is it text, numerical, a symbol? Are you able to give an example since your screen is not visible?
- Balance amount? Is it to 2 decimal places? Does it include positives and negative values? Again, example would help when your screen is not visible.
- If difference is found in Prior sheet, does that go into Differences sheet? Or is it only from the New sheet?
- What column and cell does the data go into in Differences sheet?
- Is the next cell after the last used row in that column?

Pretend people are blind, how could you use words and clear examples to exactly explain what you require?

 

[mklindquist0815]

I"m actually importing in two different csv files into the excel workbook and then I'm comparing that data. So I'm not entering new data. The CSV files are generated from a different system and we need to compare the data to see if new students present on the file or if a student's balance changed.

The column headers start in row 1. The Starred (*) items could have changes. There could be a new EMPLID on the later report (or vice versa). There are no decimals in the Balance and it includes both positive and negative. I probably would need to show differences in both sheet. I'm thinking we don't need to know if a student was on Sheet 1 but not anymore on Sheet 2 because this is basically to make sure we are billing all new students or students with different balances. But then maybe I need to but maybe add a new column called "Comment" and put the word "DELETED". Other comments could be "CHANGED", "ADDED"

EMPLID | NAME | CAREER | PROGRAM | *UNITS | *AMT DUE | *ANTAID | *BALANCE

I would like to have all columns for the row where the change occurred to appear on the differences sheet.

I will have the Differences sheet already added to the workbook so I could always make sure the column names are there as well. Then everything will start in Row 2.

 

[igold]

A couple of more questions...

You said that there may be a different amount of rows. Will the rows that already exist always be in the same order. For example if John Smith is on Row 4 and Mary Jones in on Row 9, will they always be on those rows on the new sheet, or might the order of the new sheet be different than the prior sheet and will all new entries always appear at the bottom of the new sheet.

Also, on the Differences sheet, are you looking for the calculation of the difference in the balances or just what the New balance is.

 

[mklindquist0815]

Hi igold,

The new rows will be intermingled - they won't be at the bottom or top.

On the differences sheet, I just need to have it show the EMPLID and different balance - no calculations need to be made.

 

[mklindquist0815]

Actually after reviewing the data the new sheet should include if the NEW Balance is greater than the PRIOR Balance and then any students that are on the NEW Sheet that weren't on the PRIOR Sheet. I do not need to know if a student is on PRIOR but not NEW. And it would be great if the different rows could also be highlighted on the NEW sheet.

 

[mklindquist0815]

Re: Comparison of two sheets

For my situation I need it to put any NEW students on the DIFFERENCES sheet and if a student has BALANCE value greater than the PRIOR Sheet.

For Example, certain value in a column (BALANCE) is greater than the previous sheet BALANCE.

See data below:

PRIOR Sheet
EMPLID | BALANCE
123456 | 0

NEW Sheet
EMPLID | BALANCE
123456 | 125

and I would like it to highlight the row on the NEW sheet.

DIFFERENCES Sheet
EMPLID | BALANCE
123456 | 125

 

[mklindquist0815]

A couple of more questions...

You said that there may be a different amount of rows. Will the rows that already exist always be in the same order. For example if John Smith is on Row 4 and Mary Jones in on Row 9, will they always be on those rows on the new sheet, or might the order of the new sheet be different than the prior sheet and will all new entries always appear at the bottom of the new sheet.

Also, on the Differences sheet, are you looking for the calculation of the difference in the balances or just what the New balance is.

I found this thread that does what I want it to do except I want it to just include if the NEW Balance is greater than the PRIOR Balance AND if there are new Students.

https://www.mrexcel.com/forum/excel...ighlight=compare worksheets differences sheet