Comparing updates of a register presented in colums

[Macmoledo]

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.

 

[Toadstool]

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".

	A	B
Project	Assets
AAA
BBB
DDD
XXX
YYY
ZZZ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]14[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]13[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]22[/TD]

</tbody>

Latest​

	A	B
Project	Assets
BBB
DDD
FFF
MMM
ZZZ

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]18[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]17[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]15[/TD]

</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.

	A	B	C	D
New in Latest	Dropped from Lastweek	Count New= 3
New AAA		Count Dropped 2
	Dropped FFF
New XXX	Dropped MMM
New YYY

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Compare​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=IF(Latest!A2="","",IF(ISNA(MATCH(Latest!A2,Lastweek!A:A,0)),"New "&Latest!A2,""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(Lastweek!A2="","",IF(ISNA(MATCH(Lastweek!A2,Latest!A:A,0)),"Dropped "&Lastweek!A2,""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1
[/TH]
[TD="align: left"]="Count New= "&MAX(0,COUNTIF(A:A,"> ")-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]="Count Dropped "&MAX(0,COUNTIF(B:B,"> ")-1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Macmoledo]

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!!

 

[Fluff]

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)

 

[Macmoledo]

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.

 

[Fluff]

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/

 

[Macmoledo]

Awesome!!! Many thanks.

 

[Fluff]

My pleasure.

 

[Macmoledo]

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.

 

[Toadstool]

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.