I have to type a an ID in column D of a worksheet called “restricted funds”, I have 13 sheets in that workbook and each sheet has in column D some IDs, I can not type a duplicate ID, in the that sheet. What is the best way to avoid no duplications. IDs are simply numbers.
I have a column where i have to put document numbers, Column A, i want to avoid accidently capturing something twice, so the only thing thats unique is the document number. Can i get an error if i try to enter the same number twice?
View 9 Replies View RelatedA
B
C
D
E
F
1
ID
Code
Total IDs with Code 60
Total IDs with Code 345
Total IDs with Both Codes
[Code] .......
In column “A”, there are 14,000 ID Numbers. In column “B”, there is either a 60 or a 345 code beside the adjacent ID number in column “A”. There are duplicate IDs, some with same code and some that have both the 60 and 345 codes. I need to count ID numbers with code 60, code 345, and the IDs with both codes without duplication.
On Worksheet Data 1 Column I there is a column of data with "some" duplicated text entries.
The data is sorted so that all of the duplications appear in a blocks, i.e.
This is the first
This is the first
This is the first
This is the second
This is the second
This is the third
This is the third
This is the third
This is the third
This is the fourth
This is the fifth
I want to -
Starting in Worksheet Data1 cell I4
Copy A4 to Worksheet Data2 cell A4
Then go back to Worksheet Data1 cell I4 drop down to cell I5 and if the data is the same as the data just copied drop down a cell and keep going until it reaches a cell with different non duplicated data.
Then Copy that cell to Worksheet Data2 cell A5
So that I end up with a list of data with no duplications.
I have a spreadsheet with 4 columns.
I would like to Compare Column A with Column B, if Column A contains text or numbers anywhere in Column B, then remove the corresponding rows not just in Column B, but Column C and D as well for that row.
I have a master sheet that has 3500ish names on it, and another sheet that I'll need to drop in a list of about 1000 names. What I need on the master is a way to check the dropped in data, find duplicated names, and flag them up.
Now, I believe what I did last time was have a true/false method of telling me if they're on both sheets, then use an IF formula to instead make Trues into "yes" and Falses into "no". However, I can't for the life of me figure out how I had it previously checking both sheets and confirming/denying if they're on both sheets or only appear once.
I have two workbooks (source and target) I want to copy column A in the source workbook to column A in the target workbook and remove duplication in the target workbook. If any.
[Code] .....
Found this in another forum it works for the copy but not the removal of duplication.
What is the line I need to add ?
Macro to organise data, i have provided an example below, macro should only pick up unique codes and ignore any duplications.
From thisTo thisHiport IDIM IDHiport IDIM IDIM IDBHPBHP999AUDBHPBHP999AUDAUDBHPNNNNTBHPBHP999AUDTLSTLSWWWWPCCCTLS0000BHPAUDBHPNNNNTTLSTLSWWWWPTLSCCCTLS0000TLSCCCTLS0000
We have a company and need to automating workflow.
The Master sheet contains incoming mail details by customer. Each employee is assigned a set number of customers to respond to. We want the information on the Master sheet to filter to a specific employee assigned sheet. On the employee assigned sheet, once filtered, they are to provide updates in column E
Date
Reference no.
Name
Employee ID
16-Feb-12
S/S/1
[Code] ........
We need a macro that can filter customers' details to the specific employee assigned sheet based on the employee ID in the master sheet. For example, all customers assigned to DW (i.e with the 'DW' employee ID) filters into a sheet called 'DW'. Please note that the Master sheet is a continuous log updated daily. This macro must not duplicate information previously filtered once the employee enters a status update in column E.
I'm havin' a chart for interest, instalments and so on;
Interest [X%] Loan [Z crowns/dollars]
Loantime [Y years] Instalment [formula, which is "=Z/Y/12"]for each month.
Year, Month, Loan(left), Instalment, Total payment
1, Januari, =Z, =Instalment (constant), =Interest+Instalment
Month, Loan(left), Interest
Februari, =above-Instalment, =X%*Loan(left)/12 (rent is divided per year),
Instalment, Total payment
Constant, =rent+Instalment
and so on... as long as you want ^^ (it's quite messy, but try :D)
The question is as follows "What would a loan of 900'000kr with 9% interest rate totally cost over 30 years?"
[use "IF" to avoid getting anything written after the loan expires]
The question itself can be easily solved by just dragging the formulas down to 30 years and fill in the % and loan etc. But what I do not understand is the "IF" question. I'm supposed to use the IF function to instantly show the cost of the loan (total rent payment) after the 30 years? That's how I understood it. In that case... how do I do that? ^^
I know how to avoid getting the #N/A in a vlookup, but I have found situations where I have data matches and still get #N/A. For instance, I am looking up an item number (which contains numbers and letters) on one tab to find corresponding info on another tab. I've verified that the item number is indeed in the lookup range but I still get the #N/A. If I copy and paste the item number over the same item number in the lookup range then it works. However, I don't want to do this for each item number.
=AVERAGE(IF(A:A=B6,IF(C:C>0,C:C)))
"A" is a duplicated rep field and "B" is unique rep IDs and "C" is a sum of product types purchased. I'm averaging the number of product types sold by each rep.
I need to modify it to remove the #DIV/0! error it generates. It calculates just fine by row (with the error on some rows), but when I use that calculated field as an element in another formula, the #DIV/0! blows up the second formula. I tried a second IF function tied to the sales field "D" but I don't think I nested it right.
I have a set of data containing "N/A" in some cells, and when I try to plot the chart using stacked lines chart type, the chart is completely off. if the "N/A" are causing this problem and how to fix this.
View 5 Replies View RelatedTransferring Multiple Data from separate worksheet without duplicating"
As you can see i am looking for information in column d (Letter) and getting the result to show from column B (number) using the formula in Column G (result 1).
The problem i am having is that if i try to copy or drag the formula down into Result 2 i just get the same answer.
Is there any way for the next cell down to ignore the previous result?
I've attached an example of what I am trying to achieve and an explanation as well. I'm trying to avoid the use of a macro as I don't understand them and I will need to modify the sheet later on which could cause problems. However, if a macro is the only solution, then I will have to make due with one.
View 5 Replies View RelatedI would like to avoid user to SAVE or SAVE AS file. In my macro I used code to disable SAVE and SAVE AS from FILE menu. But how to avoid to save file if user use CTRL+S command to save file?
View 7 Replies View RelatedI have a formulas in Sheet1 linked to other sheets, but I have only formula and there are no sheets ( eg: in sheet 1 A1 =Data!A1) where as there is no sheet called Data. I have a macro to import this sheets from a different file.
The problem I am facing is, after importing the sheets, the formula still shows #REF but if I do double click the cell or press F2 and then "Enter" the value appears. I have about 1200 formulas in this sheets.
the data is as follows:
Data  ABC1Timestampname 21/31/2012 11:07:13abc931/31/2012 11:07:50def 41/31/2012 11:10:03a 51/31/2012 11:17:12b 61/31/2012 11:17:14a 71/31/2012 11:17:15a 81/31/2012 11:17:16a 91/31/2012 12:39:41cv 101/31/2012 13:04:00k 112/1/2012 7:26:53k 122/1/2012 7:26:56dg 132/1/2012 7:41:05Gw 142/1/2012 8:01:19cg 152/1/2012 9:32:14CB 162/1/2012 9:32:15CB 172/1/2012 9:32:15CB 182/1/2012 9:32:17CB 192/1/2012 9:32:26kw 202/1/2012 10:24:54jw Spreadsheet FormulasCellFormulaC2=SUMPRODUCT(--(MONTH(A2:A20)=1)*(YEAR(A2:A20)=2012))
if you see the cell a6 to a8 happened in millisecond same person has punched i want to count it as one only
I have been reading and studying up on everything I can get my hands on with excel, and today I read the golden rules sticky. One of them confused me, so I need to understand, so I can follow it. "Don't Nest 2 Lookups To Avoid #N/A! Allow it to Occur and Reference Like: =IF(ISNA(A1),0,A1) and Hide the Lookup Column." I need cells to be empty instead of returning #N/A! So I nested using the ISNA. I do not understand the formula example above and hiding the lookup column. Here is the formula I have in one of the cells: How do I change it, so it returns an empty value instead of #n/a without nesting the lookup =IF(ISNA(IF($I$8="Student",((VLOOKUP($J$8,'Exit Card Data.xls'!Card1_1,2,FALSE)))/D33,IF($I$8="Class",(HLOOKUP($J$8,'Exit Card Data.xls'!Card1_1,28,FALSE))))),"",(IF($I$8="Student",((VLOOKUP($J$8,'Exit Card Data.xls'!Card1_1,2,FALSE)))/D33,IF($I$8="Class",(HLOOKUP($J$8,'Exit Card Data.xls'!Card1_1,28,FALSE))))))
Also I really need the cell to be empty if nothing has been entered in I8 or J8, but right now it returns FALSE. For what I am doing, it is important that the cell is empty if no data can be found.
in column E i need to add a formula:
=($C2 - $B2)/$C2
And i would be extending it to the rows below.
Now, obviously, if C2 contains a 0, it gives a divide by 0 error. How to modify the formula which makes it valid only if C2!=0(not equal to 0)
When I click a button on a Sheet, Certain data is copied from another sheet and paste in active sheet. While this action is taken place, whole sheet is refreshed and the image and a data is blinking for a second.
Do we have an option to avoid that blinking ?
How can I avoid letters in a cell (read only numbers)
I have an excel spread sheet set up with a button I created (a colored rectangle with text) that has been asigned a macro. When I print this spread sheet I do not want the 'button' to print on the page.
View 2 Replies View RelatedI have a formula that converts the interval between two times into a whole number of minutes:
View 3 Replies View RelatedI have attached a simple example worksheet showing what I am trying to do. I have a contract start date, a contract finish date, a notice period and a standard review period. To get a date when I should start my contract review I do the following: (finish date-notice period)- review period.
This is fine when the finish date is populated but if it isn't it results in a negative number. What I want to achieve is if there is not a finish date then leave the review date cell blank.
I would like to design a button (with macros),so that users of my excel page do not need to press CONTROL+SHIFT+F1+ALT,which needs to be pressed on my firm to get the new data from a central server.
View 3 Replies View RelatedI have a userform that has a control button that opens another userform that contains a listbox with a range of data. I want to click on an item and press a control button to add it to a textbox on my first userform. I do this via:
View 2 Replies View RelatedI am programming VBA for Excel and I have a problem with automatic popup messages. I would like to answer Excel's request "Selection contains more than one value. If cells are merged, only the first entry will remain" with OK. The cells are sorted, so they contain the same value, hence I want to merge them. Of course I could work around and delete every entry except for the first before merging, but I would like to know, how to answer this popup automatically, so that the user does not get to see it.
View 7 Replies View RelatedI have the following macro
Sub UpdataMainTables()
'
' UpdataMainTables Macro
'
'
Sheets(" Table ").Select
Sheets("Main Data").Range("B2:B1048576").AdvancedFilter Action:=xlFilterCopy _
, CopyToRange:=Range("B2"), Unique:=True
[Code] ......
I have 2 questions
First one about avoid the movement of this macro, as you can see it starts at one sheet but the job is in other sheet when i run it it goes to the second sheet to do the job,, how i can avoid showing the movement.
Second question about th last part of the macro,, it most replace words with no thing,, if these words are not available it givs me alert,, how i can eliminate this alert.
I have an excel column that includes many numbers. I want to add them to the listbox. But there are same numbers in the list. What I want to do is to add each number to the listbox only once. Example:
This is what I have__________________This is what I want
622768____________________________622768
622768____________________________631215
622768____________________________635054
631215____________________________647461
631215____________________________681216
635054
[Code]....