coverting a list of employees details into team lists on a separate sheet.

I have a list of employees with their teams and grades etc which I need to replicate automatically on a different sheet, but also in descending grade order.

my main list contains all the relevant info but I am struggling to create an (array?) formula to copy them across

Hi Chrisk67,

PFA and PFB for the updated code. I hope this code will help.

Please Login or Register  to view this content.

Regards
Ramandeep Singh
Please Click STAR to Add Reputation if my answer helped!

Put this formula in G4 of Sheet1:

=A4&"_"&COUNTIF(A$4:A4,A4)

and copy down to the bottom of your data. This will identify the team and add on a unique sequential number for each team member.

Then, with the team number in A1 and the headings in row 3, you can use this formula in A4 of Sheet2:

=IFERROR(INDEX(Sheet1!$A:$F,MATCH($A$1&"_"&ROWS($1:1),Sheet1!$G:$G,0),MATCH(A$3,Sheet1!$A$3:$F$3,0)),"")

Copy this across and down as required.

You can put another team name in G1 and use this formula in G4:

=IFERROR(INDEX(Sheet1!$A:$F,MATCH($G$1&"_"&ROWS($1:1),Sheet1!$G:$G,0),MATCH(G$3,Sheet1!$A$3:$F$3,0)),"")

(note the slight difference in red), then copy this across and down.

Hope this helps.

Pete

Hi Chrisk67,

Or you could do it without any VBA or formulas just with 2 Pivot Tables - see attached.

Regards

peterrc

Thanks for this, could I modify it so all the revised tables would appear on the same page

Thanks, it almost produces what I need, but unless I have misread it i dont see anything to sort by grade

Hi Chrisk67,

The macro which i shared produce different sheet for each Team and also sort the output by grade. In future, if any new team is included suppose like Team3. The macro i shared is capable of catering the new Team (Team3). Macro will generate separate sheet for Team3 and also sort the result by grade.

You can check the functionality of the macro by clicking the "Submit" button on the main sheet.

Regards
Ramandeep Singh
Please Click STAR to Add Reputation if my answer helped!

Well, my solution reproduces the tables that you had in the yellow area - in what way(s) do you want it different to that? A strict alphabetic sorting by grade would produce the order HO, O, SO (or the reverse).

Pete

Pete

I was looking to order by grade down SO-HO-O. I am assuming that as names are added the they would never be in grade order. thanks for your help on this

Chris

thanks for your help on this. I don't think I explained my query. your solution does everything but I was hoping all the output would be on one sheet no matter how many teams. I could then rename it with a date every time there was a change in staffing levels. My full sheet will cover 7 teams and 70 staff so the number of sheets would get out of hand quite quickly with separate tabs for each one. thank you very much for all your help on this.

Chris

Originally Posted by chrisk67

thanks for your help on this. I don't think I explained my query. your solution does everything but I was hoping all the output would be on one sheet no matter how many teams. I could then rename it with a date every time there was a change in staffing levels. My full sheet will cover 7 teams and 70 staff so the number of sheets would get out of hand quite quickly with separate tabs for each one. thank you very much for all your help on this.

Chris

Hi Chirs,

I have modified the macro as per your requirements. Now, macro display record in the same sheet and sort by grade.

I hope this code will help. PFA and PFB for the macro.

Please Login or Register  to view this content.

Regards
Ramandeep Singh
Please Click STAR to Add Reputation if my answer helped!

Ramandeep

thanks for your help on this, it work with one slight glitch. When I add a new team it pushes the formatting for new teams across one column so there are no gaps between the names. Is it straight forward to create all of the teams on a new sheet each time I submit. so that if i ran it today it would put all the teams on sheet 2. If i then added a team and re ran it would put them all on sheet 3. If it is too complicated I can get around by deleting the teams before re submitting

thnaks again for all your advice

chris

Hi Chris,

PFA and PFB for the modified code as per your requirement. I hope this code will help.

Please Login or Register  to view this content.

Regards
Ramandeep Singh
Please Click STAR to Add Reputation if my answer helped!

Hi Dave,

I think a simple MATCH of the grade in column B against those in cells A11:A13 will result in 1, 2, or 3, so these would be a better parameter to use for sorting rather than the FTE. The problem is that I don't know where A11:A13 would be on the real file, as the OP mentions about adding more data (and having 70 lines already).

Hope this helps.

Pete

perfect, thanks for your help

Chris

tanks for your help. I have managed to get a workable solution with everybody's help.

Hi Chris,

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete