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"
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.
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.
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 love this forum, and am usually able to find the help I need without bothering anyone However this one has me stumped and I wonder if anyone can help. It feels like it should be a fairly simple solution, but they can often be the ones that are most eluding LOL!
I have two columns; in column A are incoming telephone numbers and in column B are the date and time the calls were made. (I've put a few hashes in column A just to maintain confidentiality of the numbers, but in reality the cell is formatted as text in order to maintain the leading zero, and entries will follow the format 01234567890)
A sample would look like this:
0##6270####01-Mar-2009 00:01:440##6271####01-Mar-2009 00:03:020##6271####01-Mar-2009 00:03:040##6272####01-Mar-2009 00:16:330##6273####01-Mar-2009 00:30:490##6274####01-Mar-2009 00:55:470##6274####01-Mar-2009 01:06:170##6274####01-Mar-2009 01:07:420##6275####01-Mar-2009 01:08:360##6275####01-Mar-2009 01:11:410##6276####01-Mar-2009 01:13:45
Some numbers only call in once, I need to identify them as only called once.
Some numbers call twice, if they do I need to be able to show time it took between call 1 and call 2.
Some numbers call more than twice. For each successive call I need to be able to show the time since the previous call.
In my mind, the results table would need to look something like this:
NumberTime of callTime between
1st and 2nd call
Time between
2nd and 3rd call
Time between
3rd and 4th call
0##6270####01-Mar-2009 00:01:44Only called once0##6271####01-Mar-2009 00:03:0200:00:020##6272####01-Mar-2009 00:16:33Only called once0##6273####01-Mar-2009 00:30:49Only called once0##6274####01-Mar-2009 00:55:4700:10:3000:01:250##6275####01-Mar-2009 01:08:3600:03:050##6276####01-Mar-2009 01:13:45Only called once
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
I'm looking to populate my Helper Column with all duplicates from my Data Column. Please note that my helper column needs all instances of a duplicate.
Data Column
Company ABC
Business DEF
Corporation GHI
Business DEF
Company ABC
Business DEF
Helper Column
Business DEF
Company ABC
Business DEF
The formula needs to be dynamic and it will need to be able to be copied down.
I need to remove the duplicates under column B for each item under column A and I can't seem to figure out how to do it.
I'm using Excel 2007; I would prefer a VBA solution, as I have to do this on a monthly basis. However, if a formula is the best way to go, then that's just fine. Either way, I'm preparing the data for a pivot table. I am unable to post the actual document in the interest of data protection.
Essentially, I'd like to turn this-
_ A B
1 Paris Bill
2 Paris Bill
3 Paris Mike
4 Paris Derek
5 Paris Derek
6 London Mike
7 London Bill
8 London Mike
9 London Bill
10 London Derek
11 Tokyo Derek
12 Tokyo Derek
13 Tokyo Derek
into this-
_ A B
1 Paris Bill
2 Paris Mike
3 Paris Derek
4 London Mike
5 London Bill
6 London Derek
7 Tokyo Derek
In other words, each city should have only one instance of any name that accompanies it; not all names will accompany each city.
My issue is that I have 1200+ addresses to make more readable - the first column is the street name, the second is the post code(s) relative to the street - what Ideally I'd like to achieve is one row per street, the street name followed by the post codes... i.e. take this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG
Abbey SquareRG1 3BE
Abbey SquareRG1 3BQ
Abbey SquareRG1 3FB
Abbey StreetRG1 3AN
Abbey StreetRG1 3BA
Abbey StreetRG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP
Admirals CourtRG1 6SR
Admirals CourtRG1 6SS
Admirals CourtRG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
to this...
A33 Relief RoadRG2 0RR
Abbey SquareRG1 3AG, RG1 3BE, RG1 3BQRG1 3FB
Abbey StreetRG1 3AN, RG1 3BA, RG1 3BD
Abbots WalkRG1 3HW
Aberford CloseRG30 2NX
Admirals CourtRG1 6SP, RG1 6SR, RG1 6SS, RG1 6SW
Ainsdale CrescentRG30 3NG
Alan PlaceRG30 3BW
Albany RoadRG30 2UL
i have a wordlist which i want to enhance. The wordlist contains of three columns A, B and C. Column A contains of source words, column B of grammtical specifications and column C contain of meanings for source words. The sheet containing this list is named "original" and it looks for example like
View 14 Replies View RelatedI am having a small problem with microsoft excel 2007.the problem is that: my excel 2007 cannot display tooltip of the column width when I keep and drag left mouse
Show tooltip of the column width as attached file.
I also tried many ways to find settings of advance menu. But cannot.
Pic.jpg
I am trying to figure how to show in one pivot table a current column and a proposed column. I have 15k rows of data. My data columns are employee, month, task, hours, proposed month. I can get a table that has months as columns and tasks as rows with sum of hours. What I would like to do is incorporate the proposed month, so that it shows hours in the months by current and proposed. That way my result would be January current, January proposed columns etc. I can change the propsed months by formulae so I want to play with the proposed task month the refresh the pivot table to see the results.
View 2 Replies View RelatedI have a spreadsheet with a column showing dates (dd/mm/yyyy). I need the column next to it to display the month only (Jan,Feb etc).
How do I create a column to show the month only using the date column as a reference.
SIZE-help.xlsx
I need to show the sizes to shows from column A to show in column b.
I've got a spreadsheet with a column of company names. I need to search the entire column and retrieve the rows of any duplicate names.
I tried using :
cRow = ActiveSheet.Columns("D").Find(coName).row
However, it only ever retrieved the first instance of the name and not all the duplicates.
I have a data set of property sales that represent properties sold twice over a period of time (each address is represented twice in one column but they are 2 different transactions and the remainder of the row data varies). I am looking for a formula or method to remove the duplicates, but I want to remove the duplicate that sold for less.
The entire row is not a duplicate, only the address. There is another column with sale price and I would want it to keep each address of the 2 (or sometimes 3) that is highest. I intend to use a pivot table to analyze and realized that the data is skewed because everything is showing up at least twice.
I have a worksheet which basically tracks time. the time is reported in Column C. In that row in Column E, there is a validation list with about 6 different categories in it. On the side of this "table" I have a list of all the categories and I want a value to be next to it that reports the sum of time (C) for each category (E).
So for the "Routing" category, I would want the value to be the sum of just data on the timesheet that have "routing" in Column E.
I have a column with text values, I would like to remove all the duplicates. How can I do that??
View 4 Replies View RelatedI 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.
how to Delete Duplicates in Column D with a macro. It would search all of Column D and look for Duplicates and if it finds an exact value match it will delete one of the rows containing the duplicates. This is running in between a much longer macro which is the reason why I can't do it with conditional formatting.
View 12 Replies View RelatedI'm trying to create a macro that will Delete Rows of Duplicates found in Column D. It will start with the first row it finds that is yellow and check to see if that Column D matches any other yellow Column D's. If it does then the row the match is in gets deleted. Right now it searches in between the yellow rows, but for this portion I'm looking for it to use the yellow rows only. I included a file that has the examples on sheet1 and the expected outcome on sheet2.
View 6 Replies View Relatedhow can i remove duplictes from a colum. For example:
Column C:
A;B;C;A;D;E;A;G;B *(A is three times here and B two times!)
F;G;H;I;F*(F is here two times)
I would like to have the duplicates removed from each cell in column C so that each member is unique in the cell. The example would then look like:
Column C: (after removing the dupplicates A and B in first cell and F in second cell)
A;B;C;D;E;G
F;G;H;I
Hi, the code in the codebox under here, which I wrote, is supposed to delete all duplicate records in the column. Which column to uniquify is stated in cell Skin!B17. I searched the internet round, and there are different solutions everywhere. I have been unable though to find the right one - so I thought, why not ask here. If you have a solution, I'd be happy to hear it ...
View 14 Replies View RelatedI've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:
=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(F2:F1079))
The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.
So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?
I Have Data in my excel where i need to findout the Duplicates as well as unique if there are duplicates in the given column.
For eg In Column "M" if there are Five "ABC" so i need all the five ABC as a Duplicates and not only 4 ABC as Duplicates and one as Unique.
ABC
B
C
ABC
ABC
ABC
ABC
D
E
I'm trying to find duplicates in one particular column. I'm using this formula =if(countif($A$1:$A:$300,A1)=1,FALSE,COUNTIF($A1:A1,A1)=1) in the conditioning format tool but here's my problem:
I'm noticing that some of the cell values have spaces after the entries so the formula is not picking it up as a duplicate. Is there a quick way to remove those unwanted spaces after each entry?