count different instances in two columns

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. 


0
jkanoj (5)
6/5/2006 6:12:48 AM
excel 39879 articles. 2 followers. Follow

10 Replies
397 Views

Similar Articles

[PageSpeed] 12

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

etc.

-- 
 HTH

Bob Phillips

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

"K7" <jkanoj@hotmail.com> wrote in message
news:uDDTTcGiGHA.1612@TK2MSFTNGP04.phx.gbl...
> 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.
>
>


0
bob.NGs1 (1661)
6/5/2006 7:57:33 AM
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" <bob.NGs@somewhere.com> wrote in message
news:O$KOIXHiGHA.4896@TK2MSFTNGP05.phx.gbl...
> =COUNTIF(A:A,"red")+COUNTIF(B:B,"red")
>
> etc.
>
> -- 
>  HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "K7" <jkanoj@hotmail.com> wrote in message
> news:uDDTTcGiGHA.1612@TK2MSFTNGP04.phx.gbl...
> > 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.
> >
> >
>
>


0
6/5/2006 9:42:34 AM
On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <jkanoj@hotmail.com> 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
0
ronrosenfeld (3122)
6/5/2006 11:30:12 AM
On Mon, 05 Jun 2006 07:30:12 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <jkanoj@hotmail.com> 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
0
ronrosenfeld (3122)
6/5/2006 11:55:23 AM
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" <ronrosenfeld@nospam.org> wrote in message 
news:585882p8706i59jvovl5fl1jrkuev66e4m@4ax.com...
> On Mon, 5 Jun 2006 08:12:48 +0200, "K7" <jkanoj@hotmail.com> 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 


0
roger5293 (1125)
6/5/2006 12:28:10 PM
On Mon, 5 Jun 2006 13:28:10 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> 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
0
ronrosenfeld (3122)
6/5/2006 1:26:26 PM
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" <ronrosenfeld@nospam.org> wrote in message 
news:rtb8821qrroj1mh8i386focd01f14gsqsu@4ax.com...
> On Mon, 5 Jun 2006 13:28:10 +0100, "Roger Govier"
> <roger@technologyNOSPAM4u.co.uk> 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 


0
roger5293 (1125)
6/5/2006 3:05:49 PM
On Mon, 5 Jun 2006 16:05:49 +0100, "Roger Govier"
<roger@technologyNOSPAM4u.co.uk> 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
0
ronrosenfeld (3122)
6/5/2006 6:27:07 PM
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" <jkanoj@hotmail.com> escribi� en el mensaje 
news:uDDTTcGiGHA.1612@TK2MSFTNGP04.phx.gbl...
> 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.
> 


0
jkanoj (5)
6/5/2006 9:17:27 PM
On Mon, 5 Jun 2006 23:17:27 +0200, "K7" <jkanoj@hotmail.com> wrote:

>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
0
ronrosenfeld (3122)
6/6/2006 1:11:27 AM
Reply:

Similar Artilces:

Insert Column 2007
aHello I have recently installed Access 2007. When I'm in the Design View of any query the icon to Insert Column is greyed out and unavailable. I have closed the query, the datebase and restarted the PC, Access and checked different queries, different databases and the problem persists. Any ideas? Judith Try to mark a column & Hit Insert Pieter "JudithJubilee" <JudithJubilee@discussions.microsoft.com> wrote in message news:7A44F079-25D2-4B1A-9AD2-89F76BEDCD30@microsoft.com... > aHello > > I have recently installed Access 2007. When I'm in the Desi...

My outlook opens two times
I just reloaded my Office 2003 application and now my Outlook client opens twice. ...

Why receive two copies of every email?
Hi there, I have an Exchange 2003 server. Users are using Outlook 2003. There is one issue with one user's account. When he sends out emails from his Outlook 2003, some recipients sometimes receive two copies of every email. Could you see what is the problem on this? Thanks in advance, Ross Sorry to jump in here, but I have the same type problem. When I receive email from my co-workers, I get duplicates of those emails. It isn't all of them, but the majority. I am running Office 2003 with all updates. I also made sure that the box in the tools section to download the header and bo...

