• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique data of a column

NARAYANK991 said:
Hi Zameer ,

Can you say what will be the data in the Gatepass cell where the Unitcode is either 01 or 02 ? Will it be blank ?

Narayan
Click to expand...

Hello Sir,

It may be Blank or very nice if it gives "Internal Issue" in the cell of gatepass number.

Warm Regads,


Zameer Shaikh
 
NARAYANK991 said:
Hi Zameer ,

Can you test this ?

Narayan
Click to expand...


Hello Sir,

You have fullfill my requirement. But through myside one issue is remain to tell that, you have see my file. in issue voucher dashboard, there is limit of one issue voucher no. is 15 items. When i want to issue more than 15 items to one unit code i have to change the Issue Voucher No. But Unit Code were same. And there may be 3 different Issue Voucher No. to Same Unit Code.

For example: on the day of 01.08.14 Issue Voucher No.301 [15 Times] & Unit Code 05 [15 Times], then Issue Voucher No.302 [15 Times] & Unit Code 05 [15 Times], then Issue Voucher No.303 [08 Times] & Unit Code 05 [08 Times]. i.e. Issue Voucher 301,302,303 are different but Unit Code is 05 same, Hence it should be generate same Gate Pass Number. Coz i want give single gatepass for above three Issue Voucher.

Then there is any little change in code to solve this issue.

Warm Regards,

Zameer Shaikh.
 
Hi Zameer ,

Does this mean that when the Unit Code changes , the Gate Pass number should change ?

What are all the possibilities ?

1. Suppose Issue Voucher and Unit Code are the same , the Gate Pass number should be the same.

2. Suppose Issue Voucher is the same , but Unit Code is different , what should happen ?

3. Suppose Issue Voucher changes , and Unit Code is the same , what should happen ?

4. Obviously , if both change , the Gate Pass number should change , correct ?

Narayan
 
NARAYANK991 said:
Hi Zameer ,

Does this mean that when the Unit Code changes , the Gate Pass number should change ?

What are all the possibilities ?

1. Suppose Issue Voucher and Unit Code are the same , the Gate Pass number should be the same.

2. Suppose Issue Voucher is the same , but Unit Code is different , what should happen ?

3. Suppose Issue Voucher changes , and Unit Code is the same , what should happen ?

4. Obviously , if both change , the Gate Pass number should change , correct ?

Narayan
Click to expand...

Hello Sir,
1. Suppose Issue Voucher and Unit Code are the same , the Gate Pass number should be the same.
Ans. YES
2. Suppose Issue Voucher is the same , but Unit Code is different , what should happen ?
Ans. Unit Code will same for Same Issue Voucher.
3. Suppose Issue Voucher changes , and Unit Code is the same , what should happen ?
Ans. Issue Voucher change 2 times, 3 times for same Unit Code, Gate Pass Number should be same, coz on the particular date it will be happen.

And Issue Voucher No. & Unit code change it will be generate next Gate Pass No.

Your code is perfectly work. But there is one issue that If Issue Voucher No. change& Unit Code is same it is generating next gatepass no. which is everytime i have to print new gatepass for same Unit Code. which i am issueing the material.

Warm Regards,


Zameer Shaikh
 
Hello Narayan Sir,

I have checked the file. When the once Unit Code has get the Gate Pass No. it is continuing again & again when its found the Same Unit Code. I have upload a file for example that IV No.101 & 102 are continue upto 21 times with same Unit Code 05 on the date 01.01.14. And which i mentioned the condition is fulfill. Also 01 &02 Unit Code has not generating Gate Pass No with IV No.103&104. Then I get Unit Code 15 with IV No.105 its give next Gate Pass No. upto here is all my conditions are fulfulling.

But when i get the Unit Code 05 with IV No.106 on the date 10.05.14 its give me same Gate Pass No. which fist time received for IV No.101&102. which is wrong. Here he should be generate the next Gate Pass No.

