Countif with ADO or ScriptingDictionary

**turist** · 12-20-2018, 04:45 PM

I have 2 sheets Sheet1 and Sheet2.
In Sheet1 There is a table which Column "C" has Item Numbers.(Approx. 60.000 Rows)
In Sheet2 There is a table which Column "C" has Item Numbers.(Approx. 50.000 Rows)

Starting from Sheet1 C2 cell to end of C Column (as C59850) ;
I'd like to count Item Numbers in Sheet2, to find how many Item Numbers exist in Sheet2 and put counting values to Column "V" for each.

In order to find the result for each Item Number in Sheet1, I used below code.

Please Login or Register  to view this content.

But Excel gives "Not Responding" message and crash.

I want to use ADODB or ScriptingDictionary method instead of my code.

If there is a solution with ADO or ScriptingDictionary , I thank in advance for any reply and help.
Best regards.

**jindon** · 12-20-2018, 10:07 PM

Try

Please Login or Register  to view this content.

**turist** · 12-20-2018, 11:05 PM

Dear jindon,
Thanks a lot for your reply.
Some Items counted more than 1 in Sheet1, but Code only shows "1" value as counted.
How can I modify the code in order to get , if counts more than "1" ?

**jindon** · 12-20-2018, 11:29 PM

Tested befote I posted.
So, need to see your workbook.

I'll be busy for the day, so I can not reply soon.

**turist** · 12-21-2018, 12:15 AM

Originally Posted by jindon

Tested befote I posted.
So, need to see your workbook.

I'll be busy for the day, so I can not reply soon.

Dear jindon
I attached My Workbook.
Waiting for reply.
Thanks and regards

**jindon** · 12-21-2018, 03:51 AM

OK, I'm back.

Your question is now something more than you stated originally, so I need to see your EXACT results.

Post a workbook showing before/after with a much less rows of data that you can describe the logic enough, so that I can wrote a code.

**jindon** · 12-21-2018, 04:16 AM

This is what I read from your workbook.

Please Login or Register  to view this content.

**turist** · 12-21-2018, 10:06 AM

Dear jindon again I attached workbook and explained the results and differences on Sheet1.
Could you please look and help to solve it.

Thanks in advance for your kind support again.

Best regards.

**jindon** · 12-21-2018, 10:25 AM

Loop through Col.C in Sheet1 and if the same value exists in Sheet2, count same value in SHEET1!C:C then result in col.V and pull data from col.L in the same row in Sheet2 to Sheet1 col.U

This is how I read your problem.
If I'm correct, your results are wrong.

Regarding 4003537517, you have 2.
one in C37779 and other in C42716, 1st one have blank in col.L, but 2nd one have 1 in col.L.
My code is taking the last one if there are multiple value in Col.C in Sheet2.

**turist** · 12-21-2018, 02:13 PM

Dear jindon.
I am sorry for taking your time.

My aim:
1-Loop through Col.C in Sheet1 COUNT in Sheet2 Col.C and pull counting numbers to Col.V in Sheet1
Regarding 4003537517 there are 2
Col V. Sheet1 should show "2"

2-Loop through Col.C in Sheet1 COUNT in Sheet2 Col.C and pull Col.L value to Col.U in Sheet1 (if Count number is >1 then pull LAST value in Col.L)
İn this case Col. L value can be "blank" and Col.U in Sheet1 will be "blank". Shortly , whatever is exist in Col.L in Sheet2 will be in Col.U Sheet1
Regarding 4003537517, you have 2.
one in C37779 and other in C42716, 1st one have blank in col.L, but 2nd one have 1 in col.L.
My code is taking the last one if there are multiple value in Col.C in Sheet2.
This is CORRECT.

I would be grateful if you can modify the code according to above explanation.

Thanks a lot for your support, help and sensibility.
Best regards.
Best regards

**jindon** · 12-21-2018, 09:30 PM

So, my logic in counting was correct, therefore your result was something wrong, because it can't be 2 when use =COUNTIF(C:C,C43475).

Pulls the data in col.L from sheet2 when multiple ITEM NO found in sheet2.
Change one line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**turist** · 12-22-2018, 12:18 AM

Dear jindon,
Please use this formula in Sheet1 on Cell V43475

=COUNTIF(Sheet2!C:C;Sheet1!C43475)

What do you see ?

This formula's result is =2 Completely CORRECT.

I'm looking for this logic in the code.

Thanks for your kind help.

**jindon** · 12-22-2018, 12:51 AM

Count Col.C in Sheet2, not Sheet1...

Please Login or Register  to view this content.

**turist** · 12-22-2018, 08:14 AM

Dear Jindon,
Finally I reached the correct result , thanks.
I tried to combine two macros as below.

Please Login or Register  to view this content.

Now, I need to shorten this modification.

Could you make a favour for me to redesign it.

Thanks for your patient support and help.
Best regards.

**jindon** · 12-22-2018, 08:23 AM

Doesn't make sense to me,

First part picks data from first appeared row from Col.L in Sheet2 and 2nd from the last appeared and overwrites Col(21).

What are you trying to do?

**turist** · 12-22-2018, 08:39 AM

Thanks Jindon.
May be ı can not tell what I need.
You don't have to reply any more.
Thank you very much indeed.

**jindon** · 12-22-2018, 08:43 AM

As I said already, upload a workbook with much less data set showing before/after, so that better understanding your problem, otherwise no way.

Anyway,
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

**turist** · 12-22-2018, 08:02 PM

Dear jindon
Please look at my attached workbook.
Thanks again.
Best regards

**jindon** · 12-22-2018, 09:55 PM

Change to

Please Login or Register  to view this content.

**turist** · 12-22-2018, 10:08 PM

That's it,happy end.
Thank you very much.
Best regards.

Countif with ADO or ScriptingDictionary

LinkBack

Thread Tools

Rate This Thread

Display

Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Re: Countif with ADO or ScriptingDictionary

Thread Information

Users Browsing this Thread

Similar Threads

Adding a COUNTIF function to an existing IF AND COUNTIF Formula...

How can I make =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) workwhen copying it further?

Faster if/countif formula for flagging uniques - countif too slow

Combining a Date Range COUNTIF and a general COUNTIF

Adding a CountIF to a formula that is already Countif

[SOLVED] COUNTIF or not to COUNTIF on a range in another sheet

Countif w/ Multiple Criteria-How do I use countif

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions