CODEWITHSUNDEEP
excelexcel-2007excel-formulaexcel-2010vba
user1259984
user1259984

Reputation: 23

Comparing values in Excel between YTD Monthly reports

I am extracting Year To Date monthly reports on tickets and their statuses.

I have a report for Apri:

ID Status
123 Open
124 Closed

and another report in May:

ID Status
123 Closed
124 Closed
125 Open

I need to find out how many tickets have closed since April based on the May report. So in this case only one ticket -> 123. 124 was already closed in April so I am not counting it as closed in May, although it will be present in May's report. I am basically looking for changes of statuses between the two reports.

I am obviously working with a larger amount of data. This is a regular report that I should extract so I am also looking into automation possibilities(e.g. macro or VBA).

Upvotes: 0

Views: 459

Answers (1)

Sakir SEN
Sakir SEN

Reputation: 284

I hope that a solution the following will do.

Sheet April

Sheet April

Sheet May

Sheet May

Sheet May C2 (April Status) write formula

=IFERROR(VLOOKUP(A2;April!$A$2:$B$3;2;FALSE));"")

And D2 write

=IF(AND(C2="Open";B2="Closed");1;0)

I believe will want to Sum column D

Upvotes: 1

Related Questions