Log In
Register
Home
January 20, 2018
Offline
Hi All
Thank you for your interest to perhaps give me the answer i am looking for.
I produce an excel spreadsheet each week of my employees productivity. All the information is available in the one spreadsheet to start with.
What i want to do is automatically move all the columns in that initial table to individual worksheets based on the employee name.
The spreadsheet is already set up with the 20 tabs of the employees names and headers etc in the same position on each worksheet.
At the moment i an filtering on each employee name in the raw data sheet and then cutting and pasting all columns into the appropriate employee sheet.
Can this be automated. so that it identifies the staff member in the raw data sheet and copies all the column into the corresponding employee.
This would save so much time if someone could help.
Kind Regards
Newbie Matt
See Answer
VIP
April 21, 2015
Offline
There are different ways to accomplish this, if I understand your question well. Always difficult without real examples.
It sound the most easy to put all data on 1 sheet and make a Excel table from that. After that a Pivot Table, so you can make your reports on every employee.
You can add new data to that Excel table and refresh the Pivot Table.
Something like that?
Frans
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
I concur with Frans about using a PivotTable to accomplish your task.
You can refer here on how to do that: https://www.myonlinetraininghu.....tract-data
Sunny
Answers Post
January 20, 2018
Offline
Thanks for your replies i will try here is the headings of my data
id code clients name visit date visit time visit type future appointment clinician
123 joe bogs 18.1.18 12:00 emerg no dr savage
124 joe blow 18.1.18 12:00 general no dr pain
I want to have separate sheets for the drs with all the patient info on that sheet.
VIP
April 21, 2015
Offline
Hi Matthew,
From the data you provided I made this little example to start with.
On the same sheet as the data (with some extra to show what you can do) I also placed the Pivot Table. Normally you put that on another sheet.
If something like this is what you want, you can extract the data by clinician in a very easy way.
But first I want to know if this is in the direction you are looking for?
Frans
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
As an alternative to the PivotTable (or a VBA solution), you can also try this. It uses array formulas.
Without knowing how many rows of data you are dealing with, I only extract records until row 1000 of the DATA sheet and output until row 100 of each of the output sheets. Not 100% tested and don't know how it will affect the calculation speed (WARNING : Too many array formulas can slow down calculations in a workbook)
You will need to enter the clinician name in cell B1 of each output sheet. It will then extract the required info from the DATA sheet automatically.
Any additional record added to the DATA sheet will be auto updated to the respective worksheets.
Do not enter record pass row 1000 of the DATA sheet. If you need to add more records to the DATA or output sheets, you will need to adjust the formulas accordingly.
Hope this helps.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Here is a PivotTable equivalent. You only need to duplicate the output sheets and change the filter.
Just refresh the PivotTable if there are any additional/changes to the DATA sheet.
Sunny
January 20, 2018
Offline
thanks all, i am playing with all methods at the moment the most data i get in the month is no more that 500 lines.
i think the pivot table works well 🙂
January 20, 2018
Offline
I like this example also as it will do excatly what i have in mind. just trying to work out the array formulas 🙂
SunnyKow said
Hi MatthewAs an alternative to the PivotTable (or a VBA solution), you can also try this. It uses array formulas.
Without knowing how many rows of data you are dealing with, I only extract records until row 1000 of the DATA sheet and output until row 100 of each of the output sheets. Not 100% tested and don't know how it will affect the calculation speed (WARNING : Too many array formulas can slow down calculations in a workbook)
You will need to enter the clinician name in cell B1 of each output sheet. It will then extract the required info from the DATA sheet automatically.
Any additional record added to the DATA sheet will be auto updated to the respective worksheets.
Do not enter record pass row 1000 of the DATA sheet. If you need to add more records to the DATA or output sheets, you will need to adjust the formulas accordingly.
Hope this helps.
Sunny
January 20, 2018
Offline
HI Sunny
I have included the test sheet of what i am after. I have tried to play around with the forumla but i cant get it to work.
I have a worksheet called data and i am wanting to do the array formula thing to populate the data in from row 8 based on the provider named in c6. All worksheets are named as Drs to worksheet is set up also to count the number of entries and then a macro to convert to pdf and email the dr based on cell a6.
that array forumla would be the best thing ever
January 20, 2018
Offline
file here
January 20, 2018
Offline
this is the forumla i tried
{=IF(COUNTIF(Data!$H$8:$H$1000,$C$6)<ROWS($A$8:A8),"",INDEX(Data!$A$8:$A$1000,SMALL(IF(Data!$H$1:$H$1000=$C$6,ROW(Data!$G$8:$H$1000)),ROW(Data!H8))))}
VIP
Trusted Members
June 25, 2016
Offline
Hi Matthew
There is no attachment.
January 20, 2018
Offline
Hi SunnyKow,
I have now been using this formula for a year now and it has worked excellently.
{=IF(COUNTIF('Data 2019'!$A$1:$A$517,$A$2)<ROWS($C$1:C1),"",INDEX('Data 2019'!$C$1:$C$517,SMALL(IF('Data 2019'!$A$1:$A$517=$A$2,ROW('Data 2019'!$A$1:$A$517)),ROW('Data 2019'!B1))))}
What I would like to be able to do is now is the data in Cell $A2 which is the lookup value I need it to be able to search case sensetive as the row it is looking up in the data sheet $A$1:$A$517 can have the data GD or Gd and i need it to return the correct value.
hope this makes sense.
1 Guest(s)
