No duplication formula

[civilred]

Hi all,

Hope I can explain this properly.

I have one column that has vacancy numbers in. The second column tells me if the vacancy is Live, Suspended or Closed. I am looking to count the Live vacancies but don't want to count duplicate numbers eg.

A B
Vacancy No Vacancy
10211 Live
10235 Live
10257 Suspended
10211 Live
10211 Live

You will notice that Vac No 10211 is showing more than once but i need these duplicate Numbers to count only once and only if they are showing live in column B. I have to say that f I have the numbers showing more than once in column A, They will always show the same status in Column B.

Hope this makes sense.

I also have another issue. Is it possible that when I change the status in Column B of a vacancy No that it will automatically change the status of all the other relevant vacancy numbers. eg. I change the Live to Suspended in one of the 10211 Vacancy Number and all other 10211 vacancies change from live to suspended.

Thanks in advance.

Red

 

[Aladin Akyurek]

Hi all,

Hope I can explain this properly.

I have one column that has vacancy numbers in. The second column tells me if the vacancy is Live, Suspended or Closed. I am looking to count the Live vacancies but don't want to count duplicate numbers eg.

A B
Vacancy No Vacancy
10211 Live
10235 Live
10257 Suspended
10211 Live
10211 Live

You will notice that Vac No 10211 is showing more than once but i need these duplicate Numbers to count only once and only if they are showing live in column B. I have to say that f I have the numbers showing more than once in column A, They will always show the same status in Column B.

Hope this makes sense.

I also have another issue. Is it possible that when I change the status in Column B of a vacancy No that it will automatically change the status of all the other relevant vacancy numbers. eg. I change the Live to Suspended in one of the 10211 Vacancy Number and all other 10211 vacancies change from live to suspended.

Thanks in advance.

Red

Query 1

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A2:A6<>"",IF(B2:B6="Live",
    MATCH("~"&A2:A6,A2:A6&"",0))),ROW(A2:A6)-ROW(A2)+1),1))

If no chars like < can occur around the vacancy entries, remove the "~"& and &"" bits from the foregoing formula.

Query 2 requires VBA I think.

 

[civilred]

Thanks will give it a try

 

[T. Valko]

Hi all,

Hope I can explain this properly.

I have one column that has vacancy numbers in. The second column tells me if the vacancy is Live, Suspended or Closed. I am looking to count the Live vacancies but don't want to count duplicate numbers eg.

A B
Vacancy No Vacancy
10211 Live
10235 Live
10257 Suspended
10211 Live
10211 Live

You will notice that Vac No 10211 is showing more than once but i need these duplicate Numbers to count only once and only if they are showing live in column B. I have to say that f I have the numbers showing more than once in column A, They will always show the same status in Column B.

Hope this makes sense.

I also have another issue. Is it possible that when I change the status in Column B of a vacancy No that it will automatically change the status of all the other relevant vacancy numbers. eg. I change the Live to Suspended in one of the 10211 Vacancy Number and all other 10211 vacancies change from live to suspended.

Thanks in advance.

Red

For the count of unique vacancy numbers with a status of "live"...

Array entered**:

=SUM(IF(FREQUENCY(IF(B2:B6="live",A2:A6),A2:A6),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Your other issue will require VBA programming. I'm not much of a programmer so someone else will need to tackle that one.

 

[civilred]

Hi Aladin,

That works great.

Another question, If I am doing this sum on another page such as a summary page where would I put in the code to tell it to look in sheet 1?

Thanks again
Red

 

[Aladin Akyurek]

Hi Aladin,

That works great.

Another question, If I am doing this sum on another page such as a summary page where would I put in the code to tell it to look in sheet 1?

I suppose you mean how to refer to Sheet1 from with a summary (different) sheet? If so:

=SUM(IF(FREQUENCY(IF(Sheet1!A2:A6<>"",IF(Sheet1!B2:B6="Live",
    MATCH("~"&Sheet1!A2:A6,Sheet1!A2:A6&"",0))),
     ROW(Sheet1!A2:A6)-ROW(Sheet1!A2)+1),1))

which still to be confirmed with control+shift+enter, not just enter.

Thanks again
Red

You are welcome.

 

[civilred]

Thanks Aladin,

Also if you wanted to add another Logical test asking the sum to calculate if column D had a yes in it could you add this to the sum?

Red

 

[Aladin Akyurek]

Thanks Aladin,

Also if you wanted to add another Logical test asking the sum to calculate if column D had a yes in it could you add this to the sum?

Red

Like this...

=SUM(IF(FREQUENCY(IF(Sheet1!A2:A6<>"",
    IF(Sheet1!B2:B6="Live",
    IF(Sheet1!D2:D6="Yes",    
      MATCH("~"&Sheet1!A2:A6,Sheet1!A2:A6&"",0)))),
        ROW(Sheet1!A2:A6)-ROW(Sheet1!A2)+1),1))

 

[civilred]

Great Thanks for your help.

Red

 

[Aladin Akyurek]

Great Thanks for your help.

Red

You are welcome. Thanks for providing feedback.