Incoming personnel sheet with grouping moved to namned sheet

[Rickard74]

Hi

Hope i can translate my problem to you guys.

The purpose of this sheet is: up to 600 namned personel can join under one contribution. Ill use sheet1 for enrollment.
A2:A600 is a roll list of groups 1 to 10.
B2:B600 is there full name
C2:C600 is there mobil number
D2:D600 is there education
E2:E600 is date of arival
F2:F600 is date of discharge.

Now, i need to put these names into groups, and il rename Sheet2 to Group 1, Sheets3 to Group2 and so on.
Under group1 to 10 ill need to see the names and mobilenumber joind to that group.
And if one of them discharge and ill change group to notting or 0 they wont be in any group.

Is there anyone that can help me? I know office365 inside got this new not relesed formula (FILTER), but i can use it on that computer.

best regards
Rickard Österlind
Sweden

[BlindAlley]

Take a look at this template to see if you can adapt it to what you need.

The 1st lookup is a normal INDEX MATCH for Group 1 followed by array formulas which look up the 2nd, 3rd entries for Group 1 (ignoring group 2 or 3) and so on. Your 3rd tab would be named Group 2 and those formulas will do the same lookups for group 2 providing you enter the proper number in tab 1.

Array formulas are entered using 'Shift, Ctrl, Enter' btw, not what I wrote on the template.

[Rickard74]

Thank you

Im getting to it at ones. Ill try to get it done.
I can post it so you can see how i used it. But then its i swedish, well only the topics.

thank you again.

[vcoolio]

Hello Rickard,

I'm not totally sure if I have understood your request properly but the following VBA code, assigned to a button, may help:-

Sub CreateNewShtsTransferData()

        Dim sht As Worksheet
        Dim lr As Long, i As Long
        Dim GID As Object
        Dim key As Variant

        Set sht = Sheets("Enrolment")
        Set GID = CreateObject("Scripting.Dictionary")
     
Application.ScreenUpdating = False
Application.DisplayAlerts = False
     
lr = sht.Range("A" & Rows.Count).End(xlUp).Row
sht.Range("A2", sht.Range("F" & sht.Rows.Count).End(xlUp)).Sort sht.[A2], 1

For i = 2 To lr
        If Not GID.Exists(sht.Range("A" & i).Value) Then
        GID.Add sht.Range("A" & i).Value, 1
        End If
Next i

For Each key In GID.keys
        If Not Evaluate("ISREF('" & key & "'!A1)") Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = key
        End If
        If key = 0 Or key = vbNullString Then
        MsgBox "Any zero group values will not be accounted for. No sheet will created and no data will be transferred.", vbExclamation, "WARNING"
        Else
        Sheets(key).Cells.Clear
        sht.Range("A1:A" & lr).AutoFilter 1, key
        sht.[A1].CurrentRegion.Copy Sheets(key).[A1]
        Sheets(key).Columns.AutoFit
        sht.[A1].AutoFilter
        End If
Next key
        
        On Error Resume Next
        Sheets("0").Delete
        sht.Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

This code will actually create the new Group sheets for you based on the Group names that you can select from a drop down box in each cell in Column A in the "Enrolment" sheet. It will then transfer the relevant rows of data to their respective sheets. Should you select "0" from any of the drop downs, a message box will appear advising that row values will not be accounted for and no new sheet will be created or data transferred. On clicking a button, all sheets will be refreshed to account for any changes.
The data is sorted based on Column A prior to any data transfer taking place.

I've attached a sample workbook so that you can see how this would work. Click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.

[BlindAlley]

That's a very good VBA sheet vcoolio. Simple and quick, I like it.

[Rickard74]

Vcoolio, thats a peace of work. That worked fine. Thank you so mutch.
I can tweek it a little bit.

Thanks again
Rickard
Sweden

[Rickard74]

Hello BlindAlley

I have been working on this on and of for a couple days now. And i love it.
But, i find a glich in the system. If you have several personen with same last name, then it sends it over in faulty groups.
But i think i now how to fix it. If i just use one cell for both first and last name. Then it would be harder to get the error.

Do you think diffrent?

Thanks again, its really funny to getting in to it with excel.

[BlindAlley]

Yes, you could enter First & Last Names into one cell only, and then separate them again in the 2nd tab by using:
First name:

 =LEFT(H16, SEARCH(" ",H16) - 1)

and
Last name:

=RIGHT(H16,LEN(H16)-SEARCH(" ",H16))

Where H16 is the cell with the first & last names.

Basically, what you need is a unique identifier to look up each entry, like a persons mobile phone number, or Employee Number.

[vcoolio]

Thanks for the kind feed-back BA.

I'm still not sure if it's what the OP is after but...............

Cheerio,
vcoolio.