Comparing 2 columns of Text

[Berk21]

Im trying to compare 2 colums of text and if one comlumn is typed more than the other column, it will sum in cell A1
for instance:
the word Text is typed in C1:C6, and C12:C14


they word Text2 is typed in C7:C11

Obviously Text is typed more than Text2, so cell A1 will count as a "1"

Thanks!

[Catalin.B]

You want to compare the lenght of text in two cells?
Try : =if(len(C2)>len(C1);len(C2)-len(C1);C2&" lenght is not bigger than "&C1&" lenght.")

[Berk21]

Originally Posted by Catalin.B

You want to compare the lenght of text in two cells?
Try : =if(len(C2)>len(C1);len(C2)-len(C1);C2&" lenght is not bigger than "&C1&" lenght.")

Not length, but more like 6 words is greater then 5 words.
as if your converting the words to numbers..and adding them and comparing which column came out ahead..if column with "x" > "y" X counts 1 and Y counts 0 in a differnt area (just for example - Cell A1 for x and A2 for Y)
Depending on the word...
Thats why i used "text" & "text2" as my test...if you were to type them 1000x side by side, and happen to miss one of either..this formula would see which one and how many times you miss typed in a cell that totals that ansewr.

Thanks

[Catalin.B]

Originally Posted by Berk21

Not length, but more like 6 words is greater then 5 words.
as if your converting the words to numbers..and adding them and comparing which column came out ahead..if column with "x" > "y" X counts 1 and Y counts 0 in a differnt area (just for example - Cell A1 for x and A2 for Y)
Depending on the word...

Thanks

I must say that the formula above is not converting words to numbers.. It counts the number of characters that exists in that cell. If the second cell has a bigger number of characters, the formula result is indeed numeric, but that number represents the number of extra characters in second cell, compared to the first cell. (including spaces)
Please clarify exactly what you want, maybe i misunderstood your problem:
you want the two columns to be a perfect match? (then use Match-formula, the result is numeric , or a simple comparation =IF(D11=E11;"ok";"no"))
You want to compare the number of whole words (not characters separately) between the 2 columns? This can only be done with a macro.

[Berk21]

Originally Posted by Catalin.B

Please clarify exactly what you want, maybe i misunderstood your problem:

I know that formula isnt converting to numbers...that was just an example.
apparently its not clear enough to have someone figure it out..Thanks for the response/attempt

[teshurtz]

if it is a fixed string you are looking to compare, you could make a column next to each (even hidden afterwords). in the first cell of column b (assuming the data is in column a) type =if(a1="text",1,0) and then drag that to the bottom of the list. copy and paste that same column of formulas next to the other list. then just sum the two columns to compare them.

Better yet to be more consistent with your example. you are trying to see if a single column has more of one word than another right?
so in column D cell 1 type =countif(c:c,"text") and in D2 =countif(c:c,"text2"). then compare cell d1 and d2

or you could combine them all in cell A1 with a slightly complex "if" statement =if(countif(c:c,"text")>countif(c:c,"text2"),"text ",if(countif(c:c,"text")<countif(c:c,"text2"),"tex t2","Equal")

This will make cell A1 display the word "text" if there are more cells in column C containing "text" and A1 will display the word "text2" if there are more cells in column C containing "text2". if there are the same number of text and text 2 it will say "Equal"

[Berk21]

Originally Posted by teshurtz

Better yet to be more consistent with your example. you are trying to see if a single column has more of one word than another right?
so in column D cell 1 type =countif(c:c,"text") and in D2 =countif(c:c,"text2"). then compare cell d1 and d2

or you could combine them all in cell A1 with a slightly complex "if" statement =if(countif(c:c,"text")>countif(c:c,"text2"),"text ",if(countif(c:c,"text")<countif(c:c,"text2"),"tex t2","Equal")

This will make cell A1 display the word "text" if there are more cells in column C containing "text" and A1 will display the word "text2" if there are more cells in column C containing "text2". if there are the same number of text and text 2 it will say "Equal"

your on the correct track...im testing your theory...to elaborate on the text, text1 concept...switch it to win, lost...
maybe now you know where im going with this...if wins over see lost then a dedicated cell says 1 for win and 0 for lost.

let me know what you think..
thanks!

[Berk21]

follow up on the formula...works great!...here's the counter question...

can we make win or lost = 1 if true or 0 if false? (example: wins>lost=wins=1,and if second match is played..if wins>lost 2x then wins = 2 and lost = 0..and viseversa)

if an example is needed..I will be glad to provide..so far in 3 weeks, your the closest to what im expecting...GREAT JOB!

[teshurtz]

I am not exactly sure what you are looking for but I think it is this, you want a counter that will count how many wins there are vs losts? or do you want a number that says how many times there have been more wins than losses (like you have 20 columns of data, each with a list of win or lost in each cell, so a column would represent a single match of 10 games for example and then there are 20 matches and you want to see how many matches each person won?)

if all you want is to see how many wins are in a column or how many lost
label a1 as "wins" then in a2 =countif(c:c,"win")
and cell b1 as "losses" and in b2 =countif(c:c,"lost")

the countif function counts the number of cells within the range (in this case c:c for all of column c) that equal a given value (in this case the word win or lost)

if you want more of the second scenario I listed you could expand on the idea. make a1 say wins and a2 =if(countif(c:c,"win")>countif(c:c,"lost"),1,0)
than make a3 say lost and a4 =if(countif(c:c,"lost")>countif(c:c,"win"),1,0)
if you put a similar set of counters next to each column of win/lost then you can sum all of row 2 and 4 to get the totals.

if neither of those are what you are looking for please be as descriptive as possible in what you want to do.

[Berk21]

2nd option was more of what i was looking for...comparing wins to loss and placing the one in the column that was greater...perfect..

Can another column be added?
This would be the part where i would be comparing another game.

To clarify why im using this to count wins vs. loss...the games are best of 5..
(out of 5 games, who took more wins or losses= team win or a team loss) your formula works great so far..
Im going to expand the criteria.

taking what you gave me and adding another column to add(+)to the results.

Example:"my"team beat teamX 4:1...the next time we played, TeamX happened to win 3:2. "my team" total wins= 1 loss =1 against that team.

once i can do that, i can add in the other teams in, and be able to show My teams wins,losses to all the teams we played against for a season...

You have been great..thanks so much for helping me out.