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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 15, 2009
I have two columns of data, and I need to create a third column to count the number of times that same line appears in the document (and then remove all but the first copy of that line). my data looks as follows (and it is sorted so all duplicate rows appear directly next to each other):
Adam1998 | Jan
Adam1998 | John
Adam1998 | John
Adam1998 | Paul
Adam1998 | Peter
Adam1998 | Peter
Adam1998 | Peter
Adam1999 |John
Adam1999 | Paul
I need this to look as follows:
Adam1998 | Jan | 1
Adam1998 | John | 2
Adam1998 | Paul | 1
Adam1998 | Peter | 3
Adam1999 |John | 1
Adam1999 | Paul | 1
View 2 Replies
View Related
Oct 27, 2009
This code counts all cells with a value from column B1:B:10, C1:C10, D1:D10, E1:E10, F1:F10, G1:G10 and H1:H10. And then give the cells of the 3 lowest outcomes a blue backcolor. The problem is that the code also colors any duplicate outcomes. How can I let the code skip any following duplicate outcome(s)?
View 2 Replies
View Related
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
Sep 13, 2006
I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.
The number order of precendence is 0-9 with zero being the lowest.
Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.
I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)
When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.
Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.
View 3 Replies
View Related
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
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
4455ONHIG
234234
66555556
66555556
66555556
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?
View 9 Replies
View Related
Nov 29, 2006
I have an Array:
FALSE
TRUE
TRUE
FALSE
TRUE
FALSE
FALSE
TRUE
TRUE
my aim is to count the maximum occurrences in sequence of False statements before they are interrupted with a True ( in this case 2)
I can figure it out what method to use, Formulas or VBA? More precisely my VBA sucks I am trying to use excel functions by creating a counter with if() and then sum it but obviously it is a dead end.
View 3 Replies
View Related
Mar 14, 2007
I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence.
Please look at the sample to see what I mean.
W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.
I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.
Dim rCell As Range, rRng As Range, vKey, lrow As Long
Set rRng = Range("F2:F199")
With CreateObject("Scripting.dictionary")
.comparemode = vbTextCompare
View 8 Replies
View Related
Feb 18, 2008
I have 2 columns on column contains 2 numbers, the other comlumn has names. We'll use A1:A4 and B1:B4 for the example.
Row A Row B
25 John Smith
50 Mike Jones
25 Chris Carter
50 Mike Jones
I want to be able to count one number in "A" but only if the name is not duplicated in "B".
My result for #25 would be 2.
My result for #50 would be 1, because Mike Jones is duplicated.
The results would be displayed in two seperate boxes, so I would only need on formula to count #25, then one formula to count #50.
View 3 Replies
View Related
