Count specific characters present in a column

Hi
I've tried doing this using formulas, but I have about 12000 entries and they aren't working as I want them to anyway. I have land transactions between multiple parties, there are transactions with a single seller and buyer, and with multiple joint owner sellers and joint owner buyers with many permutation (please see attached sheet). I want to find how many are female /male /male&female(in case of joint titles).

The names are not in english but I have some identifying characters which can be used to determine female persons:
मती,
दवी,
व ता

There can be 1,2 or all three characters in the same name.


The formulas I've tried and failed to use are:
So I've tried:
=IF(OR(ISNUMBER(SEARCH("ीमती",C3)),ISNUMBER(SEARCH("दवी",C3)),ISNUMBER(SEARCH("वती",C3))),"1","")

But this will only determine the gender for one cell and wont 'count' if there is more than one seller/buyer

=COUNTIF(C5:C8,"*" & "ीमती" & "*") + COUNTIF(C5:C8,"*" & "दवी" & "*")
Again, this counts the number of instances these characters appear, however if they appear twice in the same name then it'll count it twice.

The main problem I have is that I want the code to also look at the transaction ID to start a new count when the ID changes, so I have a count for each transaction, this is proving to be difficult because as I mentioned earlier, its not just two rows with one being a buyer and one being a seller, sometimes with joint titles there are like 5 buyers and 2 sellers and many other permutations.

Please post links to your threads in other forums.

Crossposting without links is against most forum rules.

I'm sorry I'm new here. What do you mean exactly? What is crossposting?

Have a read here: http://www.excelguru.ca/content.php?184

I have a suggestion involving Pivot tables but I'm not sure I'm allowed to post it until you have complied with a moderator request (msg#2)

Hi, I've been trying to post a link but it says I need to have 5 posts before I can include a link in my posts. Any idea how I can do this?

paladin7 said:

Hi, I've been trying to post a link but it says I need to have 5 posts before I can include a link in my posts. Any idea how I can do this?

Click to expand...

Well you could either try to disguise the link, eg:
www dot excelguru.ca/forums/showthread.php?3010-Count-specific-characters-present-in-a-column&p=12916#post12916
and explain what to replace, or post a couple more short messages in this thread to get you post count up (I hope this isn't against the rules!).

Ok here is where I found another post:

http://www.ozgrid.com/forum/showthread.php?t=188278

p45cal,

You can proceed to post your solution. Thank you.

Other than just posting a couple more times

Which I have done so now

Here we go http://www.excelforum.com/excel-fo...his is satisfactory. Sorry for the ignorance.

I added another column to your data in column P:
=IF(COUNT(SEARCH({"मती","दवी","व ता"},D5))>0,"Female","Male")
You need to check thse characters are correct.
Then I created a couple of pivot tables. This is when I noted that your ID column (B) contained similar looking values, some as text, some as numbers which were treated differently in the pivot tables, so I normalised that column to all text.
These Pivot tables are in the vicinity of cell X70 in the attached. In the leftmost one I added a calculated item to the Gender field, called Mixed, which returns 1 if there are mixed gender among buyers and 0 if not. I filtered the pivot table by Mixed.
There appears to be only one transaction where there are mixed gender amongst the buyers or sellers.
The rightmost pivot is more straightforward and shows the number of males and females for buyers and sellers by transaction ID. A bit more difficult to spot where the mixed gender are, but this shows numbers of each gender amongst buyers and sellers by transaction.

I could be better versed in calculated items/fields, so I'm sure it could be improved upon.

Hey p45cal,
Thanks! That is incredibly helpful! Is there any way to copy these tables in other spreadsheets? I tried, but it brings along the original sheet values and I can't find a way to change that, or do I need to recreate these table in every spreadsheet from the start? I have about 500 of these...

Do you need to summarise all 500 sheets it one table or must you have one summary per sheet?

I can do it in one table I think...

paladin7 said:

do I need to recreate these table in every spreadsheet from the start?

Click to expand...

Perhaps.

paladin7 said:

I have about 500 of these...

Click to expand...

500!
Well, this raises more questions… are we talking 500 workbooks or 500 worksheets, perhaps a mixture?
Is there a way to reliably determine, say from the name of the sheet, that the sheet contains data you want analysed (this may not matter if workbooks contains ONLY data sheets)?

To automate the determination of the extent of the table on each sheet, can we reliably assume that that table can be selected by selecting cell A4 on the sheet, pressing F5, choosing Special…, Current region, then OK? (It would be good if you could say Yes to this.) (Current region is defined in Help by "The current region is a range bounded by any combination of blank rows and blank columns and the edge of the sheet." (my addition in red).
If this is not the case then to determine the extent of the table we may need to:
a) look for column headers on the sheet if these are always the same
b) know which column will always have data in it in every row right to the very bottom of the table.

I'd be looking at running through each sheet/workbook/table and either creating a single large data table from which to create a pivot table or perhaps ditching the pivot table idea altogether and doing all the summarising in code.