Boil out duplicates

[jomili]

I'm not going to reveal the solution I'm using, because I'm SURE it can done much more easily. I'm sure I'm overthinking this.

Background: A weekly Position Report comes out. I have an automated workbook which my teammembers use to check that report various ways. The entire process is automated, they select their regions and the checks initiate and a report is generated to them. All new checks need to be incorporated into the same process. A new check has been requested.

The problem to solve: In the attached, I have 3 columns: Job Title, Job Cd, and Unit
A job code can have only 1 job title, so I need to boil out the job codes having more than one job title. Once I have these isolated, I need to copy the Job Title, Job Cd, and unit to another worksheet. So, for my team, they need to see only those job codes that have more than one job title, and the Unit those job codes/titles exist in. A count of how many of each would be nice, too, kind of like this:

Please Login or Register  to view this content.

.

My issue is that I can't figure the best/easiest way to isolate that particular data.

[Marcol]

See if this helps, I've got to go so can't take it further for now.

Copy Column B "Job Cd" from Sheet1 to Sheet2 Column A Then use the facility Data > Remove Duplicates, this will cut the load to process dramatically.

Look at the Names Manager to see how to use Dynamic Named Ranges to process only the exact amount of data required.

I''l come back later to see if this gives you a start, and any comments/queries you might have.

[jomili]

Marcol,

Looking at your sample, and your directions, I fear I must have mistated my problem. In a nutshell, it's not how many duplicates are in the column, but if a single value in B (Job Code) has more than one unique counterpart in Column A (Job Title). IF there are any, then for those I need Job Code, Job Title, and Unit. The only one in the sample data I gave is Job Code 5026K, which has two different job titles. There are different ways to boil that out, using pivots, or concatenating columns and filtering. I have a way, but it's pretty complex, and I'm sure there's an easier/faster way. That's what I'm looking for.

[Marcol]

Okay I'll look again when I get back later tonight.

[Marcol]

See if this workbook takes us a step further.

With Sheet2

1/. Copy Sheet1 Column B to Sheet2 Column A, then use Data > Remove Duplicates to get a list of "Unique Job Cds", this reduces dramatically, the crunching to be done later.
This can be done with formula, but it is a real memory hog.

2/. Use Column B to count the instances of each Job Cd, eliminating these that only appear once.
This returns the row numbers for each multi-instance Job Cd.

3/. in Column C, reduce the number of Job Cds to be checked for multiple Job Titles, using Column B as an index.

4/. Column D does as it says.

5/. Column E checks if there is an imbalance between the Job Cd count and the count of the first title found.
If there is no imbalance then "" is returned, otherwise the first dis-similar title is returned.
Dragging this Across returns any other titles found.

6/. Filtering out the blanks in Column E "Other Titles 1" will reveal the Job Cds with multiple Job Titles.

Columns A:B can be hidden with the +/- grouping button.

See the names Manager for the dynamic names used to reduce the data to the exact range calculated.
Don't be tempted to change these to refer to whole columns, it will cripple your sheet.

[Marcol]

See if this isn't a better approach.

I would still use the Remove Duplicated to get the starting data.

This extracts only the Job Cds that have multi-job titles.

If the first title found is not the most common one, we might have problems. If this is likely to happen we can look again.

As this file stands it will handle 20 Job Cds that have multi-job titles, and up to 4 job titles, just drag Across and Down to extend these limits.

[Marcol]

Is anybody there whooooo? ...

jomili ... call home.

Knock, knock. Who's there? Jomili. Come in!

[jomili]

Marcol,

Please accept my apologies for not responding. I've been pulled away for the last week, so just saw your post. Your approach appears even more complex than mine, so I thought I'd post mine in hopes that someone can fine-tune it. The goal is for my team to have a list of the problems they need to deal with in the Active Position report, so on their end they need it short and sweet. If you'll open the attachment to Sheet1, then run the "dupl" macro, Sheet 3 will populate with the desired output. The only thing not handled correctly is the "Unit"' the larger number, instead of reflecting the unit where the majority reside, should just say "All". Here's my "Dupl" macro:

Please Login or Register  to view this content.