Use List data to generate new list

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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.
 

Attachments

  • Blank Training Template for troubleshooting.xlsx
    49 KB · Views: 18

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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.
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
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?
 

Attachments

  • ExcerlGuru2714Blank Training Template for troubleshooting.xlsm
    59.9 KB · Views: 19

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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?
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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?
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
Sample Sheet w/ partially working example:
 

Attachments

  • Blank Training Template for troubleshooting.xlsx
    50.7 KB · Views: 24

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
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:

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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:

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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.
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
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:
 

Attachments

  • Troubleshooting Template.xlsm
    54.8 KB · Views: 17

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
Attached is an example of a rearranged MASTER sheet with tweaked code.
You can hide columns in the results sheet without upsetting the advanced filter.
 

Attachments

  • ExcerlGuru2714Blank Training Template for troubleshooting pd02.xlsm
    61 KB · Views: 13

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
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:
1.You haven't got unique headers in the MASTER sheet.
2.Connected to 1 above, you'll have to clear the target sheet so that headers are renewed (not needed if the headers are unique).

Original code will have to be tweaked to cope with deleting rows from the Master sheet (I'm not sure whether advanced filter deletes old rows if the new resultant list is shorter).
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
I guess I'm misunderstanding you. If by unique headers you mean each named entry only appears once, I believe I do have unique headers. If unique headers is a term that means something else, that may be where you're losing me.

Google isn't helpful on this subject, unfortunately, so I'm not able to identify the discrepancy. Can you explain?
 

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
EDIT: to answer your other question, there should never be any data on the "Area2" sheet that does not have "Area2" in Column E.
What I mean is, on the MASTER sheet, if you have Area2 in column E, will you ever have data in task columns other than Q, R S or T?
 
Last edited:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
I guess I'm misunderstanding you. If by unique headers you mean each named entry only appears once, I believe I do have unique headers. If unique headers is a term that means something else, that may be where you're losing me.

Google isn't helpful on this subject, unfortunately, so I'm not able to identify the discrepancy. Can you explain?
The headers are what's on row 1 of the Master sheet. You have multiple instances of Task1 for example.
Either make those headers all different (I don't think it's the way to go) or rearrange the data along the lines of my attachment in msg#11.
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
What I mean is, on the MASTER sheet, if you have Area2 in column E, will you ever have data in task columns other than Q, R S or T?
Understood. The answer is yes. My column E may have more than one Area - let's say on the example MASTER sheet, cell E4 and E13 are actually employees who are alternately assigned to multiple areas. They would have data in the master sheet under both areas they correspond to. Example attached.

The headers are what's on row 1 of the Master sheet. You have multiple instances of Task1 for example.
Either make those headers all different (I don't think it's the way to go) or rearrange the data along the lines of my attachment in msg#11.

OKay, you're absolutely right. I had not realized I had duplicate headers. I've updated the sample sheet to have all unique headers. However, the sheet is still behaving oddly: it populates the Area1 sheet with accurate data for the Area1 header, but also inaccurate data for the Area2 and Area3 headers, and populates nothing on the Area2 or Area3 sheet.

This behavior is also shown in the sample sheet attached.
 

Attachments

  • Troubleshooting Template revA.xlsm
    56.1 KB · Views: 8

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
try the attached. The results sheets are cleared out every time.
 

Attachments

  • ExcelGuru2714Troubleshooting Template revA.xlsm
    50.5 KB · Views: 11

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
I suspect this is different than what you see on that file, but when I open it, the Area1 Sheet shows erroneous data for tasks under Area2, the Area2 Sheet shows erroneous data for tasks under Area1, the Area3 sheet shows erroneous data for tasks under Area2, and the Area4 sheet is blank.

Please advise.
 

kaeroku

New member
Joined
Feb 25, 2014
Messages
29
Reaction score
0
Points
0
Okay my mistake, I was looking at it wrong.

If we could add the code we discussed to remove blank columns, this looks like it ought to work quite well.

(sorry for multiple replies, this 10 minute editing limit is quite silly.)
 
Last edited:

p45cal

Super Moderator
Staff member
Joined
Dec 16, 2012
Messages
2,168
Reaction score
10
Points
38
Excel Version(s)
365
It's curious that my attachment to msg#17 is showing as 0 views.
I included in an area results sheet any line on the MASTER sheet that included that area, so for example, if Area3 appeared anywhere in column E, even if alongside Area2, that row got included in the Area3 report (see row 11 of your image in the last message, I haven't checked, but I doubt that the data on the rest of that row is erroneus).
I did it by introducing wild card characters to the criteria (cells YC1:YC2 of each sheet):
Code:
.Range("YC1:YC2") = Application.Transpose(Array("Area", "*Area" & SheetNo & "*"))
but you could revert to the older code for this line if you want, but only the first Area mentioned in any particular row in column E of the MASTER sheet wll be used for the reports; so where you have Area2, Area3 in one cell in column E of the MASTER sheet, this will be treated as just Area2. This would mean that that row would not appeaer on the Area3 report at all.
 
Top