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
ADVERTISEMENT
Jan 27, 2006
Need count formula to count records with amounts in either columns E, F, or G. For example
Need a formula (not VBA) in cell F2 to return a count of 5 records counted that have an amount in column E, F, or G (but only count as one record when amounts exists in multiple columns):
__|____E___|____F___|___G___
_7 | 1200.62 | 1500.53 | -0-
_8 | 1000.00 |________|_1620.00
_9 | 7000.00 |________|________
10 |________|________|________
10 | 2000.00 | 3000.00|________
11 | 8000.00 |________|________
View 8 Replies
View Related
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
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
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):
Proj-001
Proj-001
Proj-002
Proj-004
#N/A
#N/A
Proj-007
Proj-002
View 3 Replies
View Related
Nov 11, 2008
I have a spreadsheet with a list of items for a company inventory. For each item there is a row that has all the details related to the item. There is also a "sub-row" for each item that only has the item number and current costs and pricing for the item.
I have a 2-part task that I am having trouble with:
First: I have to essentially update the "main row" with the updated cost and pricing info from the "sub-row" wherever one would exist and...
Second: Delete the duplicate row for the item (the one with less info).
My production file has thousands of rows, but I have created a sample file if anyone can help me with getting my task accomplished. I am new to these forums, so I'm not sure how I could attach the sample file to this post, but if someone could tell me how to do that I will provide it. In the mean time I will just insert a screenshot.
View 9 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
May 8, 2009
i'm creating a spreadsheet with records of repair jobs on vehicles that my company is/has performed. the problem is that, the way our system works, there are currently no unique identifiers for jobs, meaning i cannot create a master list with various vlookup tables with only the information relevant for our engineers on them.
is it possible to create a macro or use some other system, that will create a new, unique code when a line/cell is filled in? because we end up sorting the list by different criteria on a regular basis (to find specific jobs) the system can't be relative (i.e. +1 to the code above).
View 4 Replies
View Related
Jan 25, 2013
I have a combobox on a Userform, when the userform initializes i want it to populate Combobox1 with all the Unique values from SHeet 1 Column A (variable length to the column)
I dont know how to select a unique record only.
*thinking a bit further ahead i have a second combobox that i need to populate with unique values from column B on sheet 1, where in column A matches what was selected in combobox 1
View 5 Replies
View Related
Jun 1, 2006
I am trying to filter a list where the number of occurences of unique cost codes for each emp No is more than 1. What is needed is the emp no's who have only one costcode to have '0' in the 'no of codes' column. If more costcodes per empno, I need the first costcode to have 1, second code 2, etc in 'no of codes' column. The idea is at the end to be able to filter and delete all those with '0' in, as I only need to work with emp no's that have more than 1 'cost code'. See attached file. The records are in col A, the costcodes assigned to them in B, the formula in C, at the moment is:
=IF(AND( COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)>1),C1, IF(AND(COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)<2),(C1+1),1))
View 4 Replies
View Related
Jun 13, 2007
I have to copy and paste each group of "Control Accounts" from column A into a separate workbook. I want to create a macro that can open as many new workbooks as there are unique records in column A and copy paste each group of unique records into those different workbooks.
View 7 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
Dec 31, 2008
Col A contains SS#'s and Col B contains names. Quite often there are duplicates and sometimes triplicates because they may be sending payments for multiple providers. We charge one fee per client (unique SS#), regardless of the number of times they may be on the spreadsheet.
I can get a grand total by summing the unique items by using this formula:
=SUM(IF(LEN(A2:A4970),1/COUNTIF(A2:A4970,A2:A4970)))
and then doing a CTRL-SHIFT-ENTER to put brackets around it so it will "take."
However I now need to show the fee by each unique name. Right now I am inserting Col C, typing in 3.00 into C2 and copying on down, but then have to manually remove the amounts in the rows which contain duplicate ss#. Long painful process. How can I put a formula in Col c so that it will only show fee one time by the first unique ss#?
View 9 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Sep 1, 2008
Is there a formula to count the amount of days, for example.....a1=8/1/08 b2=8/10/08 then c1=9 days....
View 4 Replies
View Related
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
View Related
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
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
Jun 10, 2009
I am trying to count the amount of cells in a column (Col H) containing data against a certain date which is displayed in another column (Col B)
Eg.
Col B................-...........Col H
13/06/09.................FIRST LAST
13/06/09.................
13/06/09.................FIRST LAST
20/06/09.................FIRST LAST
20/06/09.................
So that in the sheet next to it it would appear:
13/06/09 - 2
20/06/09 - 1
The dates don't appear in row order and the sheet has a filter on it so I am unable to select the Col H and just use COUNTA
View 11 Replies
View Related
May 6, 2014
I have a worksheet where
Column D lists a service offered by a business, such as IMC, TC, Respite, etc
Column M lists how long the customer has used this service for in weeks, such as 4,5,3.5,6 etc
What I would like to do in another column is to count the number of clients who have used the service for 6 weeks or more broken down by the service type i.e. Count how many people have used IMC for 6 weeks or more.
If possible I would also like to be able to count those who have used a service for between 5.1. and 5.9 weeks too, again by service i.e. Count how many people have used IMC for between 5.1. and 5.9 weeks.
View 2 Replies
View Related
Jan 12, 2010
I have a list of objects:
A
B
C
D
X
D
A
F
G
H
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
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
Sep 4, 2012
I have data table
A3:D103
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
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
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
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
Apr 29, 2012
I am trying to count the amount of times 4, 5, and "11-5" show up in a column and sum it at the bottom. I tried
=sum(if(c4:c41="4"+c4:c41="5"+c4:c41="11-5",1,0)
As an array and keep getting N/A. So im not sure where my problem is.
View 6 Replies
View Related
Jan 17, 2009
I have a spreadsheet containing 10,000 + entries.
Each Entry is Dated within Column D2:D10786 in this format - 1-Nov-08 (example).
Lets say i have a cell on another sheet Cell A1 and in this Cell i want it to Count how many Cells contain the dates from Nov-08 in my Date column..
View 9 Replies
View Related