can compare with the date? suppose i create IV for Unit Code 05 as on today, the Gate Pass No. whatever it may be only for today, when i create IV for Unit Code 05 tommorrow it should be generate next gate pass no.

Warm Regards,


Zameer Shaikh
 

Attachments

  • SIMS_To_Be_Checked.xlsm
    794.8 KB · Views: 0
NARAYANK991 said:
Hi Zameer ,

See this file.

Narayan
Click to expand...
Hello Sir,

I have checked the file. It is justifying all conditions except one condition i.e. after 15 times IV No. change, but Unit Code is same Gate Pass should be same.

Remember Sir, One IV No. of 15 items for single Unit Code. After 15 items IV No. is must to change.

I have earlier post on friday:
For example: on the day of 01.08.14 Issue Voucher No.301 [15 Times] & Unit Code 05 [15 Times], then Issue Voucher No.302 [15 Times] & Unit Code 05 [15 Times], then Issue Voucher No.303 [08 Times] & Unit Code 05 [08 Times]. i.e. Issue Voucher 301,302,303 are different but Unit Code is 05 same, Hence it should be generate same Gate Pass Number. Coz i want give single gatepass for above three Issue Voucher.

You have justify the above condition. But now it is fail to justify. If this condition is justify my need is fulfill.



Warm Regards,


Zameer Shaikh
 
Hi Zameer ,

If you can upload a file with a lot of data , and you can specify manually what the Gate Pass numbers should be , it will make testing easier.

Narayan
 
NARAYANK991 said:
Hi Zameer ,

If you can upload a file with a lot of data , and you can specify manually what the Gate Pass numbers should be , it will make testing easier.

Narayan
Click to expand...
Hello Sir,

Here is file.

Warm Regards,

Zameer Shaikh
 

Attachments

  • SIMS_To_Be_Checked (1).xlsm
    798.2 KB · Views: 1
Hi Zameer ,

There was a mistake in the code ; check the file now.

Narayan
 

Attachments

  • SIMS_To_Be_Checked_1.xlsm
    797.5 KB · Views: 3
I don't understand that why nobody wants to use "=CountUnique" formula instead of SUMPRODUCT(1/COUNTIF....). And also with countunique you can give a high range if your data changes everyday. But sumproduct formula gives you an error when you try to give a range with blank cells.
 
memot said:
I don't understand that why nobody wants to use "=CountUnique" formula instead of SUMPRODUCT(1/COUNTIF....). And also with countunique you can give a high range if your data changes everyday. But sumproduct formula gives you an error when you try to give a range with blank cells.
Click to expand...


Hi memot,

In that case you can see in this post using sumproduct method

=SUMPRODUCT(IFERROR(1/COUNTIF(O5:O13,O5:O13),0)) with CST by Narayan Sir and many other answer
=SUMPRODUCT((O5:O13<>"")/COUNTIF(O5:O13,O5:O13&""))
it wiil not give error if any blank cell in range. and what about formula countunique,could you please share???
 
Ramesh Deo said:
Hi memot,

In that case you can see in this post using sumproduct method

=SUMPRODUCT(IFERROR(1/COUNTIF(O5:O13,O5:O13),0)) with CST by Narayan Sir and many other answer
=SUMPRODUCT((O5:O13<>"")/COUNTIF(O5:O13,O5:O13&""))
it wiil not give error if any blank cell in range. and what about formula countunique,could you please share???
Click to expand...

Wow, i missed that solution thank you very much.
And for the count unique formula, i am sorry, i've just realized that it's not excel default formula, my plugin; EZAnalyze addin got it into my excel.
 
@XLPadawan: Creative approach. Thinking out of the box. Thumbs up.

@Debraj: XLPadawans method works fine, the only problem would be that you have to widen the countif ranges and the sum range manually every time you have new value's.
 
You must log in or register to reply here.
Back
Top