Count Duplicates In 2 Columns, But Not If Column A=b

Jun 20, 2007

I have data in two columns where column A is a name and column B might be a synonym or might just equal the value in column A. I want to count duplicates if cell A2 matches any other cell in column A or B, unless the only match is with cell B2. If cell A2=B2, but also matches cell A7 then it should be counted as a duplicate. I've searched through a lot of the threads and found a lot of useful information, but nothing that seems to do what I need.

View 4 Replies

Count The Duplicates In At Least 2 Columns

Jan 20, 2010

I have columns from A to AA and need to count the duplicates in at least 2 columns and accordingly need to give them values on the basis of their ratings (A, B, C, D) and type of work (book, article etc) given in yet other columns.

I tried COUNTIF, but that does not work for multiple perimeters
I tried SUMPRODUCT, =SUMPRODUCT(($G$4:$G$3000=$G28)*($AA$4:$AA$3000=AA28)), but that only gives a multiplication of my input without the possibility of giving new values to the output.

I tried AND(IF( but that did not want to work either really...

I think the solution is ifsum, but I do not know how to use it in this scenario...

I know this all sounds a bit blurry, so let me attempt to make it more concrete.

Author Title Year Rating Type Desired outcome
P Marx I 2005 I A I Book I duplicate
A Hegel I 1923 I B I Article I -
B Kant I 1674 I D I Book I -
A Derrida I 2005 I D I Monograph............................

View 5 Replies View Related

Count Unique Duplicates In Two Columns

Jan 25, 2010

I need a one formula solution to compare two columns for duplicates and count the number of matched pairs. Need to ingore blanks. Can have matched numbers or letters. Asterik denotes a blank (empty) cell. Also only consider the first matched pair if there are more than one matched pairs.

Col A Col B
a
b a
* b
c c
d y
x f
f z
f z

In this case I only want to consider a:a, b:b, c:c and f:f, resulting in the count of 4.

View 9 Replies View Related

Count Duplicates In Column?

Jul 21, 2014

I have a column with something like 20 thousand rows. I would like a formula that basically checks the column for duplicates and numbers the sets of duplicates. So if there are 5 cells that are all "Server10" I would like for the formula to place a one next to all of those cells. And for the next set of duplicates it finds I would for it to place a 2 next to all of those cells. Essentially my end goal is to sort the sheet so that all of the matching duplicates are all together. There might be some simple filtering method here that I'm overlooking but if there is..... I'm overlooking it lol. And to be clear I don't want to eliminate the duplicates, I just want them grouped together.

I've attached a very simple example of what I would like the formula to do. The cells in the right column would be the result of the formula.

View 4 Replies View Related

Count Column Of Text Excluding Duplicates?

Dec 5, 2012

I need to count a column of text excluding duplicates, but only if they are duplicates of the cell directly above them in the column. Example:

a
b
c
c
d
d
a
b

I want this to return 6 instead of 4. Is this possible?

View 9 Replies View Related

Compare Two Columns-delete Duplicates From Just ONE Column

Oct 7, 2009

I have column A that is 1,500 records.
I have column B with 40,000 records.

Among the 40,000 records in column B, duplicates of ALL 1,500 records from column A exist.

My question is: How do I find WHERE the 1,500 dupes are and how can I delete JUST those records?

View 3 Replies View Related

Combine Columns To Create A New Column With No Duplicates

Nov 7, 2013

Columns A, B, C and D contan a list of Names, I want to be able to in column E list all the items in A,B,C and D with out duplicates. How would I do this?

View 1 Replies View Related

Merge Several Columns If Duplicates Exist In Column A

Apr 13, 2008

I have got a wordlist in worksheet "original" which looks like:

Before: [Code] .....

I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".

After: [Code] ......

The macro must be able to deal with very large lists. biger than 200 000 words in column A

Here is the excel file containing the example : excel file

View 14 Replies View Related

Merge Columns B,C,D,E And F, If Duplicates Exist In Column A

Apr 13, 2008

i have got a wordlist in worksheet "original" which looks like:

Before:

View 14 Replies View Related

Pivot Table To Not Count Duplicates - But Show Duplicates When Click Into It

Aug 24, 2011

In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:

I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.

I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).

View 3 Replies View Related

Count Duplicates And Duplicates With Suffix As One Instance

Mar 23, 2014

I have a list of isometric drawing numbers ending with a [underscore]weld number e.g. 1692-SG-0040-04_05.

Some welds are repaired--in that scenario the amended weld number will be 1692-SG-0040-04_05R1, and even 1692-SG-0040-04_05R2 if repaired for a second time.

On occasion a weld may be cut out entirely and a new weld done. The weld number for that will be 6317-FG-1690-02_06C1.

And here's a wrinkle I've just verified...a cut weld may also be repaired so the weld number will look like 1698-SG-0077-01_04C1R1.

Is there a formula to count these as one weld:
1692-SG-0040-04_05
1692-SG-0040-04_05R1
1692-SG-0040-04_05R2

This as one weld:
6317-FG-1690-02_06
6317-FG-1690-02_06C1
6317-FG-1690-02_06C2

...and this as one weld:
1698-SG-0077-01_04
1698-SG-0077-01_04C1
1698-SG-0077-01_04C1R1

View 7 Replies View Related

Excel 2010 :: How To Count Duplicates Of Duplicates

Jan 24, 2012

I am having trouble creating a function to count duplicates of duplicates.

An example of the data table 1 is:

Product 1 2nd
Product 1 2nd
Product 1 New
Product 1 New
Product 1 Flt
Product 2 2nd
Product 2 New
Product 2 New
Product 2 Flt
Product 2 Flt
Product 3 2nd
Product 3 2nd
Product 3 2nd
Product 3 New
Product 3 Flt

I created a new table (table 2) and made a list of all the Products on table 1 and removed the duplicates. I now have 3 columns with titles New, 2nd and Flt as follows:

New 2nd Flt
Product 1 XX XX XX
Product 2 XX XX XX
Product 3 XX XX XX

I am trying to count the duplicates for each product (XX), but I can't seem to work it out. I've tried the MS help function, but unsure of the actual formula I need to be using.

I am using Windows 7 and MS Office 2010.

View 2 Replies View Related

Incremental Count Across 2 Columns While Considering 3rd Column

Apr 29, 2014

I have a list that tracking teams, specifically, the game number of the season. I also would like to start the count over every month. I have 3 columns to consider in the formula - 1) the month, 2) the home team, and 3) the visiting team. What I hope to accomplish is something like I have listed below. The # columns are blank in the real file - this is just a small example to illustrate what I'm after. Is something like this even possible? Attached is an excel file with this same data.

