COUNT the text string ITEMS in a COLUMN without repetition

[rengeroni]

How do I COUNT the text string ITEMS like "elem school" in a COLUMN without repetition . . . thank you . . .
first desired answer is Line 6 is the total number of items in a column range without repetition . . .
Line 7 is the total number of items with the breakdown of the kinds of the item such as Total = how many elem school + how many high school + how many college school + how many university school + how many dance school + how many other school . . . in that order respectively the ANSWER is Col A = 1 elem + 1 high + 1 college + 1 university and 1 dance and zero 0 other school and listed in the sample answer as i.e. Total 13 = 5 elem + 4 high + 3 college + 2 university + 1 dance +0 others

I am using MS Office Excel 2010 running on pc with Intel i7[/FONT]

please see the attached excel file 2022rengeroniCount01a.xlsx and the image file CCI_000119.jpg for reference for a better appreciation of the workbook . . .

A B C D
1 matthew elem school matthew elem school matthew school
2 mark high school mark high school mark high school mark high school
3 luke college school luke college school luke college school luke college school
4 mark university school mark high school mark high school mark high school
5 peter dance school peter dance school peter dance school peter dance school
6 5 4 3 3
7 5=1+1+1+1+1+0 4=1+1+1+0+1+0 3=0+1+1+0+1+0 3=0+1+1+0

CCI_000119.jpg

[dosydos]

Couple of questions for you:
1) What version of MS Excel do you have?
2) Can you please provide your data in a sample workbook? Hard to tell where one column ends and another begins with the data you provided.

thanks

[AliGW]

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[ELeGault]

If you have a newer Excel Version you will be able to do something like this... if not - we will have to get creative in other ways... you can paste this formula above or below your data in B, then drag the formula through the other columns

Please Login or Register  to view this content.

Change that number in red if you want to change the column within the string as it is now set to hit 1 space. 0 would get you to your first word and so on... you can set this to be dynamic on a cell but update this to be 1 - CELL and then make that cell designated to be the WORD, or leave it as is just replace the 1 with the cell reference and put after X word breaks...

hope it works but if excel version is older - will need to do something else (Also I am sure there is a more elegant way into this)

[nimrod1313]

Please Login or Register  to view this content.

The above formula is looking at column B:B
Countif > 0 will either return a true of false , "INT" will turn the True to a 1 and a false to 0 ... these are then added up .
If Countif > 0 finds a repeat it still only returns a True which is converted to the value 1

[ELeGault]

I like the approach - but what if you dont know the second word that is going to be presented.

[nimrod1313]

I'm not sure what you mean , please provide and example.
By the way I downloaded your spreadsheet and the formula worked on all your scenarios/examples (i.e. returned the results you had requested in each column)

[ELeGault]

Not my spreadsheet - I am not the OP - Just asking the question that should be in thought for design. I have to believe this is a subset of data not the entirety of the OP's issue. Thus needing a solution that expands past what is visible...

Lets say there was a new entry, Mark Cowbell Skittles... Cowbell will not be counted correct?

[ELeGault]

Could be that I am looking at this wrong. The OP's request is a bit miss aligned with what is observed in the data.

It seems that the OP does not want to count anything that appears more than once in set.
It also appears that the items to review is always second word in each string.
These are always the same variables in succession consistent with the OP's listing of Total = elem + high + college + university + others . . .
its the Others... that had me thinking this goes beyond the visible variables.. but perhaps that is me looking too deep.

But if the OP's post is stating that the limitation is constrained to these 5 variables... than your solution will work just fine!
The way I forced the array to count each item found in the second word does a similar function and will get the same result... but it is open to OTHER words being presented in the second value... so has its own set of issues. Also is not backwards compatible ...

[nimrod1313]

WOW LOL I don't think that's what the OP is looking for ... but if it is I will leave it up to you
cheers

[rengeroni]

COUNT the text string ITEMS in a COLUMN without repetition / 2022rengeroniCount01.xlsx
How do I COUNT the text string ITEMS like "elem school" in a COLUMN without repeatition . . . thank you . . . first desired answer is Line 6 is the total number of items without repeatition . . . Line 7 is the total number of items with the breakdown of the kinds of the item such as Total = elem + high + college + university + others . . . in that order respectively 5 elem + 4 high + 3 college + 2 university and 1 others and listed in the answer as i.e. 13 = 5 + 4 + 3 + 2 + 1 +0

I am using MS Office Excel 2010 running on pc with Intel i7
please see the corresponding workbook 2022rengeroniCount01.xlsx for reference . . . thanks for your time . . .

[nimrod1313]

rengeroni :

Both ELeGault and I have posted solutions for you , do either of these solutions meet your requirements ?

[AliGW]

@ All Helpers

Administrative Note:

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.