Extract selected data in a new sheet based on a cell text

[vij]

Hi,

I am looking for extracting selected data in a new sheet based on entering "Completed" in a different cell.

My table is as attached.

Site	1. Progress (top 3 accomplishments from this week).	2. Problems (Top 03)	3. Plans for next week	4. EX - Org, Hiring/Attrition. [Changes to our team: development opportunities, internal mobility, new hires (include country/region)]	5. Recognition. [Is there someone on the team you would like to recognize? Share their name and a brief explanation]	06. Upcoming Travel	07. WFH IT Assets / Exit Employee Material/ Existing employee critical items/ Chairs handed over.	Completad
N132	• Two-day vaccination drive completed. • Monthly fire extinguisher audit completed on 25th Jun. • Tyco- Maintenance of CCTV and Access control at 3rd floor.	Nil	Water body project to be started next week.					Completed
N25A	Employees provided with lifesaving equipment in emergency.  Delivering wellness care kit for employees.  Maintenance team laying water pipeline for watering the plants near boundary wall.  Tyco- FAS PPM activity at 7th floor completed and 6th floor started today.  Monthly fire extinguisher audit completed on 26th Jun. .	Nil	Monthly audit of register.					Completed
PTP/PTC	Physical Security  Completed sites monthly fire extinguishers audit & replaced defective one. Systems • N25 – New SSL certificates uploaded in RFID portal (testing WIP)	Nil	Physical Security • Launching of Inspect & Track pilot project. • Collaborating with GSO for vaccination at PTP site. • Monthly ops meeting with Securitas branch team Systems • All Sites (except PTP) – PPM (WIP), regular breakdown calls.					Completed
ERGO	6/30 – Virtual ergo session was conducted on “Exercise snaking for stiff neck” attended by 32 participants	7/14 – Virtual ergo session would be conducted on “Syndromes of the computer era” • 7/17 – An ergo workshop would be conducted for kids “Weekend with ergonomist						Completed
ISOC	Planning shutdown activities. • Monitoring COVID case trends	Training, Drills and Audits. • Monitoring COVID case trends, India GSS team will be notified if there is any case surge in the tier one cities, specially where Adobe has a presence	Training, Drills and Audits. • Monitoring COVID case trends, India GSS team will be notified if there is any case surge in the tier one cities, specially where Adobe has a presence					Completed
KAM
N132	• Two-day vaccination drive completed. • Monthly fire extinguisher audit completed on 25th Jun. • Tyco- Maintenance of CCTV and Access control at 3rd floor.	Nil	Water body project to be started next week.					Completed
N25A	Employees provided with lifesaving equipment in emergency.  Delivering wellness care kit for employees.  Maintenance team laying water pipeline for watering the plants near boundary wall.  Tyco- FAS PPM activity at 7th floor completed and 6th floor started today.  Monthly fire extinguisher audit completed on 26th Jun. .	Nil	Monthly audit of register.					Completed
PTP/PTC	Physical Security  Completed sites monthly fire extinguishers audit & replaced defective one. Systems • N25 – New SSL certificates uploaded in RFID portal (testing WIP)	Nil	Physical Security • Launching of Inspect & Track pilot project. • Collaborating with GSO for vaccination at PTP site. • Monthly ops meeting with Securitas branch team Systems • All Sites (except PTP) – PPM (WIP), regular breakdown calls.					Completed
ERGO	6/30 – Virtual ergo session was conducted on “Exercise snaking for stiff neck” attended by 32 participants	7/14 – Virtual ergo session would be conducted on “Syndromes of the computer era” • 7/17 – An ergo workshop would be conducted for kids “Weekend with ergonomist						Completed
ISOC	Planning shutdown activities. • Monitoring COVID case trends	Training, Drills and Audits. • Monitoring COVID case trends, India GSS team will be notified if there is any case surge in the tier one cities, specially where Adobe has a presence	Training, Drills and Audits. • Monitoring COVID case trends, India GSS team will be notified if there is any case surge in the tier one cities, specially where Adobe has a presence					Completed
KAM

Thanks

 

[EFANYoutube]

Hi Vij
I Don't know exactly what you want. Could you please gave a lot more detail, like: what data do you want, where do you want it, in yout data everything is completed, what do you mean by "Completed" in a different cell"

 

[vij]

