Filter Data?

[Chris_010101]

Hello,

I track the whole company's absence on a single excel tracker (excerpt below). At the end of each week, I am required to provide an individual absence report to managers in each location. There are currently 62 in the business across 8 locations and this is hours of work on a Friday.

We use Microsoft Teams, so I was thinking of creating a Teams group for each of the 8 locations and dropping a separate spreadsheet in each group which automatically updates from my main tracker. Absence data is confidential, so location 1 managers can't see who is absent in location 2 for example.

Below is my main tracker (anonymised).

Test Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Employee ID (8 Digit)	Employee ID	Status	Forename	Surname	Role	Location	Region	Date of Birth	Age Decimal	Age Y/M	Hire Date	LOS Decimal	LOS Y/M	Last Absence Start	Total Occasions in Current 6 Months
2	00000001	1	Active	Bob	Smith	Manager	Wheathampstead	1	31/12/1993	29.47945205	29 Years, 5 Months	01/01/2022	0.780821918	0 Years, 9 Months	19/12/2022	1
3	00000002	2	Active	Alan	Carr	Shoesmith	Harpenden	2	12/01/1963	60.46849315	60 Years, 5 Months	01/01/2022	23.71780822	23 Years, 8 Months	20/12/2022	1
4	00000003	3	Active	Jacob	Mogg	Manager	Amwell	3	18/11/2000	22.59178082	22 Years, 6 Months	01/01/2022	0.953424658	0 Years, 11 Months	20/12/2022	1
5	00000004	4	Active	Boris	Yeltzen	Shoesmith	Wheathampstead	1	21/11/1988	34.59178082	34 Years, 6 Months	01/01/2022	5.978082192	5 Years, 11 Months	20/12/2022	1
6	00000005	5	Active	Bob	Mortimer	Manager	Gustard Wood	4	19/12/1959	63.5369863	63 Years, 5 Months	01/01/2022	32.13424658	32 Years, 1 Months	10/01/2023	2
7	00000006	6	Active	Daniel	Smith	Manager	Harpenden	2	01/02/1992	31.39452055	31 Years, 4 Months	01/01/2022	0.624657534	0 Years, 7 Months	19/12/2022	1
8	00000007	7	Active	Trevor	Melikklegundrilla	Shoesmith	Amwell	3	26/02/1985	38.32876712	38 Years, 3 Months	01/01/2022	21.30958904	21 Years, 3 Months	20/12/2022	1
9	00000008	8	Active	Sean	Lock	Shoesmith	Wheathampstead	1	11/01/1986	37.45479452	37 Years, 5 Months	01/01/2022	1.567123288	1 Years, 6 Months	22/12/2022	1
10	00000009	9	Active	John	Richardson	Shoesmith	Wheathampstead	1	14/05/1977	46.12328767	46 Years, 1 Months	01/01/2022	3.293150685	3 Years, 3 Months	22/12/2022	1
11	00000010	10	Active	Roisin	Connerty	Shoesmith	Amwell	3	30/07/1998	24.89863014	24 Years, 10 Months	01/01/2022	0.734246575	0 Years, 8 Months	22/12/2022	1
12	00000011	11	Active	Kevin	Kevinson	Shoesmith	Amwell	3	12/05/1978	45.12876712	45 Years, 1 Months	01/01/2022	3.024657534	3 Years, 0 Months	22/12/2022	1
13	00000012	12	Active	Peter	Andre	Shoesmith	Wheathampstead	1	06/09/1992	30.79726027	30 Years, 9 Months	01/01/2022	1.279452055	1 Years, 3 Months	30/03/2023	3
14	00000013	13	Active	Ian	McKellen	Shoesmith	Amwell	3	22/12/1976	46.51506849	46 Years, 5 Months	01/01/2022	1.41369863	1 Years, 5 Months	19/12/2022	1
15	00000014	14	Active	Paul	Gascoine	Shoesmith	Wheathampstead	1	24/09/1985	37.75342466	37 Years, 8 Months	01/01/2022	1.375342466	1 Years, 4 Months	15/03/2023	2
16	00000015	15	Active	Russel	Brand	Shoesmith	Harpenden	2	02/07/1986	36.98356164	36 Years, 11 Months	01/01/2022	15.96712329	15 Years, 11 Months	22/12/2022	1
17	00000016	16	Active	Sarah	Millican	Shoesmith	Wheathampstead	1	14/02/1989	34.35890411	34 Years, 4 Months	01/01/2022	1.356164384	1 Years, 4 Months	03/01/2023	2
18	00000017	17	Active	Kirsty	Thirsty	Shoesmith	Amwell	3	28/12/1990	32.49041096	32 Years, 5 Months	01/01/2022	1.509589041	1 Years, 6 Months	16/03/2023	2
19	00000018	18	Active	Stuart	Edwards	Shoesmith	Wheathampstead	1	24/06/1990	33.00273973	32 Years, 11 Months	01/01/2022	1.449315068	1 Years, 5 Months	03/04/2023	2
20	00000019	19	Active	Andrew	Marr	Shoesmith	Amwell	3	18/12/1960	62.5369863	62 Years, 5 Months	01/01/2022	17.32328767	17 Years, 3 Months	11/04/2023	2
21	00000020	20	Active	Patrick	Swazey	Shoesmith	Harpenden	2	21/03/1973	50.2739726	50 Years, 2 Months	01/01/2022	31.29041096	31 Years, 3 Months	28/04/2023	2
22	00000021	21	Active	Jeremy	Paxman	Shoesmith	Wheathampstead	1	15/08/1975	47.87123288	47 Years, 10 Months	01/01/2022	2.753424658	2 Years, 9 Months	25/05/2023	3
23	00000022	22	Active	Rod	Liddle	Shoesmith	Amwell	3	28/11/1961	61.59178082	61 Years, 6 Months	01/01/2022	2.846575342	2 Years, 10 Months	19/12/2022	1
24	00000023	23	Active	Rob	Brydon	Shoesmith	Wheathampstead	1	27/03/1960	63.26575342	63 Years, 2 Months	01/01/2022	2.564383562	2 Years, 6 Months	23/12/2022	1
25	00000024	24	Active	Joe	Wilkinson	Shoesmith	Amwell	3	07/02/1963	60.39726027	60 Years, 4 Months	01/01/2022	5.364383562	5 Years, 4 Months	22/03/2023	2
26	00000025	25	Active	Rob	Beckett	Shoesmith	Wheathampstead	1	18/04/1962	61.20547945	61 Years, 1 Months	01/01/2022	12.40273973	12 Years, 4 Months	22/12/2022	1
27	00000026	26	Active	Teresa	Green	Shoesmith	Harpenden	2	23/10/1955	67.69589041	67 Years, 7 Months	01/01/2022	4.731506849	4 Years, 8 Months	30/05/2023	3
Sheet1

