I am using Excel 2010 version. My workbook column B has different text values, some of which occur multiple times. I want to extract all distinct values in column G and number of occurrences of each of these values in column H. I need help for this task. I am also enclosing sample file for clarity.
If I wish to determine distinct values based on two text columns, column B and column C, is it possible? What will be the modification in code for this job.
Last edited by Prabhakar1; 02-03-2021 at 06:24 AM.
In g2:
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$26)/(COUNTIF(G$1:G1,$B$2:$B$26)=0),1)),"")
and in H2:
=IF(G2="","",COUNTIF(B:B,G2))
Both copied down.
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
Thanks Glenn. The formulas worked fine on the sample worksheet. I have not tried yet on the actual workbook where there are about 2500 rows. I will define the range (B column) appropriately and try. I will let you know the results. Thank you so much !!
Welcome to the forum.
Try:
=IFERROR(INDEX($B$2:$B$26,MATCH(0,INDEX(COUNTIF($G$1:G1,$B$2:$B$26),0,0),0)),"")
and:
=IF(G2="","",COUNTIF($B$2:$B$26,G2))
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. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks Ali. Both the formulas worked fine on the actual worksheet with about 2500 rows.
The first formula may take a minute or two to work its way through 2500 rows, but it's far from excessive for such a formula.
You're welcome.
It would 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.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
Hi Glenn, I tried the formulas on the actual worksheet and they worked fine. Thank you so much for your valued support.
Great!
You're welcome.
It would 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.
Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks