Center wise duty list creation from an existing mixed duty list

awanak

awanak

New Member
Joined
Oct 6, 2018
Messages
37
Office Version
  1. 2019
Platform
  1. Windows
I want to create a center wise duty list from an existing mixed center wise duty list

Mixed center wise duty list
[TABLE="width: 302"]
<tbody>[TR]
[TD]S.No
[/TD]
[TD]Name
[/TD]
[TD]Center
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Gul Nisar
[/TD]
[TD]Lahore-1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Jamil Ahmad Siddiqui
[/TD]
[TD]Lahore-5
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Muhammad Ashraf
[/TD]
[TD]Lahore-2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Naeem Akhtar
[/TD]
[TD]Lahore-4
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Munir Ahmad Malik
[/TD]
[TD]Lahore-2
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Muhammad Amjad
[/TD]
[TD]Lahore-2
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Muhammad Saleem Arain
[/TD]
[TD]Lahore-3
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]Zubair Khan
[/TD]
[TD]Lahore-1
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]S.M. Ibrahim
[/TD]
[TD]Lahore-1
[/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]Muhammad Khan
[/TD]
[TD]Lahore-7
[/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]Bashir Ahmad
[/TD]
[TD]Lahore-6
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Naseer Khan
[/TD]
[TD]Lahore-9
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]Fida Muhammad
[/TD]
[TD]Lahore-2
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD]Alam Shah
[/TD]
[TD]Lahore-3
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]Asghar Ali Asghar
[/TD]
[TD]Lahore-6
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]Mumtaz Ali
[/TD]
[TD]Lahore-5
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]Iftikhar Ahmad
[/TD]
[TD]Lahore-1
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]Sardar Javed Akhtar
[/TD]
[TD]Lahore-6
[/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]Shoukat Ali Mirza
[/TD]
[TD]Islamabad-1
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]Muhammad Boota Asim
[/TD]
[TD]Islamabad-1
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]Muhammad Rukhtaj
[/TD]
[TD]Lahore-6
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]Tanveer Muhammad Khan
[/TD]
[TD]Islamabad-2
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]S.Imran Hassan
[/TD]
[TD]Islamabad-2
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]Shakeel Ahmed
[/TD]
[TD]Islamabad-1
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD]Imran Asghar
[/TD]
[TD]Islamabad -1
[/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]Tariq Ali Lahori
[/TD]
[TD]Lahore-1
[/TD]
[/TR]
</tbody>[/TABLE]

Center wise Duty list to be created
[TABLE="width: 181"]
<tbody>[TR]
[TD]Name & Center
[/TD]
[/TR]
[TR]
[TD]Islamabad-1
[/TD]
[/TR]
[TR]
[TD]Imran Asghar
[/TD]
[/TR]
[TR]
[TD]Shoukat Ali Mirza
[/TD]
[/TR]
[TR]
[TD]Muhammad Boota Asim
[/TD]
[/TR]
[TR]
[TD]Shakeel Ahmed
[/TD]
[/TR]
[TR]
[TD]Islamabad-2
[/TD]
[/TR]
[TR]
[TD]Tanveer Muhammad
[/TD]
[/TR]
[TR]
[TD]S.Imran Hassan
[/TD]
[/TR]
[TR]
[TD]Lahore-1
[/TD]
[/TR]
[TR]
[TD]Gul Nisar
[/TD]
[/TR]
[TR]
[TD]Zubair Khan
[/TD]
[/TR]
[TR]
[TD]S.M. Ibrahim
[/TD]
[/TR]
[TR]
[TD]Iftikhar Ahmad
[/TD]
[/TR]
[TR]
[TD]Tariq Ali Lahori
[/TD]
[/TR]
[TR]
[TD]Lahore-2
[/TD]
[/TR]
[TR]
[TD]Muhammad Ashraf
[/TD]
[/TR]
[TR]
[TD]Munir Ahmad Malik
[/TD]
[/TR]
[TR]
[TD]Muhammad Amjad
[/TD]
[/TR]
[TR]
[TD]Fida Muhammad
[/TD]
[/TR]
[TR]
[TD]Lahore-3
[/TD]
[/TR]
[TR]
[TD]Muhammad Saleem Arain
[/TD]
[/TR]
[TR]
[TD]Alam Shah
[/TD]
[/TR]
[TR]
[TD]Lahore-4
[/TD]
[/TR]
[TR]
[TD]Naeem Akhtar
[/TD]
[/TR]
[TR]
[TD]Lahore-5
[/TD]
[/TR]
[TR]
[TD]Jamil Ahmad Siddiqui
[/TD]
[/TR]
[TR]
[TD]Mumtaz Ali
[/TD]
[/TR]
[TR]
[TD]Lahore-6
[/TD]
[/TR]
[TR]
[TD]Bashir Ahmad
[/TD]
[/TR]
[TR]
[TD]Asghar Ali Asghar
[/TD]
[/TR]
[TR]
[TD]Sardar Javed Akhtar
[/TD]
[/TR]
[TR]
[TD]Muhammad Rukhtaj
[/TD]
[/TR]
[TR]
[TD]Lahore-7
[/TD]
[/TR]
[TR]
[TD]Muhammad Khan
[/TD]
[/TR]
[TR]
[TD]Lahore-9
[/TD]
[/TR]
[TR]
[TD]Naseer Khan
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 271"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
The "Center" Column !!
Alter code as below in Red:-

Code: 
For Each Dn In Rng
  [COLOR="#FF0000"][B]If Not Dn.Value = vbNullString Then
[/B][/COLOR]    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Dn.Offset(, -3) & ", " & Dn.Offset(, -2).Value & " (" & Dn.Offset(, -1).Value & ")"
    Else
        .Item(Dn.Value) = .Item(Dn.Value) & "/" & Dn.Offset(, -3) & ", " & Dn.Offset(, -2).Value & " (" & Dn.Offset(, -1).Value & ")"
    End If
  [COLOR="#FF0000"][B]End If
[/B][/COLOR]Next
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I have another question,
I have detail of centers with the date of exams at sheet3 as given below
[TABLE="width: 330"]
<tbody>[TR]
[TD]Center[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]Lahore-1[/TD]
[TD]10.11.2018 to 20.11.2018[/TD]
[/TR]
[TR]
[TD]Lahore-2[/TD]
[TD]11.11.2018 to 15.11.2018[/TD]
[/TR]
[TR]
[TD]Islamabad-1[/TD]
[TD]12.11.2018 to 15.112018[/TD]
[/TR]
[TR]
[TD]Karachi-2[/TD]
[TD]11.11.2018 to 15.11.2018[/TD]
[/TR]
[TR]
[TD]Lahore-1[/TD]
[TD]10.11.2018 to 20.11.2018[/TD]
[/TR]
</tbody>[/TABLE]
I want to pick up the date when i enter the center name of an employee as given below pn sheet 1.

[TABLE="width: 608"]
<tbody>[TR]
[TD]S.No[/TD]
[TD]Name[/TD]
[TD]Designaiton[/TD]
[TD]Place of Postings[/TD]
[TD]Center[/TD]
[TD]Dates[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]DG[/TD]
[TD]CE Wing[/TD]
[TD]Lahore-1[/TD]
[TD]10.11.2018 to 20.11.2018[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]DG[/TD]
[TD]Recruitment Wing[/TD]
[TD]Lahore-2[/TD]
[TD]11.11.2018 to 15.11.2018[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]C[/TD]
[TD]DG[/TD]
[TD]C&R Wing[/TD]
[TD]Islamabad-1[/TD]
[TD]12.11.2018 to 15.112018[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]Chief [/TD]
[TD]IT Wing[/TD]
[TD]Karachi-2[/TD]
[TD]11.11.2018 to 15.11.2018[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]D[/TD]
[TD]DG[/TD]
[TD]Admin Wing[/TD]
[TD]Lahore-1[/TD]
[TD]
10.11.2018 to 20.11.2018​
<strike></strike>
[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
 
Upvote 0
Try this in your Sheet 1 module
This is a Change event, so when a value is changed in column "E" the Related date (from sheet3)should appear in column "F".
Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Column = 5 And Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet3")
[COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Target.Value = Dn.Value [COLOR="Navy"]Then[/COLOR]
        Target.Offset(, 1).Value = Dn.Offset(, 1).Value
        [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
sheet 1 already has the following code. how will the above code be nested into it

Sub MG21Oct21()
Dim Rng As Range, Dn As Range, n As Long, Dic As Object, nn As Long, Sp As Variant, c As Long
Dim K As Variant, KK As Variant, Txt As String


Set Rng = Range("E2", Range("E" & Rows.Count).End(xlUp))
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare


For Each Dn In Rng
If Not Dn.Value = vbNullString Then
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Dn.Offset(, -3) & ", " & Dn.Offset(, -2).Value & " (" & Dn.Offset(, -1).Value & ")"
Else
.Item(Dn.Value) = .Item(Dn.Value) & "/" & Dn.Offset(, -3) & ", " & Dn.Offset(, -2).Value & " (" & Dn.Offset(, -1).Value & ")"
End If
End If
Next
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare


ReDim Ray(1 To Rng.Count * 3, 1 To 2)
For Each K In .keys
Txt = Split(K, "-")(0)
Dic(Txt) = Dic(Txt) + 1
Next K
c = 1
Ray(c, 1) = "Center:=Name/Desg/Post"
For Each KK In Dic.keys
For n = 1 To Rng.Count * 3
If .Exists(KK & "-" & n) Then
c = c + IIf(c = 1, 1, 2)
Ray(c, 1) = KK & "-" & n
Sp = Split(.Item(Ray(c, 1)), "/")
For nn = 0 To UBound(Sp)
c = c + 1
Ray(c, 1) = Sp(nn)
Next nn
End If
Next n
Next KK
With Sheets("Sheet2").Range("B1").Resize(c)
.Value = Ray
.Borders.Weight = 2
.Columns.AutoFit
End With
End With
End Sub
 
Upvote 0
The latest code is a "Change_Event" code, and works separately from the previous code
This is a Change event, so when a value is changed in column "E" the Related date (from sheet3)should appear in column "F".
Click to expand...

To load the code:-
Right click sheet1 tab, Select "View Code" , vbwindow appears, Paste code into vbwindow.
Close Vbwindow
When you now enter a "Center" in column "E" the related "Date" from sheet3 should show in column "F".
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

A
  • Question Question
Find entries in a sheet available or not
2
Replies
10
Views
494
AFZAL SOHAIL
A

Forum statistics

Threads
1,224,830
Messages
6,181,229
Members
453,026
Latest member
cknader

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top