Cell Formulas
Range	Formula
A2:A27	A2	=REPT(0,8-LEN(B2))&B2

Say I wanted extract all Wheathampstead rows from the above onto a separate spreadsheet which automatically updates when the above is updated, how could this be done?

Kind Regards,
Chris

 

[Fluff]

It's still very simple.

 

[kweaver]

However, if, after you filter the data per location, you copy only that data and send that single sheet to a manager, there wouldn't be any issue.

 

[Chris_010101]

However, if, after you filter the data per location, you copy only that data and send that single sheet to a manager, there wouldn't be any issue.

Problem is, it's still the same as producing an individual report for each manager on Friday, as the data won't auto-update. A lot of copying and pasting

 

[kweaver]

Might just have to face the security problems with Excel depending on manager's skills.

 

[Chris_010101]

Might just have to face the security problems with Excel depending on manager's skills.

Think so!

Thanks anyhow

 

[RandyMorris]

Problem is, it's still the same as producing an individual report for each manager on Friday, as the data won't auto-update. A lot of copying and pasting

I believe you could write a filter + copy/paste new workbook macro to circumvent the manual part.

 

[kevin9999]

Problem is, it's still the same as producing an individual report for each manager on Friday, as the data won't auto-update. A lot of copying and pasting

True Chris, but with VBA the task could be made quite simple & relatively fast. Please try the following on a copy of your workbook:

Option Explicit
Sub FridayReports()
    Application.ScreenUpdating = False
    Dim t As Double: t = Timer
    Dim ws As Worksheet, wsNew As Worksheet, i As Long
    Set ws = Worksheets("Sheet1")   '<~~ *** Change as required ***
    Dim a
    a = WorksheetFunction.Unique(ws.Range("G2", ws.Cells(Rows.Count, "G").End(xlUp)))
    
    For i = LBound(a, 1) To UBound(a, 1)
        ws.Copy
        Set wsNew = ActiveSheet
        With wsNew.Range("A1").CurrentRegion
            .AutoFilter 7, "<>" & a(i, 1)
            .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
            .AutoFilter
        End With
        Application.DisplayAlerts = False
        wsNew.SaveAs ThisWorkbook.Path & "\" & a(i, 1)
        Application.DisplayAlerts = True
    Next i
    Application.ScreenUpdating = True
    MsgBox "Completed in " & Timer - t & " seconds."
End Sub