How do I compare two Power Point Presentations, side by side?
I need to copy and paste some text from the slides and notes from one PPT to another. Can I open the two simultaneously and toggle from one to another without a whole lot of clicks? Which version of PowerPoint are you using? Open both presentations, put them both into slide sorter view and arrange them side by side and move stuff back and forth as needed -- Michael Koerner MS MVP - PowerPoint "olga" <olga@discussions.microsoft.com> wrote in message news:FB9CBB1F-A371-4B18-A9CC-7C6702593304@microsoft.com... I need to copy and paste some text from the s...

using 2 different IF statements
I have a macro that, after the user fills in 2 different areas of a form, the data in those areas are appended to an Excel list. If either area is not completely filled out, I want a message to appear indicating where the problem is, and the sub exited. I used the following code: If Range("c3") = "" Or Range("c4") = "" Or Range("C5") = "" Or Range("C6") = "" Or _ Range("C7") = "" Or Range("C8") = "" Then answer = MsgBox("Incomplete Header Information", vbInf...

rearrange the data in columns by dates?
How do I rearrange data by dates in reverse order? Example: I'd like to change the order to 3/21/2010, 3/22/2010, 3/23/2010...etc. Current order Column A B C D 3/24/2010 3/23/2010 3/22/2010 3/21/2010 5:00 5:00 5:00 5:00 LSR LSR LSR LSR 82.6 82.8 83 82.3 93.6 80.3 94 88 106.7 100.9 105.2 102.8 108.5 103.6 106.7 105.4 111 107.5 109 109.1 113.6 111.3 111.5 112.8 116.8 115.5 114.4 117.1 121.1 120.7 118.5 122.1 126.9 127.2 123.8 129 135.3 136.4 132 138.8 148.3 150.2 144.8 153.2 165.7 168.3 162.5 171.8 183.3 184.9 177.9 188.5 192....

Viewing Specific Columns at Various Times
I have a spreadsheet that has accounts in Column A and data in some of the rest of the columns. For example Columns B through M are month columns and Column N is Year Total. Then Columns U through AF are Year- to-Date by month and Column AG is Total Year Y-T-D. When reviewing January, I want to see only Columns B, N, O & U and when reviewing February I want to see only Columns C, N, O & V. Is there a way to group these Columns with a simple process? Thank you. John John Check out View>Custom Views. Name one January and the other February. Gord Dibben MS Excel MVP On 27...

using data from two tables
hello kind folks who have vast knowledge... I have a table of students who are employed by various companies. The table has a field where the employer's name is entered. AND I have a separate table of employer company details. I would like to be able to create a query that gives me the student names, their employer's name, and their employer's details. The employer's names are entered identically in the fields of both tables (ie spelling etc)... It seems like a simple problem but I just don't seem to be able to figure out how to do it.. any advice wo...

column mapping from on sheet to the next
What is the fastest/easiest way to take data from one Excel sheet in columns A, B, C, ...etc. and load it in another sheet in order I, J, A, C, K, G etc (with some columns being left unused as they don't exist in the source sheet). I have ten of those workbooks in order of A, B, C ...etc. and need to bring them (after some column manipulation) into a single new workbook. I am now thinking to copy and paste all ten of those workbooks into one, do the data manipulation (i.e. separating last name and first name etc) and then just cut and paste columns in the needed order. But I was hoping ...

Difference between Activesheet.unprotect
Using XL 2003 Can someone explain the differences between: (A) ActiveSheet.Protect UserInterfaceOnly:=True vs. (B) ActiveSheet.Unprotect In most cases where I have protected sheets, I can use (A) with no problems. When attempting to use a macro to first delete then re-establish data validation with different parameters (in a selected Range), I ran into a type of " lost contact with X or Y error". If I manually unprotected the sheet then ran the macro all was OK. What I found was Test Sub1() ActiveSheet.Unprotect .. .. "Data validation via ...

Function or formula to add different formatted cells
Is there a way in which I can use a function or formula to add, multipl and/or divide cells which are formatted differently and get an en result? I am specifically needing to divide a general number by tim frames (hh:mm:ss) and multiplying by another general number, yet I hav no idea if that can be done. Help -- rcdunn200 ----------------------------------------------------------------------- rcdunn2003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=138 View this thread: http://www.excelforum.com/showthread.php?threadid=26894 Hi as a starting point: http://ww...

How to create combo box on whole column fastly?
Hi: How can I create combo box on one whole column fastly? Please let me know, thanks. Thanks. -- accessman2 ------------------------------------------------------------------------ accessman2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27953 View this thread: http://www.excelforum.com/showthread.php?threadid=474563 Here is a fastly code: Dim ListItems As Variant Dim i As Integer ListItems = Range("A1:A1000").Value ListItems = Application.WorksheetFunction.Transpose(ListItems) With CB1 'CB1 Stands for ComboBox1 For i = 1 To UBound(Li...

To allow a different unit of measure when using SOP/POP Commitment
Would like the ability to use a different unit of measure for Inventoried Items when using SOP/POP Commitments. For example, if I have an Item on a Purchase Order where I am ordering 2 Cases (1 Case equals 10 Each), I would like to link the Item on the Sales Order document where all quantities of 10 Each are back ordered to the Purchase Order Item. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

Excel
I need to create an expense sheet in excel that has the same 'title' (e.g., "transportation", "business lunch", "advertising" read down the left side/columns, and "date", "cost", "vendor" read across the top/rows) information in column A (for example) on each page, for what will be a multi-page document. It seems that this would be resolved in a header-footer capacity, but I am uncertain of the proper procedure, partly because the info is rather complex. Help? See the "File->PageSetup->Sheet" tab. --...

create one invoice from two differant Orders or Quotes
Is it possible to create one invoice from two differant Orders or Quotes? -- Kind Regards, Bahaa Salem This is not possible out of the box but could easily be done using the SDK and some programming skills. -- Lior Zarfati, Dynamics CRM specialist, <a href=http://www.g-s.co.il>Guardian Information Systems</a> "Bahaa Salem" wrote: > Is it possible to create one invoice from two differant Orders or Quotes? > -- > Kind Regards, > Bahaa Salem Dear Lior, Could you send me any document helps me in that issue? -- Kind Regards, Bahaa Salem "Lior Zarf...

align two y-axis
I have a scatter line graph with two lines. The two lines are associated to different y-axis (primary and secondary). Is there a way to align the scale of the primary and secondary y-axis so that the point y=0 is the same major axis for both the primary and secondary y- axis? Other than this macro http://peltiertech.com/Excel/Charts/AlignXon2Ys.html Which I can't seem to get running becuase I am an extreme dumdum when it comes to macros... Hi, Double-click the primary Y axis and choose the Scale tab and set the Minimumn, Maximum and anything else you want. Repeat the process for the...

multiple columns, each with a forecast line
Hi i'm trying to build a chart that will display actuals (columns) and forecasts (which is a horizontal line about the same width as the column within the column) x-axis - product a, product b, product c y-axis - sales for each column (which denotes a product), I would like to plot a horizontal line to indicate the forecast. The column height indicates actual, and the line height indicates the forecast. any help would be most appreciated thanks K Making a horizontal lines the same width as the columns in the chart could be a task, but this might be a useful workaround. Sample data...

How can BB receive mail from two sub domains?
Hi We have two subdomains managed by a Windows 2000 server: membersedgellc.com scholasticfundinggroup.com I currently receive mail to membersedgellc.com on my blackberry. How can I receive mail from scholasticfundinggroup.com as well? My Outlook is configured with multiple profiles. Do I need to combine the Inbox's of Outlook? If so, how can I do it? Thanks for your help. On 26 Aug 2006 14:42:30 -0700, soup_or_power@yahoo.com wrote: >Hi >We have two subdomains managed by a Windows 2000 server: >membersedgellc.com >scholasticfundinggroup.com > >I currently receive mai...

Crosstab - column names
Hi, I have a query that looks like this: TRANSFORM mytable.var1,Count(mytable.var2) AS cnt SELECT mytable.var1, count(mytable.var2) AS TOTAL FROM mytable GROUP BY mytable.var1 PIVOT mytable.var2; and my result looks like this: var1 female male 1 30 10 2 40 20 3 50 30 Is there a way I can name the columns to "column1", "column2" and so forth, instead of female and male? Assume there are any number of columns. Thanks in advance, Jenni ...

Summarizing of columns for different days of month
I have an Excel spreadsheet with two columns. Column 1 contains a date e.g. 01 Jan 2005, column 2 contains a number. The rows are organized by date. I can have variable number (rows) of days per month ... but all days are "bunched together" I would like to either create a new sheet where I have the days summarized, in other words I want one row to contain 01Jan2005 with the total of column 2 another row with the total of 02jan2005 etc. I have been trying to figure this one out. Any help would be greatly appreciate it. Thank You! Try SUMIF. For example, let's say your dates...

Multiple instances of Dynamics CRM 4.0 on one domain
Hi, Does anybody know if this is possible. We had CRM installed on one server on the network and then attempted to install another instaance on a Virtual Server, but on the same domain. both connecting to their own local SQL Server DBs). The version on the Virtual Server works OK but now the original version doesn't work. If you try to browse it as any user you get the message: Access Error The system could not log you on. This could be because your user record or business unit you belong to has been disabled Any help would be appreciated Thanks On Jun 30, 12:05=A0pm...

OWA Login Differences
Looking for some clarity on different login requirments: WindowsXP + IE6 gives a prompt for User/Pass Windows2000 + IE6 gives a prompt for User/Pass/Domain With a default Exchange 2k3 SP1 install on IE6 XP client I'm required to put user@domain.com for the user. With IE6 2k no @domain.com is required. As well DOMAIN\username I know is anothe option. Why the difference between 2k & XP IE6 login prompts? Thanks, Dan it is a silly thing. It is strictly due to the client OS. It has nothing to do with Exchange actually. For XP, they decided to go the UPN model where a real userna...

comparing two columns
I need to compare cells 2 columns and return a value if different. e.g. A1: 2002 B:2002 A2: 2002 B:2003 A3: 2002 B:2002 Need to identify row 2 is different (with an x and a color?) I have about 1000 rows to compare. I tried some of the formulas listed in this section e.g. =if(countif(a:a,a25)>1,a25,"") but didn't return what i wanted. Paste this formula in C1 cell =IF(A1=B1,"Both Are Same","Mismatch") Now copy the C1 cell and paste it to the remaining cells of C Column depends upon your A & B Column Data. For Applying the Co...

Comparing two spreadsheets
I have extracted data from two systems into two seperate excel spreadsheets. they should be identical but are not. I would like to compare them to see which spreadsheet is missing information. Sheet 1 has - (a1) emp no (b1) hours (c1) date (d1) project id Sheet 2 has the same columns but has 60 extra rows I have sorted both sheets by emp no, date and project id I used a pivot table to show that multiple emp nos do not have the same hours over the two spreadsheets. I need to compare the information to ensure (a1) emp no, (c1) date and (d1) project id are all equal and then determ...

different versions unable to open
We have a small network of 5 computers 4 running with office small business 2000 and one running with small business 2002. I am unable to open publisher documents created with 2002 version with the 2000 version. Any ideas? Open the document in 2002 (or have the other user do it) then do a "save as" choosing publisher 2000 format from the "save as type" dropdown list. You may lose some formatting, but at least you can edit it. >-----Original Message----- >We have a small network of 5 computers 4 running with >office small business 2000 and one running with smal...