count different instances in two columns

  • Thread starter Thread starter K7
  • Start date Start date
K

K7

Hello all,

I haven't been able to solve this...

I have two columns in the same sheet. I would like to extract (in another
sheet) all the unique elements of each column and count how many each data
is repeated. Any element can appear in both column. An example would be:

column 1 column 2

red red
red blue
blue green
yellow magenta


the result would be

red 3
blue 2
yellow 1
green 1
magenta 1


Sorry for my english and thanks to whoever can help me with this.
 
=COUNTIF(A:A,"red")+COUNTIF(B:B,"red")

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
If that method is ok, then COUNTIF(A:B,"red") will do it just as well -
however, I THINK the problem is in identifying all the unique values, then
summing them - perhaps the original questioner could clarify this? I had
thought pivot table to get the data, but it's in 2 columns which will cause
more problems - so my thoughts are now on a macro solution!
 
Hello all,

I haven't been able to solve this...

I have two columns in the same sheet. I would like to extract (in another
sheet) all the unique elements of each column and count how many each data
is repeated. Any element can appear in both column. An example would be:

column 1 column 2

red red
red blue
blue green
yellow magenta


the result would be

red 3
blue 2
yellow 1
green 1
magenta 1


Sorry for my english and thanks to whoever can help me with this.
Click to expand...

This should work: Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then assuming your data is on Sheet1!A2:B100

On sheet2

A2: =INDEX(UNIQUEVALUES(Sheet1!$A$2:$B$100,1),ROWS($1:1))
B2: =COUNTIF(Sheet1!$A$2:$B$100,A2)
Copy/drag down as far as required to encompass all of the unique values.




--ron
 
This should work: Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then assuming your data is on Sheet1!A2:B100

On sheet2

A2: =INDEX(UNIQUEVALUES(Sheet1!$A$2:$B$100,1),ROWS($1:1))
B2: =COUNTIF(Sheet1!$A$2:$B$100,A2)
Copy/drag down as far as required to encompass all of the unique values.




--ron
Click to expand...

I should also note that with this formula, you are limited to a maximum number
of 65535 elements in the array. If you have more than that, a different
approach can be devised.


--ron
 
Hi Ron

This is not working for me.
It gives me a result of
blue 1
blue 1
green 1
magenta 1
red 1
red 2
yellow 1

It looks as though the formula is treating each column as separate in
terms of determining the uniques.
What am I doing wrong?
Windows XP Prof, Excel 2003
 
Hi Ron

This is not working for me.
It gives me a result of
blue 1
blue 1
green 1
magenta 1
red 1
red 2
yellow 1

It looks as though the formula is treating each column as separate in
terms of determining the uniques.
What am I doing wrong?
Windows XP Prof, Excel 2003
Click to expand...


Do a copy/paste of the exact formulas you are using. It works fine here, so I
suspect there's either a typo in your formula, or something about your data.

Looking at your results, I'd consider that some of your data has a trailing
character -- either a <space> or a <no-break space>. That could be the case if
you downloaded the source data from a web table, for example.


--ron
 
Hi Ron
Of course. I copied the data from the OP and pasted with a Text to
columns split.
With correct data, the formula works exactly as described.
Thanks
 
Hi Ron
Of course. I copied the data from the OP and pasted with a Text to
columns split.
With correct data, the formula works exactly as described.
Thanks
Click to expand...


You're welcome. Thanks for letting me know.
--ron
 
Thanks all of you. I did not imagine that I could have an answer to this
problem so quick! I hope I could help you sometime...

This UNIQUEVALUES... any idea of how it works? It works fine, but I'm
writting a macro and I'd rather prefer to depend of my own code only.

Best regards and thanks all.
 
Thanks all of you. I did not imagine that I could have an answer to this
problem so quick! I hope I could help you sometime...

This UNIQUEVALUES... any idea of how it works? It works fine, but I'm
writting a macro and I'd rather prefer to depend of my own code only.

Best regards and thanks all.
Click to expand...

On Longre's web site is information and links about how he wrote the XLL; but I
don't know that he's posted information about the algorithms that he uses. You
might ask him within one of the message boards on his site.

Morefunc functions, though, can be easily distributed with the workbook (see
morefunc HELP for information) and/or used within a macro using the RUN
command.


--ron
 
Back
Top