• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Comparing data for 2 different months.

Hi

I really would appreciate if someone can help me. I'm sure there much be a smart solution, I'm just not start enough to know it :-(.

I have 2 files one for the month of May and one for the month June, and I would like to know if there are any differences between the two files. The manner and format of the files make is impossible for me to do this without hours/day of manual manipulation. I am hoping someone can come up and show me an inspired way to do this.

I have attached the two files so you can clearly see what I need.

In column A - you have the employee number, and we are comparing for each employee.
In Row 13 you have the different codes we want to compare.
For each code, an employee may or may not have a value.

I want to see if there were any changes in the June to May file per employee for each of these codes.

I would like to take the sheet tab called 'June' and create two new columns after each code, and in 1st new column to add the data the relates to the employee in the prior month (sheet tab 'May')for the same code, and in the second new column to add a text field (either "same or "different").

The codes start from column D

Please remember I want to compare June to May.

I hope I have explained myself clearly.

Thank you *1000000 for anyone that can help.

David.
 

Attachments

  • Compare 2 Months Files --.xlsx
    22.6 KB · Views: 9
david gabra
The 1st sample... ( = not ready at all! )
It would be ... more useful ... it those texts would be saved as numbers
... eg 5948.8 is different than 5948.80, if saved as now as with texts.
Yellow cells has something different that other sheets texts and
with cells comment, can see other sheets text.
 

Attachments

  • Compare 2 Months Files --.xlsb
    78.3 KB · Views: 5
david gabra
The 1st sample... ( = not ready at all! )
It would be ... more useful ... it those texts would be saved as numbers
... eg 5948.8 is different than 5948.80, if saved as now as with texts.
Yellow cells has something different that other sheets texts and
with cells comment, can see other sheets text.
david gabra
The 1st sample... ( = not ready at all! )
It would be ... more useful ... it those texts would be saved as numbers
... eg 5948.8 is different than 5948.80, if saved as now as with texts.
Yellow cells has something different that other sheets texts and
with cells comment, can see other sheets text.
Thank you for pointing that out to me. I am now attaching a revised file, where all numbers are saved as number format and not text.
 

Attachments

  • Compare 2 Months Files - Revised File .xlsx
    21.7 KB · Views: 1
Hi

I really would appreciate if someone can help me. I'm sure there much be a smart solution, I'm just not start enough to know it :-(.

I have 2 files one for the month of May and one for the month June, and I would like to know if there are any differences between the two files. The manner and format of the files make is impossible for me to do this without hours/day of manual manipulation. I am hoping someone can come up and show me an inspired way to do this.

I have attached the two files so you can clearly see what I need.

In column A - you have the employee number, and we are comparing for each employee.
In Row 13 you have the different codes we want to compare.
For each code, an employee may or may not have a value.

I want to see if there were any changes in the June to May file per employee for each of these codes.

I would like to take the sheet tab called 'June' and create two new columns after each code, and in 1st new column to add the data the relates to the employee in the prior month (sheet tab 'May')for the same code, and in the second new column to add a text field (either "same or "different").

The codes start from column D

Please remember I want to compare June to May.

I hope I have explained myself clearly.

Thank you *1000000 for anyone that can help.

David.

Please use the updated file since in this file all numbers are converted from the text format to number format. I understand now that this is needed.
 

Attachments

  • Compare 2 Months Files - Revised File .xlsx
    21.7 KB · Views: 1
Hi and welcome
You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.
 
david gabra
Could You update those to that my made file?
and
after that give more hints, what is missing?

Hello

I need to add two columns you see below for the 'Prior Month' and 'Difference' after each number (code), on row 2. In this case prior month is the tab sheet named May.

I have attached a example with columns I added manually, I need this automatically for all.

thanks

David.


ID NAMEDATE
Emp220Prior Month Difference221Prior Month Difference
1​
BLANK6946.086946.080.000.000.00
2​
BLANK0.000.003350.000.003350.00
3​
BLANK4921.724921.720.000.000.00
4​
BLANK4390.204390.200.000.000.00
5​
BLANK7534.837534.830.000.000.00
6​
BLANK13540.8813540.880.000.000.00
7​
BLANK6460.006460.000.000.000.00
8​
BLANK4813.754813.750.00690.08-690.08
9​
BLANK4293.464293.460.000.000.00
10​
BLANK6044.296044.290.000.000.00
11​
BLANK5259.774000.001259.770.000.00
12​
BLANK4673.424673.420.000.000.00
13​
BLANK4458.334458.330.000.000.00
14​
BLANK0.000.004359.170.004359.17
15​
BLANK5948.805948.800.000.000.00
16​
BLANK4958.904958.900.000.000.00
17​
BLANK0.000.004608.180.004608.18
18​
BLANK5193.675193.670.000.000.00
19​
BLANK3583.333583.330.000.000.00
TOTAL93021.4393021.430.0012317.35690.0811627.27
 

Attachments

  • Compare 2 Months Based on Code and Employee -Updated.xlsx
    118.4 KB · Views: 2
david gabra
Could You update those to that my made file?
and
after that give more hints, what is missing?
Hi and welcome
You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it. Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Hello Sir
I did not create these, this is the what I was given, and it is nightmare for me. i would never design like this.
I really need help for an effective simple solution.
Is there any way you can help.
Note thee latest revised file above.
thanks
David.
 
david gabra
Still there are texts as numbers.
Why do You need two more columns per code's?
Did You checked my previous versio? ... there You could see - other sheets values
... now, You also could see differences
... and those Your text-numbers would use as numbers too
... as well as ... if those Emp would be different order or some new Emp's would come ...hmm?

> You should know - what would You need to get from ... that?
 

Attachments

  • Compare 2 Months Files --.xlsb
    114.9 KB · Views: 5
david gabra
Still there are texts as numbers.
Why do You need two more columns per code's?
Did You checked my previous versio? ... there You could see - other sheets values
... now, You also could see differences
... and those Your text-numbers would use as numbers too
... as well as ... if those Emp would be different order or some new Emp's would come ...hmm?

> You should know - what would You need to get from ... that?

Hi

What you did is great - very smart!!!

I see I missed a few for number conversion and fixed them - really am trying.

I wanted the additional column so I could easily send a file back with the differences to the person who needs to fix.
if you can't do this ok

i noticed that it does not always work as required if the columns are not in the same order. That is also my issue,, you could have code 681 in column J in may and this code may be in column H in June, and this can be with all the codes, each month they are not in the same column.
the solution please needs to overcome this,

thanks for all effort so far.

David.
 

Attachments

  • Compare 2 Months Files -- (5).xlsb
    115.1 KB · Views: 2
david gabra
#1 Your: I see I missed a few for number conversion and fixed them - really am trying.
As I tried to explain ... no matter if those Your text-numbers are as texts!

#2 Your: I wanted the additional column so I could easily send a file back with the differences to the person who needs to fix.
If You have there ~35 (visible) columns to compare ... do You really need 70 more columns more? ... and 35 of those would be duplicate?
... and now ... there are ... would I express ... a lot of differences!

#3 Your: i noticed that it does not always work as required if the columns are not in the same order.
That is also possible to check ... just makes things ... slower.

>>> Added new file <<<
#2 You could see those differences like with this file.
 

Attachments

  • Compare 2 Months Files --.xlsb
    108.2 KB · Views: 4
Last edited:
Thank you!!!!!!!!!!!!!!!! unbelievable :)
I don't even seen how it work - where is all the formulae?

It seems to work great

q1) what does green mean

And I hope the last tweek - If I were to add a new employee to the June tab - I don't see that come up an an exception - which to me is super-key
since it means that someone is on June payroll and not in the May. Is there anyway to someway catch that - updated the file with that scenario
- the new employee is on the last row, employee number 20. I added that on the new file.

thanks

David.
 

Attachments

  • Compare 2 Months Files -- (9).xlsb
    104.8 KB · Views: 2
david gabra
There are no formulas.
'Green' would try to show that those values cannot compare and that's why those would be as okay.
Number 20 ... seems that there missed 'two letters' in my code. - as above 'Green'.
I use only my file, You should try to this version.
 

Attachments

  • Compare 2 Months Files --.xlsb
    108.3 KB · Views: 18
Hi

If I want to add a file that has 1000 rows, what do I need to do- since our sample over has a few.

I keep getting circular reference error

thanks

David.




david gabra
There are no formulas.
'Green' would try to show that those values cannot compare and that's why those would be as okay.
Number 20 ... seems that there missed 'two letters' in my code. - as above 'Green'.
I use only my file, You should try to this version.
 
david gabra
What to adjust?
You can copy over there as many lines as You would like to lets say.
... and 'My solution' do not matter anything about Your 'possible' formulas; if those are correct then those would work otherwise something else.
... do You need there formulas or fixed results of formulas?
 
david gabra
What to adjust?
You can copy over there as many lines as You would like to lets say.
... and 'My solution' do not matter anything about Your 'possible' formulas; if those are correct then those would work otherwise something else.
... do You need there formulas or fixed results of formulas?
Yes - I see - I succeeded.
 
I really would appreciate if someone can help me. I'm sure there much be a smart solution, I'm just not start enough to know it :-(.
 
Back
Top