• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Running a script or something to move data based on employee to a specified sheet|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Running a script or something to move data based on employee to a specified sheet|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Running a script or something to mo…
sp_PrintTopic sp_TopicIcon
Running a script or something to move data based on employee to a specified sheet
Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
1
January 22, 2018 - 12:23 am
sp_Permalink sp_Print

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 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
2
January 22, 2018 - 2:29 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
3
January 22, 2018 - 1:00 pm
sp_Permalink sp_Print

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

sp_AnswersTopicAnswer
Answers Post
Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
4
January 23, 2018 - 8:44 pm
sp_Permalink sp_Print

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.

Avatar
Frans Visser
Duivendrecht (near Amsterdam) - The Netherlands

VIP
Members
Level 2
Forum Posts: 346
Member Since:
April 21, 2015
sp_UserOfflineSmall Offline
5
January 24, 2018 - 5:19 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
January 24, 2018 - 12:20 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
7
January 24, 2018 - 12:52 pm
sp_Permalink sp_Print

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

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
8
February 2, 2018 - 11:03 pm
sp_Permalink sp_Print

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 🙂

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
9
February 2, 2018 - 11:09 pm
sp_Permalink sp_Print

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  

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
10
February 3, 2018 - 12:31 am
sp_Permalink sp_Print

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

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
11
February 3, 2018 - 12:32 am
sp_Permalink sp_Print

file here

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
12
February 3, 2018 - 12:41 am
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
13
February 3, 2018 - 10:26 am
sp_Permalink sp_Print

Hi Matthew

There is no attachment.

Avatar
Matthew Tucker
Member
Members
Level 0
Forum Posts: 11
Member Since:
January 20, 2018
sp_UserOfflineSmall Offline
14
February 4, 2019 - 12:48 pm
sp_Permalink sp_Print

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.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Geoff McClure, Riny van Eekelen, David Jernigan, Cassie Bernier
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 215
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Ngoc Qui Nguyen
Clement Mansfield
Rose .
Bindu Menon
Baruch Zemer
Purple RainbowBenefactor
MOTH Junkie
Forum Stats:
Groups: 3
Forums: 24
Topics: 6350
Posts: 27773

 

Member Stats:
Guest Posters: 49
Members: 32317
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.