Syntax for unique values

[terryg]

Hi,
I do not know VBA code and standard excel formulae cannot perform the following;


I have builders with different skill sets who work in different areas who can be assigned to any job at any address as long as it meets those 2 criteria.

Now I need a method of counting how many different addresses a builder is assigned to, it is complicated because they can have more than 1 job at an address but I want to count the addresses they have been assigned to.
this is further complicated by the fact that I only want to reckon with jobs that are in certain statuses.

please see attached sheets, contractors names are in sheet 'contractors' column B and I want the above result displayed in column I of that sheet.
jobs are recorded in sheet 'jobs' and their contractor is stored in column K.

Many thanks for help.

[SamT]

If you had a Assigned Contractor Name Column on the Jobs sheet, it would be a piece of cake. You could use the CountIf function.

I have improved, and, designed from scratch, a lot of Excel management systems, and there comes a point of complexity in all systems where it is best to design a new one from scratch using the experience and management knowledge gained by use of the original. I think you have arrived at that point.

However, I am not taking any projects on at this time.You might contact the VBAExpress Forum owners, who are in the business of developing systems like you need, and this one is actually pretty basic. Just go to the forum home page, http://www.vbaexpress.com/, and their contact info is all there.

On the other hand, we do have some experts here at the forum who work in GB. Very probably, one of them will see this thread and may reply to you.

[terryg]

[QUOTE=SamT;337260]If you had a Assigned Contractor Name Column on the Jobs sheet, it would be a piece of cake. You could use the CountIf function.

there is such a column there (column k), and I used couuntif, but I cant get that formula to only count uniquly at that address.

[SamT]

If you had a Assigned Contractor Name Column on the Jobs sheet, it would be a piece of cake. You could use the CountIf function.
I see. I take it back. You would need more than that. You would need an Addresses column for each Contractor

In this attachment, 15302, I have a suggestion as to how to better lay out your tables, if you look at this lay out as a way to make Excel report only those things that you think you want, it won't make sense.

However, if you look at it as a way for Excel to record everything that you might want report on, then you will see that almost everything can be computed with Formulas, albeit some very complex Formulas. This style of table layout is usually best for even minorly complex workbooks as it keeps all information gathered in related groups

You will notice that I used some example formulas to demonstrate the use of Hidden Helper columns, but I don't expect them to work well at all, because I am just not a Formula user and have very limited experience with them.