Comparing updates of a register presented in colums

Macmoledo

New Member
Joined
Aug 16, 2019
Messages
6
Hi there,
First my apologies if there is an answer to this already in the forum, but the amount of info is a little overwhelming and I'm taking the quick route; hope you don't mind.
There is a register related to my work that I have to check every week to see whether there are new entries or if one of the existing was dropped.
I download form the web a CSV file that I convert to .XLSX and I keep only the data I need to check; let's say they are projects (the name of the project) and number of assets of that particular project.
Which would be the best way you recommend to compare one week's result with the previous one?
Note that due to the updates, the data for 1 week can be larger or shorter than the one for the following week, i.e. may be a different number of files. Probably obvious but just wanted to make everything clear.
Thanks in advance for any help.
Cheers to all.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
You could do this by pointing the addresses to external files but to keep it simple I'll assume you copy & paste the latest data into sheet "Latest" and last week's data into the sheet "Lastweek".

AB
1ProjectAssets
2AAA14
3BBB18
4DDD2
5XXX9
6YYY13
7ZZZ22

<tbody>
</tbody>
Latest


AB
1ProjectAssets
2BBB12
3DDD17
4FFF18
5MMM17
6ZZZ15

<tbody>
</tbody>
Lastweek

On a sheet called "Compare" I added formulae for the New and Dropped checks in row 2 and copied to to the row which will cover your maximum number of rows in future.

ABCD
1New in LatestDropped from LastweekCount New= 3
2New AAACount Dropped 2
3
4Dropped FFF
5New XXXDropped MMM
6New YYY
7

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Compare

Worksheet Formulas
CellFormula
A2=IF(Latest!A2="","",IF(ISNA(MATCH(Latest!A2,Lastweek!A:A,0)),"New "&Latest!A2,""))
B2=IF(Lastweek!A2="","",IF(ISNA(MATCH(Lastweek!A2,Latest!A:A,0)),"Dropped "&Lastweek!A2,""))
D1
="Count New= "&MAX(0,COUNTIF(A:A,"> ")-1)
D2="Count Dropped "&MAX(0,COUNTIF(B:B,"> ")-1)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

If you've only 10 to 20 rows it will be obvious which they are but in case you've thousands I added a count in cells D1 and D2.

Regards,
Toadstool
 
Upvote 0
Apologies for my delay in replying, but thank you so much for your response..
I understand the principle, but the formulas are giving me a problem because I'm using the Spanish edition, so I have to find the right formulas and conectors, but I'm sure this is what I need looking to your example.
Thanks a lot again.
Kindest regards!!
 
Upvote 0
For A2
=SI(Latest!A2="";"";SI(ESNOD(COINCIDIR(Latest!A2;Lastweek!A:A;0));"New "&Latest!A2;""))
B2
=SI(Lastweek!A2="";"";SI(ESNOD(COINCIDIR(Lastweek!A2;Latest!A:A;0));"Dropped "&Lastweek!A2;""))
D1
="Count New= "&MAX(0;CONTAR.SI(A:A;"> ")-1)
 
Upvote 0
Good day Fluff. Thanks for your help. I got there as well eventually, although it was giving me some trouble, until I realize that I named one of the sheets as "Last Week" instead of "Lastweek" altogether... and it was giving me a reference problem to source the data.
Totally amateur mistake!! Now is working and it is exactly what I needed for a quick check.
Apologies for the inconveniences and thanks once again to both of you for your assistance.
This forum is great, with incredible advice and for free!! something rare these days...
All the best and kind regards.
 
Upvote 0
For reference there's a site here that will translate formulae, which I used to translate Toadstool's formulae https://en.excel-translator.de/translator/


Sorry again,

Just a quick one if I may.
Could any of you explain to me how it works the criteria for the function "COUNTIF" when you put "> "? Is it just lokking for a value? Sorry but I've never seen this criteria used before. The formula works though, but I wanted to understand it.
And why the "-1" at the end? I get the right result if I don't use the "-1" so I don't understand that one??
Thanks again.
 
Upvote 0
It's because I'm so lazy... ;)

I could have typed COUNTIF(A:A,">"&" ")-1) to make it plain I was searching for a cell containing text, but COUNTIF(A:A,"> ")-1) was three less keystrokes.

The -1 is to account for my other lazy approach. I'm searching the whole of columns A and B for greater than blank but that will always return 1 for the headings, so I subtract 1.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top