count different instances in two columns

[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.

 

[Bob Phillips]

=COUNTIF(A:A,"red")+COUNTIF(B:B,"red")

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Heather Heritage]

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!

 

[Ron Rosenfeld]

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.

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

 

[Ron Rosenfeld]

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

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

 

[Roger Govier]

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

 

[Ron Rosenfeld]

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

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

 

[Roger Govier]

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

 

[Ron Rosenfeld]

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

You're welcome. Thanks for letting me know.
--ron

 

[K7]

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.

 

[Ron Rosenfeld]

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.

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