Excel (Formula/VBA) Help: Two sheets into one

[Chris_010101]

Hello,

I have an absence tracker which records absence in a current 6-month rolling period and a previous 6-month rolling period.

Each occasion of absence is recorded as a separate row and absence is manually added to the "current 6 month" sheet daily (as it occurs). A piece of VBA code then looks at the absence start date and if it falls before 6 months from the current day, it would automatically move the whole row to the next blank row on the "previous 6 months" sheet. It also looks at the "previous 6 months" sheet and any rows with an absence start date less than 12 months from today will go to the "previous 12-24 months" sheet and will then be automatically deleted from 12-24 months, if more than 24 months old.

Each occasion of absence is recorded on its own row, with the unique employee ID in column A (which uses the REPT formula to get leading 0's in employee ID from column I). So, if an employee was sick on 05/05/2022 for 1 day and then sick again on 06/06/2022, the employee would have 2 rows. Example:

I need a separate sheet to tell me how many occasions of absence each employee has over the past 12 months (i.e. over the two sheets "current 6 months" and "previous 6 months"), so an "Occasions last 12 months" sheet.

As per picture above, I did this for looking at only current 6 months using =Unique(Filter(XXXX,XXXX<>"")) on employee ID to pull a list of unique values, then used COUNTIF to count how many times the employee ID appeared in "current 6 months", which looks like this:

BUT this method won't work when the data is split over 2 sheets. Not sure if this can be formula driven or needs VB Code.

Kind Regards

 

[Chris_010101]

I basically need to do the same as the "Occasions current 6 months" sheet but do it looking at 12 months of data split over two sheets (Current 6 and Previous 6).

 

[Chris_010101]

Is anyone able to help?

 

[kevin9999]

The simplest solution would be to have 2 Countif formulas & add them together, something like:

=COUNTIF('Previous 6 Months'!A:A,A2)+COUNTIF('Current 6 Months'!A:A,A2)

Just make A:A the column where you have the names, and A2 the cell where you have the lookup value.

 

[Chris_010101]

The simplest solution would be to have 2 Countif formulas & add them together, something like:

=COUNTIF('Previous 6 Months'!A:A,A2)+COUNTIF('Current 6 Months'!A:A,A2)

Just make A:A the column where you have the names, and A2 the cell where you have the lookup value.

Hello again Kevin,

Thanks for your reply and I hope you're well.

I've spent some time on this this morning and I'm stuck. I think I know where - it's the employee ID part. Let me try and explain and hopefully I'm not being remiss in my efforts with this.

My "Occasions Last 12" sheet will look like this:

In column B (Employee ID), I need it to look at both sheets "current 6 months" and "previous 6 months" and return one of each employee ID (which are in column I on both sheets).

In column O (Total Occasions in Last 12), this would be where the =COUNTIF formula will be. This will look at column B and then tell me how many times the same employee ID appears across the two sheets ("Current 6 months" and "previous 6 months") - as each row is a separate occasion and contains the employee ID, counting rows will therefore return the number of occasions.

The rest I can do, not an issue.

---------

Maybe it's irrelevant but if it helps, the "current 6 months" and "previous 6 months" are both laid out in this format, with the employee ID in column I:

Fields in grey are all formulas.

Kind Regards
Chris

 

[Chris_010101]

Hold on....

Would

=UNIQUE(FILTER('Current 6 Months'!I2:I1048576,'Current 6 Months'!I2:I1048576<>") +UNIQUE(FILTER('Previous 6 Months'!I213:I1048576, 'Previous 6 Months'!I2:I1048576<>"""))

Do that for the employee ID?

EDIT: I've checked and no, it doesn't work as it was missing employee IDs

 

[kevin9999]

I might be aiming the formulas at the wrong columns, but I hope there's enough here to give you the gist of what to do

advanced filter.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Employee ID													Total Occasions in last 12
2	Adam													2
3	Bill													4
4	Chris													2
5	0
6
Occasions Last 12

Cell Formulas
Range	Formula
B2:B5	B2	=UNIQUE(VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000))
O2:O5	O2	=IF(B2<>0,COUNTIF('Current 6 Months'!$I$2:$I$1000,B2)+COUNTIF('Previous 6 Months'!$I$2:$I$1000,B2),"")
Dynamic array formulas.

 

[Chris_010101]

I might be aiming the formulas at the wrong columns, but I hope there's enough here to give you the gist of what to do

advanced filter.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Employee ID													Total Occasions in last 12
2	Adam													2
3	Bill													4
4	Chris													2
5	0
6
Occasions Last 12

Cell Formulas
Range	Formula
B2:B5	B2	=UNIQUE(VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000))
O2:O5	O2	=IF(B2<>0,COUNTIF('Current 6 Months'!$I$2:$I$1000,B2)+COUNTIF('Previous 6 Months'!$I$2:$I$1000,B2),"")
Dynamic array formulas.

Brilliant, that seems to work . Thank you very much.

I modified the Unique formula to:

=SORT(UNIQUE(VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000)))

I'll test this over the next few hours to double-check.

Kind Regards
Chris

 

[kevin9999]

If you wanted to lose the zero, and sort the result, this will do it for you:

=SORT(UNIQUE(FILTER(VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000),VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000)<>"")))

 

[Chris_010101]

If you wanted to lose the zero, and sort the result, this will do it for you:

=SORT(UNIQUE(FILTER(VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000),VSTACK('Current 6 Months'!$I$2:$I$1000,'Previous 6 Months'!$I$2:$I$1000)<>"")))

Thanks