Get a count of a combination of 2 different columns values, (not a sum, Just a count)
I need vba code or formula which can does the following, i am not a excel programmer, I am a C# developer, in one of the project I need to process some data from excel, which has 2 million rows! please some expert can help me, here is my problem, I have to insert count of each combination value from column 1 and column 2, what is the best way?
Column 1 Column 2 Count 20 1 20 1 20 1 3 20 2 20 2 20 2 3 20 3 1 22 1 22 1 22 1 3 22 2 22 2 2
Last edited by Jayakumar_vba; 12-28-2020 at 08:16 AM. Reason: uploaded sample file
The maximum rows an Excel workbook can have is 1,048,576.
You could use COUNTIFS for this.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
One way... whether the quickest or not is another matter!!
=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2),COUNTIFS(A:A,A2,B:B,B2),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I am not allowed to post any links 'You are not allowed to post any kinds of links, images or videos until you post a few times.'
Hi Glenn, Thanks for your solution, but it didn't work for me.
You were not asked for a link - read the yellow banner, as instructed.
"Didn't work" tells us nothing of any use - can you be more specific?
Hi AliGW, Thank you for you solution, just countifs is not enough, I have tried already, also another user Glenn Kennedy provided the solution, but even that didn't work.Originally Posted by AliGW
Please read post #6.
COUNTIFS will work if you use it properly as Glenn showed you, unless your real data is not what you've shown us.
Well, I need to get count of combination of two columns, but countifs is giving a value of one row. I can see my sample file is attached to a thread now.
Please read and act on post #6!!!!
Well, it worked for me. I cannot tell what you did wrong, though!!
All you had to do was change the start of the ranges like this:
=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1)=COUNTIFS(A:A,A1,B:B,B1),COUNTIFS(A:A,A1,B:B,B1),"")
This WORKS in your attached sample.
Hi Glen, It is worked ! my first row was blank, Sorry for my bad. Thank you very much for your help, very much appreciated.
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Yes, it worked, Sorry my bad, my first row was blank. Thank you for your help, very much appreciated.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Glen, how do i make answer as Solved?
Last edited by AliGW; 12-28-2020 at 08:48 AM. Reason: PLEASE don't quote unnecessarily!
Glenn and I have both already told you how:
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Glenn & AliGW, done, Thank you.
Glad we got there.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks