Sum Duplicates With Unique Criteria?

Aug 9, 2014

col A col B col C

AA 1 dog
BB 2 cat
AA 1 cat
BB 3 cat

The end result is:

AA 1 dog
AA 1 cat
BB 5 cat

In this example there are two examples of AA, but they each have a different animal, so they need to remain separate. BB is listed twice, but has the same animal, so the total for them is 5. I am looking for a way to express this with with a formula. I tried using Sumifs, and it sort of works, but I end up with BB listed twice, and I don't need that to happen....

View 3 Replies


How To Remove Only Unique And Keep Duplicates

Sep 9, 2009

I have been trying for a day now to figure out how to remove the unique cells and keep the duplicates only. I have tried a macro but it does not seem to work. Has any one run into this before. I have attached a sample what I would like to do is remove all non-duplicates from column A. The only macro I could find was this:

View 5 Replies View Related

Unique List With No Duplicates

Jan 11, 2007

I was wondering if there is a formula or a vba code to return each number once from a list.
For example I’ve got a list with app. 500 rows and maybe 80 different numbers in there, so one number could be 50 times in the list, another number maybe just once. Is there a formula, which returns me each number once?
Can’t think of any, or do I have to use a vba code be get the information?

View 3 Replies View Related

Identify Duplicates: Get A Unique List

Jan 12, 2010

I have a list of objects:


I know how to get a unique list. How do I identify those that are in the list more than once and how many times it is in the list?

View 2 Replies View Related

Filtering Unique Duplicates With Timestamps

May 28, 2012

I am relatively new to Excel so there may be a easy way to do this that I just can't see. Here's the background:

My raw data contains 10,000+ entries with many duplicates. Each entry though has a time stamp like so:

Unique Identifier: TimeStamp
S1111111111 10th July 02:00
S1111111111 10th July 02:10
S1111111111 10th July 00:50

What I want to do is to isolate the duplicates in this sheet of 10K+ that have the earliest timestamps and delete duplicates with later time. So in the above example, I would only be left with the 3rd entry as it has the earliest timestamp.

View 3 Replies View Related

Unique Random Samples - No Duplicates

Sep 4, 2012

I have data table


Column A are unique serial numbers
Column B, C, D contains test values.

F1= 50 (No Of unique random samples to be pulled - No Duplicates)

The out put range for sample data starts from F3:I3

View 5 Replies View Related

Get Unique Values In A List Of Duplicates

Feb 28, 2007

I am confident the answer is no, but is there any formula / combination of formulae that will seek out unique values in a list of duplicates (just like filtering unique records only)?

I have a list of many duplicating sales people and I want to just create a column on another sheet that automatically sorts out the unique values (then I will sum their sales numbers with the SUMIF formula).

(By the way, the reason I just don't use the filter function is because the list always changes so I would need to keep applying it.)

View 9 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
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 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

How Do You Create A Unique Random Number W/o Duplicates

Feb 5, 2008

I'm needing to generate a unique random value for a database with 3546 cases. The unique random values cannot be duplicates of each other. I tried the =RANDLOTTO function that I learned of in an old post on this board, but that results in "#NAME?" appearing in the first cell. I tried to install the Add-Ins (both the Analysis ToolPak and the Analysis ToolPak - VBA, but nothing seems to happen. Is there another way to generate these numbers?

View 9 Replies View Related

Count Duplicates As Unique Record, Sum Amount

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

Create Unique List From Column With Duplicates

Feb 27, 2008

I want to end up with a list of unique items (nothing repeated) from a variable length list starting in cell B5. That unique list is then placed in cell D3.

I am using the code below (found on Ozgrid) which almost works. But it seems to be dependent on what’s in column A (which I wish to ignore) for it to work.

How can I modify the code so that the results are based on column B only?

Dim Rng As Range
Dim fRng As Range
Dim lRow As Long
Dim Dest As Range

lRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("A5:B" & lRow)
Set Dest = [D3]

With Rng
. AutoFilter Field:=1, Criteria1:="1*"
Set fRng = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
With fRng
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Dest, Unique:=True

End With
End With

View 7 Replies View Related

Remove Duplicates :: Advanced Filter, Unique Records Only

Jan 14, 2010

I'm having a problem deleting duplicates from list in excel. I’ve attached a sample. I’ve tried the following:

1-Advanced Filter, Unique Records Only

2-Remove Duplicates function in Excel 07.

3-Pivot Table

4-Colour Conditional Formatting, sorting by colour

5-B2=IF(A2=A3,”Dup”,”Not-Dup”). The entire column returns “Not-Dup”

6-I’ve tried to resolve using the fix shg & teylyn suggested to Hillto in this thread, but am unable to get the ‘Numeric’ Keypad to appear in the ‘Find’ Function.


View 14 Replies View Related

Macro To Find And Delete All Duplicates And Keep Only Unique Values

Sep 22, 2009

Looking for a macro to find and delete all duplicates and keep only unique values from a column. For example column AS has

All red items need to be removed and keep only green items. Would also like the entire row the duplicates are in to be deleted.

View 6 Replies View Related

Vertical Lookup To Remove Duplicates And Return Unique Values Horizontally?

Mar 3, 2014

In column CT between rows 11:210 is the vertical data that includes duplicates. I am looking to create a formula that can lookup each value between CT11:CT210 and return only the unique values horizontally starting in cell CW9 and onwards.

Example: CT11:CT14 looks like
and so on and so forth..

I would then like this formula to return the above data (which goes all the way to CT210) in this format starting in cell CW9 - 5x20 then CX9 - 6x4 and finally CY9 - 5x8.

View 4 Replies View Related

Count Unique Values / Text Within Range Of Cells That Contain Duplicates / Blanks And Errors

Jun 25, 2014

How I can create a simple formula to count unique values/text within a range of cells that contain duplicates, blanks and errors?

For e.g., in Column A (row 1 - 10):



View 3 Replies View Related

Sum Without Duplicates With Other Criteria?

Nov 20, 2013

I need to do a sum with multiple criteria and that can account for duplicate entries and select the correct duplicate to add. I have a sheet with 6 columns of data: TripDate, Company, DepartureTime, BookedTravelers, TripNumber, DataEntryDate. It's just a running data dump from multiple sources and times and can therefore have duplicate entries for a specific trip but where the number of people booked has changed and I need the sum to use the most recent number (dataentrydate is newest).

TripDate Company DepartureTime BookedTravelers TripNumber DataEntryDate
10/31/13 A 15:30 10 111 10/01/13
10/31/13 B 15:30 11 999 10/01/13
10/31/13 C 15:30 12 999 10/01/13
10/31/13 A 16:00 20 222 10/01/13
10/31/13 A 17:00 30 333 10/01/13
10/31/13 A 15:30 50 111 10/15/13
10/31/13 A 15:30 40 111 10/25/13

Currently I have this in a seperate worksheet to break down the hourly loads where A2 is a date reference I can change to what date I need, and each company is listed in a different column with hours running down Column B from 0400-2200:

=SUMIFS(BookedTravelers,TripDate,Template2!$A$2,Company,Template2!J$4,DepartureTime,">=" & $B5,DepartureTime,"

View 3 Replies View Related

Comparing Two Duplicates To Second Criteria

Aug 24, 2009

I have two columns: Column A with SS#, Column B with loan #. i need a formula that would return 2 different loan #s for same SS# on a different worksheet. Example

SS#:Loan #ResultSS#Loan #123567812312356781231235678345123567834534578917862950013351

Spend all morning on this. Tried various INDEX, COUNTIFs, MATCH, VLOOKUPs but when looking at the same SS# in a column it always returns the first Loan#.

View 13 Replies View Related

Removing Duplicates With Criteria

Feb 6, 2007

I'm adding data from a report into a spread sheet and some of it will be duplicated. I want to remove the duplicate data, but is there a way to differentiate between the older (and more complete) data and the newer data? In other words, how do I get rid of the duplicate while keeping the one I want to keep?

I was considering the advanced filter, but if I create too many columns of criteria will it be seen as unique?

View 9 Replies View Related

Duplicates Counting With Plural Criteria

Jan 20, 2010

This is a follow-up of my previous post which was brilliantly solved. I found out that I need someting additional, which is over my head really.

I know have the following formula: IF(SUMPRODUCT(($G$4:$G$3000=$G6)*($AA$4:$AA$3000=AA6)*($O$4:$O$3000=O6)*($I$4:$I$3000=I6))>1,....

However, I want to change the end into something which can count the number of appearances of the duplications in a specific row, so I would get results saying that they appear 1 time, 2 times, 3 times or 4 times (4 being the most) in the list and could be placed after the text value of O and I (I398&" "&O398,"-"))?

I hope I formulated this well enough....

Also, does anybody have an idea how I can message my file? I've got close to 3000 rows all the way up to AB and my pc now starts to complain...

View 8 Replies View Related

Sum Values Excluding Duplicates With Criteria To Be Met First

Sep 23, 2013

Below is the criteria I need a formula for.

First Criteria - If column D=John Smith,

Second Criteria - Excluding duplicates in column A, (red are the unique values I need)

Third Criteria - Sum of column B that meets the above criteria first

Result I am after is

John Smith - $154,330.00
James Brown - $150,520.00
David Williams - $161,390.00



View 1 Replies View Related

Lookup With 2 Criteria & Duplicates Exist

Aug 16, 2007

The yellow highlight is where you input the data needed. The problem is it doesn't show the result when the letter has a duplicate. For example, when I enter "a" in the first input cell(input letter) and "1" in the next(input number), I get the result("a1"). However when I input "a" in the first and "2" in the second, the result just shows a blank cell, and so forth.

View 9 Replies View Related

Conditional Format Duplicates Ignore Certain Criteria?

Jul 30, 2014

I have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)

Is there an easy way of doing this?

View 10 Replies View Related

Rank Based On Criteria And Exclude Duplicates

Mar 20, 2014

I have a large spreadsheet where I've successfully ranked a large series of data based on revenue, using product classification as a criteria & summarising this on another page with the top 20 products. The problem I'm having is that within certain criteria's I have some products with the exact same revenue figure, so if the 2nd ranked product within classification "C" for example has revenue of $100, & there's another product within the same classification also with $100 of revenue, on my top 20 report I have results for the 1st & 2nd ranked product, but a blank for the third product & then results again for the fourth

The formula I'm using is


View 2 Replies View Related

Count By Criteria Based On Duplicates In Different Range

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

Multiple Criteria Ranking To Ignore Duplicates

Apr 2, 2008

I posted a thread a while ago asking for a formula to rank based on multiple criteria (Rank On Multiple Criteria). The following; = SUMPRODUCT(--($A$2:$A$50=$A2),--($B$2:$B$50=$B2),--($D$2:$D$50=$D2),--($G2>$G$2:$G$50))+1)

works an absoulte treat, however is there a way to modify it so that does not allow duplicates? The data in the G column refers to time values acheived during testing of athletes, the problem arises when two athletes achieve the same score and thus they are ranked the same. However, body mass is also measured (F column) and in essence the athlete with the lower body mass has achived the better score and thus should be ranked accordingly.

View 3 Replies View Related

Removing Duplicates Based On Column Values And Criteria?

Apr 3, 2013

removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.

Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.

I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria

I would need an excel formula not macro

View 9 Replies View Related

Count Excluding Duplicates Based On Multiple Criteria

Jul 4, 2014

I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!

=IFERROR((SUMPRODUCT(--(A3="2014"),--(B3="1"),--(D3="Boc Limited"),)),"")


supplier name


View 2 Replies View Related

Make Unique List From List Without Duplicates But Hold Cell And Pull Corresponding Dates

Apr 18, 2013

I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.

For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).

I am also going to upload a data sheet, and an expected results sheet.

unique list.xlsx

View 1 Replies View Related

Sum Unique Values Under Criteria

Apr 16, 2009

With reference to a thread that is nearly similar to my own problem:

I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the C-column. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"".

Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved