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
ADVERTISEMENT
Jan 1, 2014
I cannot get various formulas (Countif, Match, Frequency, Etc) to work properly.
I am trying to arrive at a total number of matches of numbers in cell range B1:G1 with any numbers entered into the cell range of K1:P11 and have the total of matches display in cell H1.
However I do not want to count duplicate numbers from the K1:P11 cells. (if the number 5 in posted in K1:P11 multiple times I only need it reported once in H1)
B1:G1 is the constant and the numbers will not change - K1:P11 cells will be populated by adding numbers until the all the numbers in B1:G1 is completed and match.
Range
B1 C1 D1 E1 F1 G1
2 7 19 45 22 13
H1 Total of matching numbers in cell range K1:P11
View 3 Replies
View Related
Jun 23, 2006
I'm looking to copy 1000 cells of singular form text into the next column
and make it plural. Is there a function to do that?
View 9 Replies
View Related
May 23, 2014
I have a dataset that has a good number of duplicates, many of which have more than 2 records.
In these sets of duplicates, there are two fields that have many combinations of values.
I would like to find a way to COUNT THE COMBINATIONS in all of the sets of duplicates, as in, every time there is a type of a certain combination of values, create a count for it:
12345 9 9
12345 9 0
123456 9 9
123456 9 0
(Total: 2) For 9/9 and 9/0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
(Total: 2) For 0/0 3x
I have attached a listing of types of combinations that I found by scanning the database, but I don't know what formula or functions would do the trick. I don't think there is something in Subtotal or the Count functionality that would apply but I will fumble around.
View 11 Replies
View Related
Dec 24, 2008
I currently have a spreadsheet that grabs data from 10 columns using vlookup.
This data is transferred to the new sheet in individual cells. (see attatched)
what i want to do is create a list from them cells, filtering out duplicates & counting them.
Creating a list like this:
720x560 = x5
452x282 = x3
etc.. etc...
I've tried everything i can think of, but i'm imagining that its a vba job to do what i'm asking.
I'd appreciated it of someone could point me in the right direction.
View 7 Replies
View Related
Sep 10, 2008
How do I count the number of air handling units, "AHU" column without counting the duplicates........
View 9 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
May 13, 2014
I'm trying to take column A (number of records can change from time-to-time and may contain blanks), copy that to column B less duplicates and then use a count forumla to count items in column B based on original list in A. I am aware of how to do this in Excel but am interested in VBA.
Example
Column A
Jim
Jim
Mike
Jeff
Jeff
Jeff
[Code]...
View 4 Replies
View Related
Oct 17, 2013
I found this formula =SUM(1/COUNTIF(A1:A17,A1:A17)) onlinesometime ago and have been using it regularly. It counts the number ofduplicates in a range of cells. I have been trying to modify this formula sothat it only looks at the first two characters in the cell. So, instead 7duplicates it would return 5 duplicates because the formula would only belooking at the first 2 characters in the cells.
A
1
GD020T01B02
2
GD020T01B02
3
GD020T01B02
[Code]...
View 2 Replies
View Related
Nov 3, 2009
So i'm try to automate a process that I currently do using filters.
Here is the problem:
I have a list of text in a column (usually 700 or so entries).
I need to remove any entries that contain 1 of 42 keywords.
So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.
example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords
somewhere else I would have a list of keywords in a column.
View 9 Replies
View Related
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).
Example:
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
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
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
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
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
A
B
C
D
[code]....
View 1 Replies
View Related
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
Jun 2, 2009
i have to create a summary of the productivity of the administration staff in my office. i have a table of raw data that shows the jobs they open each day, but i need to find a way of counting the number of those jobs.
i've included an example so that its easier to understand what i'm getting at.
on the first sheet (Jobs) i have the table of raw data. each administrator opens jobs each day which can either be S (standard) or W (Warranty). the second sheet (Summary) shows the way i need the data to be summarised. effectively i need to narrow my count by 3 criteria (Date Opened, Job Type and Administrator).
i've used DCOUNT functions in the past to count data by more than 1 criteria, but as this system will be in place for every day of the year, that means i would have to produce a table for every administrator and every type of job and every day of the year. which would be 3650 different tables, and that's too many.
is there a way i can do this more effectively? or can i somehow make the DCOUNT tables relative so that the table can look up the date required, reducing the number of tables required to 10?
View 14 Replies
View Related
Oct 18, 2009
I am looking for help on 2 aspects on my spreadsheet.
My Spreadsheet looks as follows:
My first query is - How can I create a formula which will show in cell G3 the amount of cells from a number value in f3 (inclusive) down until the next number value is reached (but not including it)?
So for the first instance - it would be f3 down to f14 - which is 12
My second query is a little more complex...
I am looking to display - for the same criteria above - all entries in column B in their own seperate cell. So for the first number - it would show 02B:D5 in cell H3, 15B:C5 in I3 and so on...
View 9 Replies
View Related
Feb 15, 2013
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
View 6 Replies
View Related
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
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
=IF(Y12="NA",0,SUMPRODUCT(--(Y12=$Y$12:$Y$6000),--(AC12
View 2 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
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
Jun 11, 2014
I want to count words given two criteria.
So I basically want to combine the COUNTA function and the COUNTIFS function.
I have attached a sample spreadsheet in the post.
Test Excel.xlsx
View 12 Replies
View Related
Oct 7, 2009
I have common tasks where I need to sort through large amounts of data to receive a total number of line entries matching multiple criteria in different columns. I've attached a Workbook with a simplified example. On STATS!B1 I would like to total, from DATA!, all lines which have "1A" in A:A and any of the following in E:E (RP, SAO, AE, RSNR).
View 4 Replies
View Related
Jan 12, 2010
I am trying to count the total number of oil rigs which have revenue in column H (postive or negative revenue, just not zero), revenue in column L, and have the owner name "Transocean" in column B. I'm trying to use this SUMPRODUCT formula but it's not working.
=SUMPRODUCT(--('Golden Source'!H2:H6280),--('Golden Source'!L2:L6280),--('Golden Source'!B2:B628="Transocean"))
View 9 Replies
View Related
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
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"),)),"")
A
B
C
D
E
year
week
smsupplier_code
supplier name
orders_no
[code].....
View 2 Replies
View Related
Apr 6, 2013
I need to count the number of occurances that meet two criteria i.e.,
Status needs to be UNSOLD
Size needs to be =>10.5 and also <12.5
Dress
Status
Size
1
UNSOLD
9
2
10.5
3
12.5
4
UNSOLD
12
5
15
View 4 Replies
View Related
Jan 23, 2010
Column A contains "Power","Instr","EHT", Column AJ contains "25M32", "25M35","25M39"and the a date is in column AE. I need to count how many time the date appears in AE if column the cell in A is "Power" and the cell in AJ is "25M32".
View 2 Replies
View Related
