Getting distinct value with multiple criteria from a data set - Using VBA countifs

Dear Forum Experts,

Need your support on the following requirements. Looking for VBA code support. Model data excel sheet has been attached for your kind reference.

I have a scoring data sheet by state, month and employee wise. An employee can be given scored multiple times in a month also. I need an output with Distinct list of an employee only. I do not want to sum the score.

For example: "ABC" an employee given score two times in a January month. I need an output as

State Name Jan-20 Feb-20

TN ABC 1 0

Regards

Try

Please Login or Register  to view this content.

Dear Mr. Jindon,

Thank you so much your guidance. It works perfectly. There is only one guidance required further. I have already fixed a worksheet ("Output") for summary table. Instead of insert a new sheet with summary table. How could we get the summary table in the existing assigned sheet.

Your guidance will be a great learning for me.

Regards

Dear Mr. Jindon,

Thanks a lot for your guidance. Second code also working fine. But the code is not taking the number of unique employees. Sorry, if I wrongly communicated my requirement.

For example: There is two employees in TN state. Such as: "ABC" and "CBA", ABC was given score two times and CBA was given score one time in a January month. I need an output as. I need to take the unique value based on Column "C" only. Column C has unique Identification number for each employee.

State Jan-20

TN 2


Regards

Confising,

If you filter by "TN", there are 4 records,
All are the same up to col.E.
Jan x 2, Feb x 1 in co.F

How do you want it?

Dear Mr. Jindon,

Yes, you are right. In the existing sheet, it is 4 records. When I was testing with your code, I have newly added one employee for TN state and checked. But the code is not bringing the 2nd employee data. Attached excel sheet for your kind reference.

Extremely thanks for your patience and sorry to bother you lot.

Regards

Is the that the result that you want in "result" sheet?
If so, I can not find any difference from my code besides the cell format in heading.
If not, upload your EXACT result that you want.

Dear Mr. Jinton,

As suggested, i have attached excel with expected result in Output_in_Pivot worksheet.

Thanks

If you have PT and got what you want, why do you need vba?

Dear Mr. Jinton,

Actually This document is very large document as well as i am working for my team mates. Among them most of the people not familiar with excel and pivot. Hence, i am trying to use VBA to get the required result in a single click.

Your support, may fulfill the requirement.

Regards

Try

Please Login or Register  to view this content.

Dear Mr. Jinton,

Thank you so much for your kind guidance. It is really a great learning for me. Thanks again for your patience and support.

Regards

Dear Mr. Jinton,

The code is perfectly working on test module, which i have shared with you, where as when i apply into real dataset it shows the runtime error 9 - Subscript out of range. I searched in google and tried few attempts. I have checked the reference worksheet name ("Master_ANC") and is there any entire column is blank in between the range proposed. Attached screenshot for your kind reference.

Column - 266 - Month mentioned column
Column - 257 - State mentioned column
Column - 9 - Unique ID mentioned column.

Could you please teach me, where exactly the error is?

Attachment 713697

Regards

It says "Invalid Attachment".

Anyway, picture doesn't help at all.

Dear Mr. Jinton,

I have attached a file as binary format due to reduce the size of the file. the data set has column from A: JB. Due to confidentiality, i have removed the data from non referenced cells and column.

The code is perfectly working on test module, which I have shared with you, where as when I apply into real dataset it shows the runtime error 9 - Subscript out of range. I searched in google and tried few attempts. I have checked the reference worksheet name ("Master_ANC") and is there any entire column is blank in between the range proposed. Attached screenshot for your kind reference.

Column - 266 - Month mentioned column
Column - 257 - State mentioned column
Column - 9 - Unique ID mentioned column.

The requirement of the summary table is,

From column JG to KE, If any cells shows "1". I need to have a summary of those cells as said earlier. For example.

If Column JG has "1"... then

State - Jan - Feb - Mar

TamilNadu 28 - 24 - 26


Could you please teach me, where exactly the error is?

This far different from your original format.
Are you sure that you have such blanks in col.266?

Don't even try to think of "modify" the given code without understanding it.

Dear Mr. Jindon,

Now I have used a macro to extract only value available row and paste it in another sheet and apply your code and it works perfectly. I have thoroughly gone through your code and try to understand as much as possible with google help.

Could you please me to add one more criteria in the code?. I want to get the unique value based on Column J has "1".

Regards

Originally Posted by jindon

This far different from your original format.
Are you sure that you have such blanks in col.266?

Don't even try to think of "modify" the given code without understanding it.

Which code are you talking about?
If it is "Sub test_1()", it just crushes... and I don't think you understand it.

Is this the last change you make?

Dear Mr. Jindon,

I perfectly understand your code. There is one more module (Module-2) is there, which is your original module. "Sub test_1" module is testing module, in which i was trying add something and i failed. In order to understand your code, i have gone through VBA dictionary videos and understand the flow.

Just for the clear requirements, request you to find the attached sheet. It will make you very clear. I have done some manual output for clear understanding. Please do the needful.

Regards

If you understand it perfectly, you could do it for yourself, so should have problem.

What you understand is, I guess, only the column references... it doesn't mean "understand".

VBA is not like Formula in a cell...

You didn't answer my question.

Originally Posted by jindon

Is this the last change you make?

Dear Mr. Jindon,

Yes, I am gradually trying to understand the VBA scripts and coming to your question. yes, this is the last change i did it.

Regards

Not really sure about "sumif".

Please Login or Register  to view this content.

Dear Mr. Jindon,

Thanks for your patience and support. Both codes are working and still the newly proposed criteria (Column J - only "1" value cells to be selected for count value) is not met in the first table (Summary_Tab_1) , but it works on the second "sumif" table.

As per the current code, first table shows overall unique count value, but not filtered Column J - "1" value and "sumif" code works, where all the months have a data only. If there is no data in between months, again data start from the respective month data.

Regards


Regards