+ Reply to Thread
Results 1 to 8 of 8

Count same characters in a string

  1. #1
    Forum Contributor
    Join Date
    09-21-2012
    Location
    greece,athens
    MS-Off Ver
    Excel 2010
    Posts
    102

    Count same characters in a string

    Good day to all of you.
    It is very difficult t explain with words So please take a look at the attachment.Thank you.

    SORRY WRONG SECCTION. I NEED A FORMULA TO DO THESE.
    Attached Files Attached Files
    Last edited by Glenn Kennedy; 11-25-2020 at 09:47 AM.
    Power! is knowledge...............

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,009

    Re: Count same characters in a string

    Part 1

    =SUMPRODUCT(--(D3:P3=E3:Q3)*(E3:Q3<>F3:R3))

    Part 2 to follow!
    Glenn



  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,009

    Re: Count same characters in a string

    I dont really understand what you want in Part 2. This delivers your expected answers, but I suspect it is more complicated!!

    First bit:

    =SUMPRODUCT(IFERROR((COUNTIF(D18:R18,D18:R18)>1)*(D18:R18<>"")/(COUNTIF(D18:R18,D18:R18)*(D18:R18<>"")),""))

    Second bit:

    =SUMPRODUCT((COUNTIF(D26:K26,D26:K26)>1)/COUNTIF(D26:K26,D26:K26))

    The first bit is an array formula.
    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    09-21-2012
    Location
    greece,athens
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Count same characters in a string

    Sorry about that.My ENGLISH is not quit good in order to give you the exact description.
    i'll try one more time for the second one.(The first its OK!)
    i HAVE data of different lenth of columns every time.I need to check the pairs of that data and count them.
    For example today i have data from A1:O1 (Tats 15 columns)
    Now i need to check those pairs(A1,O1).(B1,N1).(C1,M1).(D1,L1).(E1,K1).(F1,J1)and finally(G1,I1)THE (H1) stay away becouse it has no pair
    for that column.
    Next day i might have 12 columns lenth of my data so i need a TYPE to find those pairs matching and counting.
    THATS ALL my dear and thank you very much for your help
    Regards Fulldeen

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,009

    Re: Count same characters in a string

    Perectly clear now. All I have to do is try to figure out how to approach it.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,009

    Re: Count same characters in a string

    OK. Here it is. The orange coloured cell (X17) shows the starting point for the bigger, generic formula. I left it there, just in case I've made a mistake.

    This array formula does the job:

    =SUMPRODUCT(--(INDEX(D18:T18,N(IF(1,ABS(ROW(INDIRECT("1:"&ROUNDDOWN(COUNTA(D18:T18)/2,0)))-(1+COUNTA(D18:T18))))))=INDEX(D18:T18,N(IF(1,ROW(INDIRECT("1:"&ROUNDDOWN(COUNTA(D18:T18)/2,0))))))))

    It is generic, working for both odd and even numbers. If you need it to work on more than 17 columns, adjust the ranges appropriately. I've given it about 5 minutes of testing and it seems OK. Don't forget that array formulae need CTRl-SHIFT-Enter, as described above.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    09-21-2012
    Location
    greece,athens
    MS-Off Ver
    Excel 2010
    Posts
    102

    Re: Count same characters in a string

    That was GREAT!
    THANK YOU SO,SO,SO much dear Glenn.
    Best REGARDS....................!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    27,009

    Re: Count same characters in a string

    Thank goodness. You have no idea how much time I spent on this!!

    I'm pleased that ou're happy with it, so 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'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ 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. detect only the two pairs of twin pairs within the 7 positions.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2020, 07:54 AM
  2. [SOLVED] 137/5000 detect at the value = 1 for pairs, 2 odd pairs and 3 for the same digit
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-29-2020, 10:20 PM
  3. Replies: 7
    Last Post: 10-17-2019, 12:00 PM
  4. Replies: 1
    Last Post: 08-19-2019, 08:38 AM
  5. please, detect in the 100 pairs, the pairs of the 16 patterns,
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2019, 06:07 AM
  6. convert the sweepstakes to their palindrome pairs and identify the twin pairs.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2019, 07:59 AM
  7. [SOLVED] locate in their positions pairs and odd pairs
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2018, 06:35 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