Tracker
HOME    TRACKER    Excel

# Formula To Make Sure No Cells Are Duplicates

## A1 = 3 A2 = 3 A3 = 3 formula: =A1A2 gives us FALSE.... Why does =A1A2A3 give us TRUE What is the best formula to use to make sure no cells are duplicates?

Related Forum Messages:
Count Used Cells Formula. Make Reference Static
I have a problem using indirect in a counta function. Basically, I want to count the number of used cells in a range. However, there would be insertions to the rows (using the insert row) on top of the range.

my function is now =counta(indirect(" events database"!"A"&M22&":A"&M500)

However, this formula returns an error. What have I typed wrongly?

Make Now () = 06/29/2009 (make To Cells With Dates Equal Each Other)
I need to figure a way to make to cells with dates equal each other if the
day,month and year are the same but are placed into a cell at different times during the day. "Making Date Now () = (06/29/09) In another cell". Therefore, A1= Now() and E11 = 06/29/09

Make Formula Cells Empty Rather Than Empty Text
Is it possible to make a cell "really" blank/empty based on an If statement? For instance:

=if(a1>10,a1,"")

Has a value_if_false of "". But Excel interprets this a bit differently than a cell that never had anything typed into it.

So if you have a column full of this formula copied down, and hit <control+down arrow>, you will go straight to the bottom and skip over all rows. Whereas if you have a column with values and empty cells alternating and hit <control+down arrow>, you will only skip the empty cells and go to the next value. Excel treats the conditionally empty cells as if they have a value, when it comes to this type of navigation. This holds even if you copy and paste "Values" for the cells over the formulas.

Is there any way to tell Excel to make the cells truly empty?

Formula To Rank Without Duplicates
I am using the following formula to rank a list of data:

=SUMPRODUCT(--(\$A\$2:\$A\$140=A2),--(\$B\$2:\$B\$140=B2),--(M2

Formula For Find The Duplicates
I have excel sheets that will show a customer account number an ammount they need to pay and there full name and a date when the payment is expected, is there a formula that will find duplicates, if the same entry has been put in twice

Find Duplicates Formula..
I have two columns of email addresses and I want to make sure that between these two columns there are no duplicates.

Conditional Formatting Formula To See Duplicates
I know it can be done cause I've used it before but I can't seem to make it work this time. I want to use the conditional formatting to compare two ranges of cells within one worksheet I know it's something like =match(A2:C12) the two ranges are sitting one on top of the other. . .

Make A Formula Look For Something That IS NOT There?
Total newbie trying to use excel for work here. I have two problems that both need the use of "is NOT in another column". 1. I need to count the number of instances in a column that do NOT have the word "fly" in column G. The current formula is...

Prevent Duplicates In Formula Generating Same Names
The following attacted program is a name generator. It randomly generates names from the Roster sheet to the shuffle sheet (to be randomized) then displays it on the watchbill sheet. The problem I'm having is that the names seem to be repeating themselves before the entire list is used at least once from the roster sheet. I need all the names to generate at least once then have it fairly repeat the names (but not in the same columns on the watchbill sheet). Can this be done using the formulas I have? I've been trying to figure this out forever, its just a little beyond my level.

Concatenate Non-adjacent Cells Without Duplicates
I've been working on a spreadsheet that is used to track the application process of numerous clients. In doing this, I have a row at the top of each client that is used as a sumarry column. Below each cell in the top row, I have up to 30 non-adjacent cells that I'm trying to concatenate in the top cell separated by new lines. I'm also wanting to not display any duplicates, as there are up to only 5 steps that could be shown for each record. I found a UDF that will search an array and return only the unique values, but it accepts the data as an array, and it returns it as an array also. I seem to be having trouble passing the array from the sheet as well as formatting and returning the array with line breaks.

In the worksheet cell: ....

Adding Contents Of Cells For Duplicates
I have an employee sheet which has multiple hours rows for one employee, i want to select an employee ID and add corresponding hours and place both in a separate sheet. Here's my code so far.

Sub dup()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set NewSheet = Worksheets.Add
NewSheet.Name = "NEW"
Set NewSheet = Worksheets.Add
supcount = 2
countresources = 0
Do While Sheets("RS Nov 2009").Range("A" & supcount).Value ""
countresources = countresources + 1
supcount = supcount + 1................

Concatenate Cells And Delete Duplicates
I have an excel 2007 spreadsheet that lists
items on multiple rows for the same customer.

Each sheet will list anywhere from 25 to 100 individual customers.

I need to identify the duplicates, concatenate
the "Items" to a single cell on the first row, and
delete the duplicate rows. eg:

Cust #| Item
1 | A
1 | B
1 | C
2 | B
2 | E
3 | A
3 | C
3 | E

The result should look like:

Cust# | New Item
1 | A, B, C
2 | B, E
3 | A, C, E

Remove Duplicates And Delete Cells...
I am using the following code to grab installed software on a remote computer through a macro in Excel 2007. I don't have the entire code I'm using as the majority of it works, this section here though is where I'm having problems.

Formula To Make Every 50 Add 1
If i wanted to tack on something to the end of a formula so that it would make every 50 add 1 (example: 200 would convert to 204, because 200 divided by 50 is 4).

Make Concatenate A Formula
I have a DDE code that returns a stock symbol value. The code is as follows, where @ES# is the symbol:

=NeoTicker|q!'@ES#,last'

I am trying to create a cell so when I put in a symbol (H10), it automatically gives me the quote. So I split up the code and then concatenated it as follows:

G10: '=NeoTicker|q!'
H10: '@ES#

How Do You Make Text In To A Formula
If I have different equations in various cells without an = sign (text only) how do I make the cells into formulas without using the formula bar?

e.g Cell B1 has text in it A1+A2+A3 and we need cell B2 to be =A1+A2+A3

Make Certain Cells
I am creating a form and certain fields are required. Can I force the user
to input some value before they can save?

How To Make Cells
anyone know how to make cells number going down the row in "4's. I wish to have cells, A1,B1, C1, D1 to have a "1"in it, and the next four down (E1,F1,G1,H1)to have a "2", and the next four to have a "3",and so on.....

Link Two Cells Where If I Type Say Loubie In One It Duplicates In The Other
1. I need to generate 1, 2, 3 etc automatically when data is recorded in other cells on the same row

2. I need to link two cells where if I type say Loubie in one it duplicates in the other.

Hope these requests make sense I'm desperate for help tonight with this due to having to complete this project by the morning.

How To Search For Duplicates In A String Across A Range Of Cells
if I have a column of 5 cells (E1-E5) that contain a single character, how would I search a range of columns and rows (A1/5 - D1/5) with these?

So if E1-E5 each contain "1", i would like to return true when one (or more) of the A - D columns also contain "1" in each of its cells.

Columns A - D will contain a string of more than one character so it needs to search the string.

e.g.
E1 = 1
E2 = 1
E3 = 1
E4 = 1
E5 = 1 and
A1 = 123
A2 = 134
A3 = 142
A4 = 412
A5 = 213

I would like this to return true because the A cells do contain the values in the E cells.

I guess this is like looking for duplicates but with a search string twist!

Refer To Cells & Only List Duplicates Once
I am making an excel document that has a log of repeating data, one column of which includes names. At the bottom of the document, I am trying to make a small box that tallies all of the people within the document and the number of times they placed an order. What is the formula for when you want excel to find a word within the document? Right now I am using =CELL("CONTENTS",C6) etc. So all that does is repeat the word when it is used. But it duplicates the word in the box down below because it appears in the log numerous times.

Formula To Make A Box A Color
I need to be able to enter text in a box, and from that box the formula looks at the text to see if it is in within strings of text in any other box in the entire spreadsheet. If the text is there, the box needs to turn green (or any color). If not, it stays the same. The catch is I need the formula to be in a separate box, and the Entered text will be in a blank cell, so the user (who is excel illiterate) will not be confused by the odd formula.

Example:

B1, B2, C2, D4, and D6 will have the items: XXX, YYY, ZZZ
B3 and B4 will have only: YYY

In the "search box" the user will enter XXX

B1, B2, C2, D4, and D6 turn green, while B3 and B4 stay as normal.

If they enter YYY in the search box, all of them turn green.

Formula: Make Drop Down Boxes
Column A - I want to make drop down boxes #1-30 - which would represent quantity
Column B- That will be 30 rows of dollar amounts
Column C - To be able to calculate the quantity x dollar amount

I'm able to create the drop down boxes with the numbers, but say I choose the number 5 by the dollar amount 200.00 It won't calculate 5 x 200.00....

I've been trying the formula you would use to find an average, and i've tried the if statments, but my drop down box isn't being recognized.

Formula To Make Cell Blank Instead Of Zero
I am looking for a formula that will work with my current formula, and check that cells a1 and b1 contains information. If they do contain info then the original formula should carry on as usual and leave the answer in cell c1. I also need it so when no information has been entered in cells A1 and B1, C1 will be blank instead of containing a zero.

This is the formula I have at the moment:
=IF(SUM(A1=0,B1>0),B1-A1,"No Charge?")

A B C
10.1810.82

Make Numbers In Formula Stay The Same
I have a formula:
=INDEX('Part Info'!E1:E300,MATCH(1,('Part Info'!A1:A300=A1)*('Part Info'!B1:B300=B1),0))

I want the E1:E300 to stay E1:E300 when I drag it down but I want the A1 for example to change to A2, A3, A4 and etc.

Make This If Formula Return A Cell Value
Heres my current formula but its justreturning text i would like it to return the cells value

=IF((J2>K2);"value=J2";"(value=k2)")

How Can I Make A Persons Name Read A Formula
If I type the name Joe Bloggs How can I make whenever i type that name the computer knows to retreive a number or formula from a cell on another or same sheet.

How To Make + Symbol Variable In The Formula?
How to make + symbol variable in the below formula?

This formula is in G25 and symbol is in F25 has + & - symbols

=(NOW()-E\$592)+E614

Make A Part Number Formula?
I'll try to keep this brief. Our part #'s look like this. XP014567. Where the first pair of characters stand for a value(say XP=Bolt), the second pair stand for a value(01= Grade 2), and so on. What I would like to be able to do is type in the part numer and have the description automatically populate. Is there an easy way to do this? I'm running Excel 2003.

Can I Make A Formula That Will Highlight A Cell
I have a large spreadsheet with many pieces of equipment in it. There are a few piece that I would like to highlight should thier computer license go out of date, or even better 30 days prior. I have a Feild with the Expire dates but it is at the end of the spreadsheet not in plain sight. Is there a way to make my Equipment description column highlight when the date in the other column reaches the current date (or 30 Days before). I have cut and pasted the part of the spreadsheet into a new file to make it easier to read and hid all the none important feilds.

Make Only One Of Two Cells Selectable
If I put data into one of two cells I want it so that the other cell cannot be selected. I can't say I've struggled with this for days as I really don't know where to start

Make Cells Flash
I would like the VBA code and use instruction, which will make any cell flash in a predetermined range of cells when certain conditions are met i.e. if cell value greater than 10 or less than 100 - usual condition formatting rules.

Finding Duplicates :: Find Duplicate Cells In A Column
I have many rows of data. How can I find duplicate cells in a column?

Count The Sums Of Repetitive Cells And Then Remove The Duplicates?
I've got the following table (see attachment). In a column A there're the articles and some of them appear several times. In column B there's the quantity. The goal is to get in column C the sum of quantities in column B for the repetitive articles in column A and then delete the repetitive articles.

so the table should look like:
3RD125070442H 30
3RE071870493H 4
3RI100009624T 15

in excel 2007 I found only the function to remove the duplicate cells but I need to get the sums before removing them and have no clue how to that.

Psychologist Trying To Make Custom Formula For My Evaluations.
The test takers are shown three pictures. Under each picture is a number. So for example, let's say there is a plane, a car and a train presented for question #1. Underneath those pictures are the numbers 1, 2, and 3 respectively. Those numbers represent a tendency to a certain occupational field. After 55 questions, we can make a pretty good recommendation of where we think they should look for a job (without going into the specifics of the test).

There are 11 occupational fields. In excel, I make those fields A through K. In field L1 through L55 I put in their answers.

Let's say that in all 55 questions, for example, answering 1, 3, and 5 lead to vocational choice K, I want it to tally it up for me automatically. I don't want to have to count those by hand.

I just want to put in the patients answers, and have excel do the work. I tried using the "countif" function, but it doesn't look like you can add multiple conditions.

I need something that will be like this: add 1 each time (as in a tally) to the cell I choose, for the answers that I specify. So, ultimately, it will be tallying up the answers based on which categories that I correspond them to into the respective cells.

So to be redundant... Let's say that answering 3, 7, and 9 to questions 1, 2, and 3 pertain to cell B. After I put those in, I want cell B to have the number 3 in it.

Make A Cell's Values Become A Formula
I have on my Sheet named "Data" in Cell K4

=CONCATENATE(L16,O25,L18,O25,K20,S25,N22)

The values of that cell become a formula.

I try to make a macro that pastes 'Data'!K4 into 'Data'!L3 and then have that formula functioning in cell 'Sheet1'!A31.

The problem is when I try to make a macro to do this it will always paste the values that were recorded during the macro rather than the unique formula that is created via cell 'Data'!K4 at the time.

Is there any way to have A31 actively using the values that are created with 'Data'!K4? at all times?

How Do I Make And SUM(OFFSET Formula Dynamic
why this isn't working?

=SUM(OFFSET(A4,0,3,1,(LOOKUP(9.99999999999999E+307,4:4))))

Leadtime :: Make A Substract From A Today Formula
i got the data in this format and need to get it as date in order to make a substract from a today formula minus that date in that format, if it helps this is data imported from a website.

Make Part Of A Cell Bold Using A Formula??
I have a formula that combines the the text of various cells.

Is there a way to automatically make part of the cell Bold - so in this case I want the Cell B13 to appear in bold.

See formula below:

Make Formula Pick Up Filename From A Different Cell...
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!\$I\$32

There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.

Make A Formula Output A Blank Cell?
I want my formula to output a blank cell if the answer is 0. Ex. [B1=IF(A1=0, ???, A1)]. I want B1 = A1 if A1 is not 0. I want B1 to output a blank cell if A1 = 0.

Make Named Cells More Visible
I would like to loop through my workbook and distinguish (somehow) all of the cells that are named. I would like to do something to them so that I can easily glance through my workbook and find all ranges that aren't named. My biggest problem is that I would like whatever distinguishing I do to be reversable. Therefore, I can't just make all of the backgrounds of named ranges bright yellow, because then I would lose the original background color of my cells (they aren't all white).

The best option that I have thought of so far is inverting the fill color of each cell (this would be reversible). However, I'm not sure how to invert colors.

By the way, if the named ranges could be distinguished in some other way than by using background color, that would be fine too, as long as the changes are reversible.

Make Cells Shift Down Automatically
i need a button to copy the content of A8:C28 and paste in another section of the worksheet, i get the button to copy and paste the selected range, but when i copy and paste again it overides my current paste, i need it to paste my new selection underneath my previous paste.

Make All Blank Cells Blue?
I want to make all the cells that are outputted as blank by my formula a certain color to distinguish them from non blank cells. my current formula: [B1=IF(A1=0,"",A1)]. my guess would be to use an [AND] function but I do not know how to command a cell to change color without using the toolbar.

Sumproduct Formula: Count The Components Used To Make One Product
Is there a simpler way to to the calculations on the spreedsheet i have attached?

Basically I want to count up the components used to make one product

Make Formula Cell Appear Empty Until Data Is Entered
I'm creating a "universal-fluctuating" vendor inventory return worksheet for a auto parts store that consist of one criteria (cores, warranties, or N/R ) and will return one or two results of core cost and/or unit cost. This part of the task I have accomplished by using a drop down list for my criteria and my results will appear in two different columns using a Vlookup table. The problem is due to inventory fluctuating from cores and waranties on a month by month basis, vendor requiremnts differ for the number of units returned, and last make the boss happy on ink and papers supplies :D I was wondering if it is possible loop my code in a given column where it will move my code to the next row untill I reach a grand total?

Make A Number List And Combine 2 Cells
how do i make a number list. i want to start at 8000 then i want excel to auto generate up to 9000. it will take ages to sitt and punch 8000 8001 8002 ......

Next problem

how can i sett that cell A1 is connected with B1. so when i sort by number it follows. Example:

Click Option Button To Make Cells Bold
Public Sub OptionButton1_click()
Sheet1.Select
ActiveSheet.OLEObjects("OptionButton1").Interior.Font.Bold = True

End Sub

The problem i have is when i click on one of the option buttons it should change the cells ive highlighted to bold.

Copy And Paste (not Reading The Totals In Row 2 To Make The Formula Complete)
I am trying to get the formulas to work on Sheet 3, the rows formated to percentages correctly, but they are not reading the totals in row 2 to make the formula complete. I also want this to run more than one number, I would prefer a range for example value >= 1.29 so that Sheet 3 shows all items less than 1.29.

Join Numeric Cells To Make Date & Time
I am using this formula to combine Excel Dates & Times which is in seperate cells.
day Month Year Time

=B13&"/"&C13&"/"&D13&F13
and trying to convert to Numerical Time for further calculations.
I have tried many Format variations but nothing seems to work.

It will show the correct Numeric Time if the Formula: =NOW() is used in Cell K12
But it won't show the same from the imported data that is Text format
in Cell I 12

The sample W. Book shows a better explanation of the problem.
The end result is to show how much time has lapsed in Cell M 14 by converting Time to Numerical and simply do the sums. Works otherwise but not in this instance.
The reason to trigger various macros according to pre-set Time Limits Etc.