If Cells Are Duplicate, Then Return A Value
Sep 24, 2009Is there a formula I can use in B2 whereby if the value in A2 is identical to the value in any other cell in column A, then return the value "duplicate" in B2?
View 2 Replies
ADVERTISEMENT
Delete Duplicate Cells Or Rows With A Duplicate Cell
Nov 1, 2007I feel as though I have spent enough time searching the previous posts to ask this question.
I have a 4 column sheet, column B has many cells with identical data. I want to delete all the rows that that have duplicate data in column B.
COLUMN A= Car Makers
COLUMN B= Models of cars
COLUMN C= color
COLUMN D= owner
I want to end up with rows that each contain unique info in COLUMN B.
Pivot Table Chaos: Blank Cells Return 0, Empty Cells Return Nothing
Jun 25, 2009Hi, I desperately need help with a pivot-table and can't find anyone else who's ever had this problem...I have a very large healthcare data table which for simplicity I will describe as follows. In essence the first two columns respectively are (A) location and (B) month. Let's say the third column (C) is the number of cases of the disease kidpox treated in each health centre during that month. The fourth column (D) is the total number of cases of everything treated during that month in each clinic. The fifth column (E) is the proportion of cases of this disease over all consultations for each health centre and month, and the formula in E2 is .....
View 9 Replies View RelatedCompare Duplicate Items And Return A Value
Jan 19, 2010if there is a way to find duplicate items in a list and return a value, if a duplicate exists.
Basically I have 19 buildings each with a separate bill of quantities. I'm looking to compare and compile a comprehensive list. So that duplicate items in different buildings can be compared side by side.
I have attached a sample of what I am trying to do.
Return Offset Details For Duplicate Names
Feb 21, 2008I have a list of about 1000 names with 2 columns next to it. The first contains a list of different groups that the names belong to (there are 19 possible groups). The second contains a list of figures ranging between £10,000 and £500,000. I want a formula that will look for a name in the list and, when it finds the name, return the corresponding group and corresponding amount. My problem is that some names are in more than one group and therefore appear in column 1 more than once. I have tried to use the LOOKUP formula [eg looking for the GROUP =LOOKUP(Sheet1!B33, Sheet2!A:A, Sheet2!B:B)] but when a name appears more than once I cant get all the results - for example:
Mr Phil LittleGreen Group35,000
Mr Ed GrantRed Group20,000
Mr Robert SmithOrange Group10,000
Mr Robert SmithRed Group50,000
Mr Robert SmithGreen Group75,000
Mr James MorrisonGreen Group100,000
On my summary page (Sheet1) I would like to type in Mr Robert Smith (into cell Sheet1!B33) and in the rows below (obviously in 9 different cells) have the results appear but I can only ever get Mr Robert SmithGreen Group75,000 as a result. This is my first post so I hope I have obeyed all the rules and this is clear
Golf Scoring But Have Duplicate Scores - Need To Return Unique Name
Jul 28, 2014I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
Spreadsheet Attached : Golf Excel Score.xls‎
Return Row Number Of LAST Numeric Consecutive Duplicate In Column
Jul 5, 2006Using a Dynamic Named Range "Data", I would like a Formula to return the Row
Number of the "LAST" instance of a duplicate numeric value - repeating twice
consecutively in the same column.
Golf Scoring But Have Duplicate Scores - Return Unique Name
Jul 28, 2014I am attempting to put together a golf tournament spreadsheet. I have figured out how to review a list of numbers, find the lowest ten scores and make a list of those scores. Next to that I want to have excel input the name of the player that shot that score. The problem is, a couple of scores match so excel is only returning one of the golfers names. I am trying to have it return the other golfer who scored the same score.
Here is the formula I am using in Column N:
=SMALL($L$2:$L$21,1) This runs from 1 thru 10 for the 10 lowest scores.
Here is the formula I am using in Column O:
=INDEX($A$2:$A$21,MATCH(1,INDEX(($L$2:$L$21=$N2)*ISNA(MATCH($A$2:$A$21,N$2:N2,0)),0),0))
Here is the formula I am using in Column P: (gives same info as column O formula)
=INDEX($A$2:$A$21,MATCH(SMALL($L$2:$L$21,ROW(N1)),$L$2:$L$21,0))
Column A = golfers names
Column L = list of all golfers scores
Column N = lowest ten scores in order
Duplicate Cells
Feb 20, 2007I have a sheet where I input 8 columns of data from an email template which is sent to me throughout the day. I enter data in one row per email along the 8 columns. eg A1 to H1 then A2 to H2 etc.
Column C has the entry as a job number '123456'.
I need to be able to see if the same job number appears more than once.
In one week I have input 135 rows of data and have spotted 3 occasions where the same job number has been called in.
Any way I can set up a seperate sheet which will search and show duplicated rows from sheet 1.
Duplicate A Macro To Different Cells
Jan 15, 2009I have a worksheet change event that prevents a person from entering a positive number in 2 different cells. If they enter a number in one cell, the other locks, and vice versa. The code is below.
View 4 Replies View RelatedAdd Up Duplicate Cells And Then Delete
Jan 8, 2008I've searched the forums, and there are a lot of similar questions, but not quite what I'm looking for.
I have a large list of song titles, in one column. As an example, Song A is listed 4 times, Song B is 10 times, and Song C is 6 times.
Is it possible for Excel to go from this:
Song A
Song A
Song A
Song A
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song B
Song C
Song C
Song C
Song C
Song C
Song C
to this:
04-Song A
10-Song B
06-Song C
The numbers wouldn't necessarily have to be in the same cell, as long as I can sort the list from highest to lowest numbers while keeping the song titles matched with the correct number of instances.
Merging Duplicate Cells ..
Mar 25, 2008how would i run a macro to merge duplicate cells in a column
For example, i have lots of data in columns A, B and C
however some of the data in columns A and B is the same. Column C data never changes. so to make it easier to read, i would like to merge these cells (which are the same) together?
-ColumnA-
Cat
Cat
Cat
Dog
Dog
Dog
Dog
Mouse
Mouse
Mouse
Mouse
Mouse
would become.... (with all the other data the same)
-ColumnA-
Cat
Dog
Mouse
I dont want to lose any of the rows, as the data in column C is always different. I just want to merge the similar ones, so its easier to read.
Its a bit like this, but i cant even come close to getting this to work:
[url]
Find Duplicate Cells?
Aug 15, 2009I have two columns of numbers (2003), some of which match. Each column is of a different length and is arranged in a different order. I need to find all the number matches between the two columns. I can sort them ascending numerically but the columns are still disjointed with only occasional number pairs being readily visible.
View 9 Replies View RelatedHow Do I Remove Duplicate Cells
Jan 25, 2010I have two columns each of 5000 cells. Some cells of column A are duplicated in column B. The duplicates are not necessarily on the same rows. How do I delete the duplicates?
I've spent hours researching for an answer and I've tried Data>Filter>Advanced Filter many many times and it does not work. The two columns just sit there. Shouldn't this be a very easy thing to do in Excel?
Count Duplicate Cells Only Once
Aug 16, 2006I have attached a spreadsheet where I need the cells B2 to B8 to return the number of Y/P's (in column D) by Unit (in column E). There are duplicate entries in column D, however, if entry is duplicated, it is only to be counted once. Is there a formula to be able to do this? I don't know anything about VBA so would prefer a formula if possible.
View 5 Replies View RelatedMerge Duplicate Cells Into One
Jul 30, 2007I want to make a report wherein the macro has to merge cells based on duplicate cell values in column A and to put corresponding data in columns into one cell which we do manually by pressing ALT+Enter.
How this can be done through a macro or other tricks as I need it for a large report and need to use repeatedly.
A sample WB is attached which explains more.
Transposing Data For Duplicate Cells?
May 8, 2014I have a sheet of over 20,000 rows, each with 2 columns. Column A is numerical, Column B is name and address. There are duplicate entries in Column B, but Column A has unique entries.
I need to have 1 row per unique name and address with the numerical data from Column A relevant to that name and address either in one cell comma separated, or in cell C,D,E,F etc...
Duplicate Cells Not Exact Match
Jan 25, 2013I have text values in 2 different columns A and B. I can use the conditional formula to find and highlight duplicates but only if they are an exact match. How do I modify if they are not exact match but for example start with the same 3-4 text values?
View 1 Replies View RelatedCreate Duplicate Cells Quickly?
Jun 20, 2013How do I duplicate the values to say 4 times in a column?
So I have in a column,
dog
cat
bird
fish
I want to duplicate the above to look like this below,
dog
dog
dog
dog
cat
cat
cat
cat
bird
bird
bird
bird
fish
fish
fish
fish
I'll need to do this for 500 unique values 12 times each, so is there a formula to do this easily?
Highlighting Duplicate ALL Cells On A:A Column With Red
Jul 17, 2008I'm use with this macro to Highlight duplicate cells BUT the macro highlight only from the second duplicate
I need to highlight ALL duplicate cells in column A:A .
FUNCTION like " =COUNTIF($A$1:$A$11,A1)>1 " it's not good for me in this case, i need it with a fast macro
Highlight Cells If Duplicate Found On The Same Row
Aug 12, 2009how to even start this macro but i will like the macro to find duplicate with thin the same row and highlight it i have done with conditional formatting but the spreadsheet is about 3000 rows excel performance is super slow if anybody has a macro out there.
View 9 Replies View RelatedPopulating Cells When There Are Duplicate Conditions
Oct 22, 2008if the state, city and company name are the same, I need to make sure the address gets populated in the address column. I have included an attached sheet to clear this up.
For example,
Row 1 is Crazy Horse Saloon in Anchorage, AK and has an address and phone number
Row 2 is Crazy Horse Saloon in Anchorage, AK and does not have an address and phone number
I need it to be able to realize that the State, City, and Business all match and populate the missing information.
Clear Cells With Duplicate Values
Dec 10, 2013Checked for a while now for a match in my query but can't find one that matches. I have a column of data to which has multiple values in each cell separated by a comma I.e 1,1,12,37
I copy this column into a new sheet a:a then I'll run text to columns to separate the values into there own cells so the following columns now show this
a1 =1 b1=1 c1=12 etc
I'm now stuck as I need to clear the contents on the cells within range a1 to k1 that have duplicate values.
end result is a1=1 b1 is now blank c1 =12 etc
VBA To Duplicate Sheet And Clear 2 Cells
Jun 8, 2009I have a spreadsheet that I have created as a door schedule for faculty members at our institution of higher education. The intention is for them to fill-in their office hours and courses along with a few other bits of information. One sheet should have their teaching and office hours and one sheet should not.
These are the cells that should be cleared once the sheet has been duplicated (F30, F31, G30, G31). Once the sheet is duplicated and those two cells' contents has been deleted, both sheets should be printed. Ideally, all of this would be initiated by clicking a hyperlink or something like that.
Highlight Duplicate Cells Between 2 Columns
Feb 29, 2008I have a spreadsheet with columns A to W populated with data and 2470 rows
Column A has email addresses in it (2470 rows)
Column B has email addresses that are bad and are duplicated of those found in Column A there are only 345 of them in column B.
I would like to compare columns A and B and highlight the email addresses in column A that are also found in column B.
OR: If column A has a duplicate found in column B I would like the word delete to be inserted into column C of that row so I can review and then delete later.
Remove/Clear Duplicate Cells
Apr 15, 2008I have a workbook with 12 sheets, 1 for each month of the year. Each month has around 30,000 user IDs on it (this is a log of each time a user logs into our website). What I need to do is find out how many unique users have logged into our site for the whole year, or in other words, remove duplicate user ids accross the workbook. I realise that this will probably destroy my processor, so was wondering the best way to do it? I also have all this information in ONE spreadsheet if it would be easier? I just copied and pasted each month into a column (Jan is column A, feb in B, mar in C etc) I ran some code on this sheet to remove dupllicates from column A:E but this was taking AGES!
View 5 Replies View RelatedConcatenate Cells For Duplicate Rows
Aug 3, 2008I was trying to create this certain tool using excel which queries data from database. I was successful in obtaining the data I need and paste it to my spreadsheet, however, there are some requirements I cannot create a solution with. Below is a description of my Excel file (which is also attached):
- There are 9 columns on my spreadsheet
- Number of records per data refresh are indefinite, for information in database are constantly being updated
- Several cells on the first column have exactly the same information
The requirements I cannot do are:
- For all cells on first column that have similar values, their corresponding data on column 5 should be concatenated
- Delete rows that have same data for column 1 except the first record found, place concatenated data on column 5 of first record
Example:..................
Detecting Duplicate Cells Using Info From The List
Feb 5, 2014I have a sheet containing lots of information (lists of flight information to and from all countries). So entries are duplicated in the entire sheet. For example LITHUANIA, USA, CHINA... appears lots of times. I only need to highlight particular countries.
I would just use conditional formatting, but maybe there is a quicker way to do that?
So there is a list of 50 countries in one sheet and in another sheet I got all the info. I need those 50 countries from the list to be highlighted in the info sheet (sheet has many columns and rows).
Highlighting Duplicate Data In Column Across All Cells?
May 17, 2014I have a large compilation of data on an excel work sheet (I am using 2013). All cells relate to each other by means of dates, names, and other numeric values such as stock numbers, id numbers etc... In one column some stock numbers are repeated. I would like to know if there is a way to set up the program on this worksheet to highlight all repeating stock numbers, not only in the column but across the cells as well, in lieu of doing so manually.
View 1 Replies View RelatedRemoving Duplicate Words In Individual Cells?
Aug 8, 2014I have a column that is filled with various car brands that a dealership might sell. The data in the cell may look like CHRYSLER; CHRYSLER; CHRYSLER which I would want to just say CHRYSLER. If the cell says CHRYSLER; FORD; CHRYSLER; FORD I want it to just say CHRYSLER; FORD.
View 1 Replies View Related