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)

"K7" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>

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!
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:O$(E-Mail Removed)...
> =COUNTIF(A:A,"red")+COUNTIF(B:B,"red")
>
> etc.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "K7" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> >
> >
>
>

On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <(E-Mail Removed)> wrote:

>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

On Mon, 05 Jun 2006 07:30:12 -0400, Ron Rosenfeld <(E-Mail Removed)>
wrote:

>On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <(E-Mail Removed)> wrote:
>
>>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

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

--
Regards

Roger Govier


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <(E-Mail Removed)> wrote:
>
>>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

On Mon, 5 Jun 2006 13:28:10 +0100, "Roger Govier"
<(E-Mail Removed)> wrote:

>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

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

--
Regards

Roger Govier


"Ron Rosenfeld" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Mon, 5 Jun 2006 13:28:10 +0100, "Roger Govier"
> <(E-Mail Removed)> wrote:
>
>>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

On Mon, 5 Jun 2006 16:05:49 +0100, "Roger Govier"
<(E-Mail Removed)> wrote:

>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

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.



"K7" <(E-Mail Removed)> escribió en el mensaje
news:(E-Mail Removed)...
> 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.
>