example.xlsx‎

View 2 Replies View Related

Count Total Blanks Of Various Columns But With The Range Of Column A To The Last Used Cell

Jan 30, 2014

I need to be able to count all the blanks in columns other than A but only until the last used cell in column A. I am using a formula right now that counts the blanks in column A until the last used cell but I don't know how to apply the range of column A to other columns like B and C. Here is an example of what I hope to accomplish:

Formula used in A1 that I need applied to other columns but with the range of column A

="Total Blanks: "&COUNTIF(INDEX(A2:A8,MATCH(TRUE,A2:A8<>"",0)):INDEX(A2:A8,MATCH(2,1/(A2:A8<>""))),"")

Here is an example of what B1 and C1, with the formula, would look like if it counted blanks but with the range of column A

Total Blanks: 3
Total Blanks: 6
Total Blanks: 2

[Code].....

View 2 Replies View Related

Macro To Compare Columns A & B And Dispaly Any Duplicates In Columns C & D

Feb 21, 2009

what I'm after is a macro to check the contents of Column 'A' against column 'B' and display any duplicates in Columns 'C' & 'D'.

N.B. The headings of Columns C & D are :-

C = Value Found in Column A

D = Value Found in Column B

Any duplicate entries logged in columns C & D should be listed in C2,C3,C4....C20 and D2,D3,D4......D20 etc (in effect creating two new lists)

View 5 Replies View Related

Count Only Duplicates

Sep 7, 2008

I would like to be able to count only the duplicate numbers without having to put the actual number in the formula.

123456
56478
951753
7727969
08259186
10741916
10741916
123456
123456

=countif(duplicate)

View 14 Replies View Related

Count Without Duplicates

Oct 13, 2009

In my attachment is what i'm working with. In the first worksheet tab, "NOTBA 10 09", i need it to count what's in the other worksheets, minus the duplicates.

So C4 should be referencing to H3:H21 in the 04-Edmonton worksheet. The number it should be showing is 11, however it's counting 15 because there's 5 entries for CDAU034479. Is there a way for it to basically count what's in that column, minus the duplicates?

View 2 Replies View Related

Count And Flag Duplicates

Jun 12, 2014

I am using a CountIf formula to flag duplicate entries in a column of data, in a Column I have "=COUNTIF(P:P,P1)" It returns a number equal to the number of repeats of the data in call "P1". I then sort by that column to isolate all rows which have duplicates in Column "P". The cell has "1" if there are no repeats, "2" if there is one repeat, "3" if there are two repeats, etc. What I would like is a formula which would put a "1" in the cell the first time it sees data, then something else when it sees repeats. That way when I sort, ALL the repeats will be together and can be deleted. Is that possible?

View 7 Replies View Related

Count And Delete Duplicates

Mar 12, 2014

I have an excel sheet which has product list according to each site. I want to count any duplicate products and delete the duplicates.

I have attached an excel worksheet with example template to show what i want it to look like.

View 1 Replies View Related

