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

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.myonlinetraininghub.com/excel-pivot-tables-to-extract-data

Sunny

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.

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

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

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

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 🙂

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 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  

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

file here

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))))}

Hi Matthew

There is no attachment.

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.