I m trying to show duplicate entries and what forumla to use.
Have A1 through to A5000 listed with diffrent phone numbers, I have been supplied with new numbers which i have placed in B1 (Not quite 5000 worth) But there are duplictes in B1.
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).
I have a movie list. column A is the number location i have my movie stored, and column b is the actual movie name. I backup my movies so that my kids wont destroy them. The list is around 1000. I have duplicate backups, i want to have a list of just the duplicate movie names and there locations.
View 12 Replies View RelatedI have a list on column a
i.e
Name
Bob
Jones
Michael
Smith
Bob
Jones
What I want is to only show duplicates, else "". One way is to sort column then use =exact function and deterimine true/false. however is there an easier way, hence withouth having to sort? i just want it to show only duplicate names in cell's.
Have Column of cells 1500 total B4, B5 etc on sheet 2.
Wish to identify if any of the cells which have duplicate numbers to previous cells.
Going from top to bottom. (Cells have 10 digit numbers)
Identify duplicates in cells A4, A5 etc. sheet 2 by the word "duplicate"
I have a list of line items and I want to be able to see how many duplicates in Column A and B.
Column A has text field and column B is a numeric field
I thought I could use INDEX MATCH but I'm unsure.
I am filtering data using listbox (userform).
I want listbox show only names(without duplicates).
i have duplicate cell entries occuring. I have a column of about 8000 entries (Column B) and would like to have a cell at the top of my spreadsheet that displays where the first duplicate resides (Row No. will suffice).
At present i have a conditional format on dupllicates, but is is a big task to scroll down through all the data looking for them.
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
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.
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I'm trying to Count duplicate text on sheet 2 and populate the number of times repeated on sheet 1.
On sheet 1 I have A3:A128 and would like the number of times these respective cost centers are repeated in Sheet 2 to populate Column B of Sheet 1.
File attached.
Not sure which formula to use. I tried CountIF but didn't work with 2 sheets.
I have a column of data held in column B.
I am required to show the following.
If there is data in the cell then it is to be left. If there is no data in the cell then I would like to show the value 0.
I have tried using a circular reference, using the formula =IF(ISBLANK(B1),0,B1) and other similar formulas but they dont work as the formula overwrites the data in it.
do I need a macro?, or conditional formatting?
i use function countif "aa" in range A2:F2 COUNTIF(A2:E2,"aa")how i can do, if i do not need 0 to show when it not found "aa". i want it show blank
View 4 Replies View RelatedI have 2 different formulas that I need changed in a similar way.
The first formula is for cell AV11:
=SUM(BI11,BP11,BW11,CD11,CK11,CR11,CY11,DF11,DM11,DT11,EA11)+10
Every cell starts off blank.
What I need is for cell AV11 to always start off blank until data is entered into one of the other cells. The problem is that since the sum always needs to be +10 only when data is entered in the other cells, I don't know how to keep 10 from showing in cell AV11 when no data is typed in the other cells.
The other formula is for cell CO39:
=(CU8)+3
I pretty much need the same thing. If no data is entered in cell CU8, then I do not want cell CO39 to show the 3.
i m working on a mechanical project , I need to do some modifications to my XLS file that i aint familair with.
http://aycu36.webshots.com/image/348...0572870_rs.jpg
The data is copied daily to load and duplicates are removed and new entries are copied to list sheet, by searching on the concatenation.
The problem i have is that i also want to add to the list, the row lines from the load sheet if the date changes in column G. Not really sure how yo approach this. I have attached sample data.
How do I do a sum without duplicates. In column A I have a list of ID, many that repeat. how to I sum with without the duplicates?
View 4 Replies View RelatedI have a bunch of phone numbers in a row and I want to eliminate duplicates of 10 for a given number. For example - 5556839898 may appear 15 times, I want to delete the 5 extra instances of that number, leaving me with a maximum of 10 duplicates. This is for an SMS voting application where the entries where supposed to be limited to 10x.
View 9 Replies View RelatedI have 2 columns, one has UPC which are sometime duplicated but the number associated with it in the next column could be the same or different. I need to identify which duplicate UPCs have different numbers, if they are the same I don't really care.
01030088834524need to identify
01030088834550
01030093621315
01030093621315
01111523220043
01111523220043
01116101270212
01116101270212don't care
0111610141268need to identify
0111610141269
I am trying to organize some meteorological data for a project and I ran into a wall, basically I have 3 columns in one there is the date, in one the hour and in the third one the temperature the issue is that in the hour section i have the hour 12:00 that repeats its self , and this goes for the hole year , pretty much every day i have the hour 12:00 that repeats twice , so select for every Monday , Tuesday etc only certain hours.
View 5 Replies View RelatedI have a combobox on one of my worksheet, it is taking values from another worksheet and it is appearing that the values are repeated/duplicated.
VBA code to just copy the value once from the range.
I'm currently working with the formula
=COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000")
However I need the formula to count only one for the n possible duplicates in the Data[Name] column.
I've attached a test excel to this post. Column C contains several identical email addresses. I need ALL the rows containing the duplicate data removed, not leaving one remaining as Remove Duplicate Values normally does.
I'm going to be doing this with many Excels all with differing amounts of data surrounding column C. I need a reliable way to achieve this, regardless of how many extra data columns exist. If there is a duplicate of data in column C, all rows containing that data need to go, regardless of what other data may also be present in those rows.
I have a sheet with numbers in descending orders with duplicate in one row and their respective value in 22nd row. I want to find the2nd occurrence of that value. (max. occurrence2 is 2)
Foe example
5----4-----3-----3-----2-----2-----1
A----B-----0---- C-----0-----D-----E
So if i lockup value "3" C should be returned (not 0) and for 2 "D"
see attached also
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)
I have a table in excel that contains many rows, each row being a product. Each row has a product ID, and should be unique, but there are multiple instances of products in the table, some that are duplicate and some that contain different info (product notes, description, etc).
What I would like to do is group the products by product ID, so that I can show the different occurances of the product within each product ID, so that we can weed out the unique values within the duplicate products by ID. Is there any easy way to do that?
excelexample.jpg
I have a long table with duplicating id numbers but different values which i need to match. Basically the initial state is like this:
ID1 | example 1 |
ID2 | example 2 |
ID1 | example 3 |
and what i need is this:
ID1 | example 1 | example 3|
ID2 | example 2 |
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies View RelatedColumn A has approximately 50,000 rows with unique property parcel numbers. Column B has numbers that represent an elevation point on the parcel. The parcels are not level. The elevation of a parcel varies depending where one measures. So,...I have many rows with the same parcel number in column A but the adjacent column B shows a different elevation. Is there any way to:
1) remove all of the duplicate column A parcel number rows so I have just one column A parcel number row.
2) get the average of all the column B elevations to show up on the same one row?
I should only have approximately 15,000 rows if I could get rid of the duplicates and get the average elevation. I have version 2007 but I have to share with others who have version 2003. I don't know if that makes a difference but I thought I should mention it