| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count number of duplicates between 2 columns
Col A Col B
apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
Enter this in C1 against apples in A1
=COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
Thanks... but hoping for a single formula with no helper columns... thanks...
"Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
No problme.
Use =COUNTIF($B$1:$B$6,B1) assuming you have values in Col B. "SteveC" wrote: Thanks... but hoping for a single formula with no helper columns... thanks... "Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
oh, and one more thing, don't count number of matching blank cells
"SteveC" wrote: Thanks... but hoping for a single formula with no helper columns... thanks... "Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
I use
=COUNTIF($B$1:B1,B1) This give me 1 against the first occurrence, 2 against the second and so on... I can then retain the rows with 1 and delete others. "SteveC" wrote: Thanks... but hoping for a single formula with no helper columns... thanks... "Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
That would do it if I was only worried about Column B, but I want to compare
it against Column A. I just posted a new question with more details about the problem. Thanks for taking the time to help. "Sheeloo" wrote: No problme. Use =COUNTIF($B$1:$B$6,B1) assuming you have values in Col B. "SteveC" wrote: Thanks... but hoping for a single formula with no helper columns... thanks... "Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
Hi Steve
Try =SUMPRODUCT(--(ISNUMBER(MATCH(A1:A6,B1:B6,0)))) -- Regards Roger Govier "SteveC" wrote in message ... oh, and one more thing, don't count number of matching blank cells "SteveC" wrote: Thanks... but hoping for a single formula with no helper columns... thanks... "Sheeloo" wrote: Enter this in C1 against apples in A1 =COUNTIF($B$1:$B$6,A1) and copy down "SteveC" wrote: Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count number of duplicates between 2 columns
Try this:
=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,B1:B6,0)))) -- Biff Microsoft Excel MVP "SteveC" wrote in message ... Col A Col B apples apples oranges pears pears bananas pineapplies coconuts grapes raisins Count the number of times that data in column A matches the data in column B the formula in this case would return: 2 thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I compare two columns and return a number count | Excel Worksheet Functions | |||
| Count Employee Work Time - Don't Count Duplicates | Excel Worksheet Functions | |||
| Pivot Table - How do I count number of columns? | Excel Discussion (Misc queries) | |||
| count duplicates/total number of occurances | Excel Discussion (Misc queries) | |||
| Count number of unique items in a column that contains duplicates | Excel Worksheet Functions | |||
Linear Mode
