+ Reply to Thread
Results 1 to 10 of 10

Updating changed employees on a lengthy spreadsheet

  1. #1
    Registered User
    Join Date
    04-27-2017
    Location
    New York
    MS-Off Ver
    MS 365 v2211
    Posts
    85

    Updating changed employees on a lengthy spreadsheet

    Hello,

    I am trying to update a giant list of clients for 2019 (thousands of rows, so I can't do this manually).

    The 2019 list represents a list of clients, their ID number, the manager and all of their associates.
    The 2018 list represents a list of clients, their ID number, the manager and ONLY their main associate.

    I need to highlight or somehow isolate instances of changed managers, then do the same for changed associates. Here's the issue: It's not Apples-to-Apples, because some clients have left or changed and many clients swapped managers and/or associates. The old list only includes the main associate, but the new list sometimes has duplicate rows for clients with multiple associates.

    I'm open to your ideas for efficiency.

    Thank you. See two example sheets on the attached.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Updating changed employees on a lengthy spreadsheet

    Power Query could do this. See the attachment. I loaded both tables into queries, then ran a merge query to find all the rows with Client and Manager in 2019 that were not in 2018 (these would be items which changed). Then ran another comparing Client and Assistant from 2018 to 2019. You can also check for items in 2018 but not in 2019 if that matters to you. If the client is not listed in the 2019 list, it would not list it as changed unless you ran those queries as well.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Updating changed employees on a lengthy spreadsheet

    Sorry, should have added that Power Query is available from microsoft as an add-in for 2013.

  4. #4
    Registered User
    Join Date
    04-27-2017
    Location
    New York
    MS-Off Ver
    MS 365 v2211
    Posts
    85

    Re: Updating changed employees on a lengthy spreadsheet

    Thanks for the speedy reply, gjcase.

    I received some new information since the original post and it turns out that the new sheet will be winnowed down to one entry per client (no matter how many associates). With this new detail, is the end result within reach without adding Power Query? Aiming to stick with existing software if possible.

    Best,

  5. #5
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Updating changed employees on a lengthy spreadsheet

    Well, yes, you could do a set of lookups between the two tables to find the client/manager combinations for each client from the second table to compare with the first, and highlight if different; then run the same lookup comparing the second to the first if needed. You'd need to create a combination client+manager field for each table on which to run the lookups, i.e., a column with the combination, such as Really Good Guitars/Charlie. If that doesn't exist in the other table, then there was a change. You could use a pivot table to extract the unique values for each table and run the lookups on the tables of unique values to make things easier. However, that's still a lot of work, I still think you'd be better off trying out PQ.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Updating changed employees on a lengthy spreadsheet

    @ gjcase

    That may not be an option for OP.

    I seem to recall that PQ is only available for 2013 Pro+. I went through the frustration of finding this out the hard way back in my 2013 days.
    Dave

  7. #7
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Updating changed employees on a lengthy spreadsheet

    Dave:

    See this site, it appears to be available for over versions of Excel 2013:

    https://www.microsoft.com/en-us/down...8-a4c94ab1682b

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Updating changed employees on a lengthy spreadsheet

    Thank you for that gjcase.

    I seem to recall reference (Water Cooler) to that add-in coming. The promises / release dates were rather vague.

    I noticed the published date in that link was 7/9/2018. I moved on to 365 about that same time so can not check it out.

    Thanks just the same. Will pass it on.

    Dave

  9. #9
    Forum Contributor
    Join Date
    08-08-2005
    Location
    Kansas, USA
    MS-Off Ver
    2016
    Posts
    293

    Re: Updating changed employees on a lengthy spreadsheet

    Sorry for the late reply, but Power Query is built into 365, as Data/Queries and Connections

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Updating changed employees on a lengthy spreadsheet

    Yes. Thank you. I was referencing OP's profiled version as well as my ignorance regarding the current addins available for 2013.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 09-20-2016, 01:26 PM
  2. [SOLVED] Changed named range not updating in formulas
    By jason.holtzen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 03:42 PM
  3. Replies: 2
    Last Post: 03-11-2013, 09:59 PM
  4. Replies: 0
    Last Post: 12-10-2011, 11:14 AM
  5. Chart not updating when data changed
    By Thomas-W in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-20-2011, 01:36 PM
  6. Why is my spreadsheet not updating changed formulas?
    By Robertson in forum Excel General
    Replies: 1
    Last Post: 03-08-2006, 07:45 PM
  7. [SOLVED] Excel not updating when figures are changed
    By karenb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-28-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1