Page 1 of 5 1 2 3 ... LastLast
Results 1 to 10 of 49

Thread: Use List data to generate new list

  1. #1

    Use List data to generate new list



    Register for a FREE account, and/
    or Log in to avoid these ads!

    This seems like it should be simple, but I've been struggling with it the last few days:

    I have a MASTER sheet, and several other sheets which need to display portions of the MASTER sheet data.

    My MASTER sheet has a list of employee names (Column A) and the areas they work in (Column E.) I want to pull a list of employee names (Column A from the MASTER) working in Area1 (sorted Column E on the MASTER sheet) to Column A on the Area1 SHEET, then pull a list of employee names working in Area2 to column A on the Area2 SHEET, etc...

    I want it to do this in such a way that if I add an employee to Column A on the MASTER data sheet, it will populate that employee in Column A on the Area1 Sheet.

    So basically, I'm looking at one column on the MASTER sheet to see if the area matches. Then looking at another column on the MASTER sheet to get the name. Then taking that name and transposing it to a new sheet corresponding to the area they work in.

    I've attached a sample sheet. I want all names on the Master Sheet Column A to appear on the Area1 Sheet Column A that match the Master Sheet Column E Area1. Likewise for Area2, Area3, etc...

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Just want to make sure: is my question clear, or do you need additional details? I tried to be as descriptive and to the point as possible.

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    In the attached I've added 3 sheets called Area1b, Area2b and Area3b (so as to preserve your existing sheets). They are completely blank right now.
    I've put a Sheet_Change event handler in the MASTER sheet:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    blah
    End Sub
    I've made a little macro in a standardcode module (Module1):
    Code:
    Sub blah()
    SheetNo = 1
    For Each Sht In Sheets(Array("Area1b", "Area2b", "Area3b"))
      With Sht
        .Range("YC1:YC2") = Application.Transpose(Array("Area", "Area" & SheetNo))
        Sheets("MASTER").Range("A1:XZ1000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("YC1:YC2"), CopyToRange:=.Range("A1"), Unique:=False
      End With
      SheetNo = SheetNo + 1
    Next Sht
    End Sub
    Anytime you change anything on the MASTER sheet, these 3 sheets will get refreshed.
    It doesn't work fully at the moment because the technique uses Advanced filter, which needs unique headers for every column in the Master sheet. Do that and you should get a full complement of data in the 3 sheets.
    The data in the MASTER sheet does not need to be sorted.

    Is this something you could use?

  4. #4
    Yes, very much so! I created an Area4b sheet, updated the code you entered to include "Area4b" as a Sht, and added a line to the Master with an Area4 employee, and the Area4b sheet updated with the given info.

    This is different than the solution I was going for - trying to use a formula to scrape matching values off the master sheet - but achieves functionality which accomplishes one of the things I need (data replication)

    However, it raises a new problem: one of the purposes of moving to a new sheet for each area is to remove the unique headers corresponding to areas that are not the indicated area. From the look of it, the solution you gave won't allow me to remove those headers as it will break the advanced filter.

    My actual worksheet is about 400 columns long and has potential to increase. I do not want every employee record to require searching through 400+ columns of data. Is there a workaround in your solution for this, or a way to scrape the names from each area into individual sheets like my original request?

  5. #5
    So it looks like I have something working. Basically for the Area Sheets in Column A:

    =IF(COUNTIF(MASTER!$E1,"Area1"),MASTER!$A1,"")

    This scrapes the Master Sheet for all records matching the Area, then outputs the name from the parallel column in the reference column on the new sheet. This allows my VLOOKUP function to pull the record for that employee. I have to sort out blanks, but that's not really an issue.

    The only issue I still have is that adding new records to the Master won't update in the Area-specific sheets unless I force it to sort again.

    Is there any way to adapt the code you wrote for me to basically force all sheets to refresh their sorts (AND remove blanks) whenever a change is made in the Master sheet, without using advanced filter?

  6. #6
    Sample Sheet w/ partially working example:
    Attached Files Attached Files

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,356
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by kaeroku View Post
    However, it raises a new problem: one of the purposes of moving to a new sheet for each area is to remove the unique headers corresponding to areas that are not the indicated area. From the look of it, the solution you gave won't allow me to remove those headers as it will break the advanced filter.
    Well, you've got task numbers across the top for every Area, as well as the Area number in column E! Will there ever be data, for example, in Area2 task numbers columns for a row where column E does not contain Area2?
    If you can say there never will be such data, then you only need Task1, Task2 etc. across the top once. You will need to rearrange the MASTER data sheet a bit (can be automated). I see there's an Area1.2 in there which might put a spanner in the works.

    There are several other options; one is to still have unique headers in the MASTER sheet, but to seed the result sheets with only the headers/columns you want to see and stick with advanced filter.
    Another is to write some more code that simply hides all columns that are empty on the results sheets, but that may still show columns you don't want to see, depending on your answer to the "Will there ever be data.." question above. (edit post posting: Actually, have you tried hiding (not deleting) the columns you don't want on each results sheet - it might work still - try it on the workbook I attached last time)
    Another is to hard code the columns you want to see for each results sheet - quite a task, and makes it difficult to make changes to the MASTER sheet without having to change the code.
    Your solution of using formulae will be harder work (and as you say, only the first row from the MASTER sheet satisfying the VLookUp formula will ever show in the results sheet, so you would need to amend the formulae to cope with that) and that too would restrict changes in layout to the MASTER sheet.

    Another solution might be to use Pivot Tables. A pivot table for each results sheet, with specified headers from the MASTER sheet, but still, you'd need unique headers across the top of the MASTER sheet.

    My strong preference would be to rearrange the data in the MASTER sheet so that you only have the headers Task1, Task2 etc. each appearing once only (and if you have areas called Area1.2 and similar, put that data in column E, we can still get advanced filter to filter for Area1 and anything beginning 'Area1.' so it will cope with Area1.2, Area1.33 and so on).

    Is it possible for the data to be rearranged thus, or have you little choice as to how the data comes in/gets updated?
    Last edited by p45cal; 2014-02-26 at 02:55 PM.

  8. #8
    Just by reading I can see it is likely one or more of those will work. Let me play a bit and get back to you (might be tomorrow, I have long meetings soon.) If you can post an in-practice example of your suggestion(s), that would be great.

    For ease of cooperation: unique headers is not a problem, what I am currently using is one unique instance of each header. Maybe my example doesn't reflect that, or maybe I'm misunderstanding exactly what you're saying, which is why I'd like to see an example of what you mean versus the current state.

    As far as rearranging, that's very do-able, but I'm not certain I understand the why or the result of doing so. If you're willing to show me the light, I think we're very close to a solution here.

    EDIT: to answer your other question, there should never be any data on the "Area2" sheet that does not have "Area2" in Column E.

    There ARE situation in which column E will not contain Area2 *specifically,* (ie, Column E may at times say "Area1, Area2") although this may be avoidable. I have a separate issue which I had not addressed here, that being that some of my employees have multiple functional areas. I could create multiple Area columns to address this, but then our code would need to cross reference and match an employee that had, for instance, Area2 in EITHER Column E (EmployeeWorkstation1), Column F (EmployeeWorkstation2), Column G (EmployeeWorkstation3) etc... I'm sure this is do-able, but I'm not sure how, or whether it is the best solution.
    Last edited by kaeroku; 2014-02-26 at 03:19 PM.

  9. #9
    UPDATE: Hiding works, but it will create overhead if/when a column is added with a new task for a given area. I consider this issue minor, but would prefer to avoid it. My users are not very savvy, and among them there is a wide span of competence which will impact usability.

  10. #10
    Just discovered another issue: the example sheet you posted earlier does not copy over task data for Area2, Area3, etc. It does work for Area1, but I haven't been able to debug it.

    Also note Area4 sheet-- it copied new entry "Z Z" as a name but not the remainder of the data. Something is preventing complete transcribing. Any ideas?

    Here's the sheet with your code partially functional:
    Attached Files Attached Files

Page 1 of 5 1 2 3 ... LastLast

Tags for this Thread

Posting Permissions

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