I have data from column A to J and in column I, complete is entered if the Projects in column d are finished.
The data with the date, Site and project details as mentioned in column D are automatically entered in a new sheet with the Site name (N132, N25A and so on.

I am basically trying to capture the start and completion of projects mentioned in column D for each site in a different sheet.

I am using the following VBA code with which all data is being captured from Column A to K site wise in a new sheet.

I am unable to select specific columns.

Sub test()
Dim i As Long, d, e, dic As Object
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
With Sheets("master").Range("a12").CurrentRegion
For i = 3 To .Rows.Count
If .Cells(i, 11).Value = "Completed" Then
For Each e In Split(.Cells(i, 3).Value, "/")
If Not dic.exists(e) Then
Set dic(e) = Union(.Rows(1), .Rows(d))
Else
Set dic(e) = Union(dic(e), .Rows(d))
End If
Next
End If
Next
End With
For Each e In dic
If Not IsSheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
With Sheets(e)
.Cells.Delete
dic(e).Copy
.Cells(1).PasteSpecial xlPasteColumnWidths
.Cells(1).PasteSpecial
End With
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Function IsSheetExists(ByVal txt As String) As Boolean
On Error Resume Next
IsSheetExists = Len(Sheets(txt).Name)
On Error GoTo 0
End Function

 

[vij]

Can some one confirm if what I am trying to do is possible?

 

[vij]

GSS Weekly Report Aug 2021 with input sheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K
12	S No	Date	Site	1. Progress (top 3 accomplishments from this week).	2. Problems (Top 03)	3. Plans for next week	4. EX - Org, Hiring/Attrition. [Changes to our team: development opportunities, internal mobility, new hires (include country/region)]	5. Recognition. [Is there someone on the team you would like to recognize? Share their name and a brief explanation]	06. Upcoming Travel	07. WFH IT Assets / Exit Employee Material/ Existing employee critical items/ Chairs handed over.	Completed
13			N25A	AAAA						• WFH Handover. o Total as on date - o Current week -	Completed
14			N25A	2						• Exit employee material handed over. o Total as on date - o Current week -	Completed
15			N25A	3						• Existing employee critical items handed over. o Total as on date – o Current week -
16			N25A	4						• Office Chairs collected. o Total as on date – o Current week -	Completed
17			N132	5						• WFH Handover. o Total as on date - o Current week -	Completed
18			N132	6						• Exit employee material handed over. o Total as on date - o Current week -	Completed
19			N132	7						• Existing employee critical items handed over. o Total as on date – o Current week -	Completed
20			N132	8						• Office Chairs collected. o Total as on date – o Current week -	Completed
21			PTP	9						• WFH Handover. o Total as on date - o Current week -	Completed
22			PTP	0						• Exit employee material handed over. o Total as on date - o Current week -	Completed
23			PTP	11						• Existing employee critical items handed over. o Total as on date – o Current week -
24			PTP	12						• Office Chairs collected. o Total as on date – o Current week -	Completed
25			PTC	13						• WFH Handover. o Total as on date - o Current week -	Completed
26			PTC	14						• Exit employee material handed over. o Total as on date - o Current week -
27			PTC	15						• Existing employee critical items handed over. o Total as on date – o Current week -	Completed
28			PTC	16						• Office Chairs collected. o Total as on date – o Current week -	Completed
29			Systems	17							Completed
30			Systems	18
31			Systems	19							Completed
32			Systems	20							Completed
33			ISOC	21							Completed
34			ISOC	22
35			ISOC	23							Completed
36			ISOC	24							Completed
37			KAM	25							Completed
38			KAM	26							Completed
39			KAM	27
40			KAM	28
41			ERGO	29							Completed
42			ERGO	30							Completed
43			ERGO	31
44			ERGO	32							Completed
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H42	Cell Value	contains "Almost done"	text	NO
H42	Cell Value	contains "Progress"	text	NO
H42	Cell Value	contains "Risk"	text	NO
H42	Cell Value	contains "Completed"	text	NO
H41	Cell Value	contains "Almost done"	text	NO
H41	Cell Value	contains "Progress"	text	NO
H41	Cell Value	contains "Risk"	text	NO
H41	Cell Value	contains "Completed"	text	NO
H40	Cell Value	contains "Almost done"	text	NO
H40	Cell Value	contains "Progress"	text	NO
H40	Cell Value	contains "Risk"	text	NO
H40	Cell Value	contains "Completed"	text	NO
H39	Cell Value	contains "Completed"	text	NO
H39	Cell Value	contains "Almost done"	text	NO
H39	Cell Value	contains "Progress"	text	NO
H39	Cell Value	contains "Risk"	text	NO
H39	Cell Value	contains "Completed"	text	NO
H37	Cell Value	contains "Almost done"	text	NO
H37	Cell Value	contains "Progress"	text	NO
H37	Cell Value	contains "Risk"	text	NO
H37	Cell Value	contains "Completed"	text	NO
H34	Cell Value	contains "Completed"	text	NO
H34	Cell Value	contains "Almost done"	text	NO
H34	Cell Value	contains "Progress"	text	NO
H34	Cell Value	contains "Risk"	text	NO
H34	Cell Value	contains "Completed"	text	NO
H33	Cell Value	contains "Completed"	text	NO
H33	Cell Value	contains "Almost done"	text	NO
H33	Cell Value	contains "Progress"	text	NO
H33	Cell Value	contains "Risk"	text	NO
H33	Cell Value	contains "Completed"	text	NO
H30	Cell Value	contains "Almost done"	text	NO
H30	Cell Value	contains "Progress"	text	NO
H30	Cell Value	contains "Risk"	text	NO
H30	Cell Value	contains "Completed"	text	NO
H29	Cell Value	contains "Almost done"	text	NO
H29	Cell Value	contains "Progress"	text	NO
H29	Cell Value	contains "Risk"	text	NO
H29	Cell Value	contains "Completed"	text	NO
H28	Cell Value	contains "Almost done"	text	NO
H28	Cell Value	contains "Progress"	text	NO
H28	Cell Value	contains "Risk"	text	NO
H28	Cell Value	contains "Completed"	text	NO
H16	Cell Value	contains "Completed"	text	NO
H16	Cell Value	contains "Almost done"	text	NO
H16	Cell Value	contains "Progress"	text	NO
H16	Cell Value	contains "Risk"	text	NO
H16	Cell Value	contains "Completed"	text	NO
H32	Cell Value	contains "Completed"	text	NO
H32	Cell Value	contains "Almost done"	text	NO
H32	Cell Value	contains "Progress"	text	NO
H32	Cell Value	contains "Risk"	text	NO
H32	Cell Value	contains "Completed"	text	NO
I24	Cell Value	contains "Almost done"	text	NO
I24	Cell Value	contains "Progress"	text	NO
I24	Cell Value	contains "Risk"	text	NO
I24	Cell Value	contains "Completed"	text	NO
I24	Cell Value	contains "Completed"	text	NO
H25	Cell Value	contains "Almost done"	text	NO
H25	Cell Value	contains "Progress"	text	NO
H25	Cell Value	contains "Risk"	text	NO
H25	Cell Value	contains "Completed"	text	NO
H24	Cell Value	contains "Almost done"	text	NO
H24	Cell Value	contains "Progress"	text	NO
H24	Cell Value	contains "Risk"	text	NO
H24	Cell Value	contains "Completed"	text	NO
H80:H96,I32	Cell Value	contains "Almost done"	text	NO
H80:H96,I32	Cell Value	contains "Progress"	text	NO
H80:H96,I32	Cell Value	contains "Risk"	text	NO
H80:H96,I32	Cell Value	contains "Completed"	text	NO
H67,I66,H61:H65,H13:H15	Cell Value	contains "Completed"	text	NO
H67:H77,I66,H35:H36,H26:H27,H31,H38,H13:H15,H43:H44,H49:H51	Cell Value	contains "Almost done"	text	NO
H67:H77,I66,H35:H36,H26:H27,H31,H38,H13:H15,H43:H44,H49:H51	Cell Value	contains "Progress"	text	NO
H67:H77,I66,H35:H36,H26:H27,H31,H38,H13:H15,H43:H44,H49:H51	Cell Value	contains "Risk"	text	NO
H67:H77,I66,H35:H36,H26:H27,H31,H38,H13:H15,H43:H44,H49:H51	Cell Value	contains "Completed"	text	NO
H17:H23,H53:H65	Cell Value	contains "Almost done"	text	NO
H17:H23,H53:H65	Cell Value	contains "Progress"	text	NO
H17:H23,H53:H65	Cell Value	contains "Risk"	text	NO
H17:H23,H53:H65	Cell Value	contains "Completed"	text	NO
H17:H23	Cell Value	contains "Completed"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C13:C44	List	=$X$13:$X$19

 

[vij]

Once I enter Completed in the last column "K" then the data for the site (Column C) is copied into a new sheet.
After the data is copied I want only data in column "1. Progress (top 3 accomplishments from this week). " to remain and the balance to be deleted.