Count Only Duplicates And Not Blanks

May 23, 2012

Is there anyway to make this work without having to enter a specific range. For example I want to count the duplicates in column U, but don't want it to count blank cells.

Here is my formula right now, and it works, but it counts all of the blank cells in the row as duplicates. How can I stop that?

=SUMPRODUCT(--(COUNTIF(U:U,U:U)>1))

View 4 Replies View Related

Count Without Counting Duplicates Twice

Nov 27, 2007

i need to put serial no. without considering duplicate value.. i have ' N ' of value somewhere data is repeating when i am giving serial no i want to ignore duplicate value ...example given ... please see attached file..

View 4 Replies View Related

Locate Values That Are Duplicates Then Count Them

Mar 11, 2014

I have a spreadsheet that has the following

I have 30 teams with 15 players each

I have teams in rows and players columns with their ID and then name so B2 would be 444 and B3 would be Johnson and B4 would be 429 and B5 would be Smith

What I want to do is run a formula that counts the duplicate ID's not names and then shows them in a list like below

Duplicate ID, Number of times in worksheet
444 , 23
411 , 21
211 , 15

How I can do this.

View 3 Replies View Related

Sequentially Count Duplicates In List

Oct 2, 2008

I am trying to work out how I can assign a sequential value to sets of duplicate data within a list. i.e. where I have duplicates in a list I want each of the duplicates (that are in the same set) to have a sequential number assigned and for that to start from 1 each time it discovers a new set of duplicates.

RED
GREEN
YELLOW
PURPLE
RED
ORANGE
BLACK
GREEN
RED

Where a colour is duplicated in the list I want it to have a sequential number assigned. When a new set of duplicates is found the numbering starts from 1 and sequentially increases. So the above data list would resulting in the following output

RED 1
GREEN 1
YELLOW
PURPLE
RED 2
ORANGE
BLACK
GREEN 2
RED 3

Have tried variations of the RANK function, but not sure how to get the sequential nu,bering to restart with each unique set of duplicates. I would also like to do this without VBA if possible using the built in functions.

View 8 Replies View Related

Count Of Unique Values With Duplicates

Nov 29, 2007

Good Morning Peps (Oh thats for users in Western Europe, got to get this right!)

I have these 12 columns (Well there is 12 Columns but this forum's HTML is not showing the last two lol, would much easier if Office Web Components was able to run on Firefox): ...

View 4 Replies View Related

Count Number Of Different Values In Duplicates

Apr 10, 2008

Below is a range of data i am working with, i am trying to create a formula that will count the number of different entries in column A for each different entry in column B.
ie how many differnt values are there for "Packing"

008003PICKING MISTAKE
008042UNFIT FOR PURPOSE
008035PACKING
008035PACKING
008035PACKING
007960CHANGE OF MIND
007986PACKING
007986PACKING
008050UNFIT FOR PURPOSE
008070CHANGE OF MIND
008070CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008074CHANGE OF MIND
008086PACKING
008085PACKING
008085PACKING

View 6 Replies View Related

Find Values And Count Duplicates In A Sheet

Jul 30, 2013

I would like to find the values and count duplicates on a sheet. Here is my example:

A1
A2

Organization1
Organization2

XXY
BBA

ACC
XXY

BBA
ABC

I would do countif, but I do not know my values (this is from a report that I ran from a database) and it would take too long to find all the individual values every time I needed to run this report. So this is what I am looking for (a graph would work too).

A3
A4

ABC
1

ACC
1

BBA
2

XXY
2

View 9 Replies View Related

Count Each Item In Filtered Range With Duplicates

Nov 20, 2006

Is it possible that, once filtered, you can count the amount a filled in cells in a column range...BUT! These cells are ID numbers for stocks, so CAN contain duplicates which represent accounts, Therefore, any duplicate will be counted as 1...

eg

12345325
12345325
435ghfdhy
5464OKff
SEDDONF4

[Code]...

As we can see here there are 14 lines of data but only 7 make up the dataset

so if X was the variable assigned to this it would = 7

Is this possible, in a loop or some sort, Would VBA hold all the Instances in its memory???

ERROR#9 OUT:

View 9 Replies View Related

Count The Number Of Invoices Excluding Duplicates

Jun 14, 2007

I have a list of invoices numbers in column B, some are duplicated

I want to count the the number of invoices excluding duplicates.

View 9 Replies View Related

Count Duplicates Conditional Formating Highlight

Dec 17, 2009

I am trying to teach myself something new, say I have a list of numbers in two rows and I want to count the numbers and have it tell me if there are duplicates and highlight the duplicates. I got the part about getting Excel to tell me if there are duplicates but I can not seem to figure out the conditional formating part. I uploaded a sheet.

View 3 Replies View Related

Count Duplicate Rows Then Delete Duplicates

Mar 15, 2009

View 2 Replies View Related