+ Reply to Thread
Results 1 to 9 of 9

Add the number of times text appears in column

  1. #1
    Registered User
    Join Date
    05-08-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Add the number of times text appears in column

    Hi,
    I have a few spreadsheets with a few land transactions. I want to see if the parties involved are male or female, or both (in case of joint titles). And how many. I've tried to use ISNUMBER formulas and COUNTIF formulas but I can't seem to make them work. I've attached an example of what I need to do, the original has many more column with more info, and the names are in a different language which makes it easier to identify as female or not (like 'phany' in english female names etc).
    Please let me know if anyone knows what I can do.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,106

    Re: Add the number of times text appears in column

    What have you tried? There are no clear instructions in your file. Are you wanting Excel to somehow work out whether the person's first name is male or female? If so, then you need to be clear about the language in which you are really working and the alternatives: is it obvious from the ending of the name, for example, and if so, what are all the permutations? You will probably need a LOOKUP table for this.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    05-08-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Add the number of times text appears in column

    Ok I thought it would be too confusing with the real names, but I've reattached the file with the real names showing. There are three identifying characters (ीमती, दवी, व ता) for female. There can be 1,2 or all three characters in the same name.

    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 folmula 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 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.
    Attached Files Attached Files

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,476

    Re: Add the number of times text appears in column

    Hi,

    Could you re-attach your workbook with your manually-calculated desired results included?

    And preferably add a few more lines than 6 so that it is clear what is required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    05-08-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Add the number of times text appears in column

    Hi,
    This is the edited version, this is what the whole thing looks like, The only things I've added are the six columns to the extreme right. I've highlighted the three columns that are relevant.
    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,476

    Re: Add the number of times text appears in column

    Thanks. And just to clarify - are those your expected outcomes in columns P-U?

    Just taking one example, I don't understand why you have no expected return for women for row 6, since both ीमती and दवी are present in the string in D6 (ीमतीरजनी दवी).

    Also, how do you determine the Men/women results?

    Regards

    Regards

  7. #7
    Registered User
    Join Date
    05-08-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Add the number of times text appears in column

    Hi,
    Yes columns P-U are the expected outcomes.

    So rows 5 and 6 are the same transaction with ID 150. If we look at column F we can see that row 5 is the seller and row 6 is the buyer.

    व ता = Seller
    ता = Buyer

    So the expected results in Q5 and U6 are for the ID 150 which has rows 5 and 6.

    Similarly, the last transaction ID 3471 consists of rows 53-62. Rows 53-61 are the sellers (they jointly own the land they are selling) and row 62 is the sole female buyer. There are 8 sellers, with the one in row 61 being female, which gives me the expected results of 7/1 in P53 and 1 in U53.

    Thanks,

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,476

    Re: Add the number of times text appears in column

    Thanks. But you still haven't answered my other point:

    Quote Originally Posted by XOR LX View Post
    Just taking one example, I don't understand why you have no expected return for women for row 6, since both ीमती and दवी are present in the string in D6 (ीमतीरजनी दवी).
    Regards

  9. #9
    Registered User
    Join Date
    05-08-2014
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Add the number of times text appears in column

    Hey,
    I have accounted for everyone in the first row of every transaction. So for ID 150, consisting of rows 5 and 6, all people are accounted for in row 5; the woman in row 6 is accounted for in cell U5 as she is a buyer (as mentioned in cell F6), and is therefore in the buyer section. Same for all other transactions. For example, everyone in the last transaction, ID 3471 (rows 53 to 62) has been accounted for in the first row of that transaction (row 53). Is that slightly clearer?
    Thanks,
    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 10-22-2012, 10:26 PM
  2. [SOLVED] find number of times a letter or a number appears in a column
    By dcoates in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-17-2012, 02:47 PM
  3. Replies: 2
    Last Post: 03-14-2012, 08:12 AM
  4. Count the number of times a specific text appears in column D
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2011, 02:02 PM
  5. Replies: 1
    Last Post: 03-11-2009, 03:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1