I am using old macro code in excel written several years ago. I am trying to educate myself to update and improve the macros I am using but not making much progress. This macro is used to record data from a DDE link to a Data Acquisition System. The D-AQ system sends the data points to r5c3:r5c9 typically. The macro then records the data points in those cells to the next row in sequence on a specific time interval. However, the macro is recording duplicate entries for each set of data points. Sometimes just two, but has been as many as four duplicates. I have a remove duplicates macro to run, after the fact, but preventing them from occurring in the first place is the goal. I included the application.wait method at 10 second intervals after the application .OnTime method, but it isn't preventing the duplicates.
Dim delta_time As Date
Dim Count As Integer
Dim n_rows As Integer
Dim start_row As Integer
Dim col_mov As Integer
Sub initialize_button_set()
start_row = 7
Count = 0
n_rows = Worksheets("sheet1").Cells(1, 2).Value
delta_time = Worksheets("sheet1").Cells(2, 2).Value
Application.OnTime Now + delta_time, "get_data"
Rem Worksheets("sheet1").Buttons.Add 275, 0, 72, 36
Worksheets("sheet1").Buttons.Caption = "STOP"
Worksheets("sheet1").Buttons.OnAction = "Halt"
End Sub...........................
Sales for Shook, Emily(131520) Total Sales $160.18 Sales for Clayton, Casandra(131008) Total Sales NO SALES FOR THE WEEK Sales for Jofery, Rebecca(126310) Total SalesNO SALES FOR THE WEEK Sales for Brea, Olga(124257) Total Sales$140.21 Sales for Mastro, Rachel(131521) Total SalesNO SALES FOR THE WEEK Sales for Rodriguez, Blanca(111550) Total Sales $154.33 Sales for Katz, Mary Isabel(126840) Total Sales$269.87 Sales for Kitson, Mackenzie(130466) Total SalesNO SALES FOR THE WEEK Sales for Blagniceanu, Adriana(126518) Total Sales$161.25 Sales for Best, Jessica(128350) Total SalesNO SALES FOR THE WEEK Sales for Sanchez, Vanessa(126437) Total SalesNO SALES FOR THE WEEK
The Sales For (Last Name, First Name) was a concatenate I created to give everyone a unique identifier. Than I used a vlookup based on the the sales report to get there total sales
=IFERROR(VLOOKUP(D7,sales2,5,FALSE),"NO SALES FOR THE WEEK")
My problem is say for example: Sales for Sanchez, Vanessa shows up twice on the report stating she has total sales for $40 and $60 how can I get excel to calculate that within my VLOOKUP Function. If there a formula I can use to combine both values. I was think SUMIF is the most likely answer but I'm having problems.
I have a spreadsheet that needs to have a validation drop down list in three cells in every row (D, E, F). There are three items in the list, say, "One", "Two" and "Three".
My only problem is that there can only be one entry of "One" in each row.
I have attached a before and after image of what I am looking to accomplish.
In the before image, you can see that there are 3 rows of data
- a header
- a repeating model number (in column F) with accompanying data (values in columns G - J are the items of interest)
In the after image, you can see that I took the 3 rows of data and turned it into simply two rows of data
- a header
- the repeating model number
What I did however in the after image, as can be seen, is I took the data that appeared on the repeating row (Row 3 from the Before image), and included the PRICE, COST, BEGIN, and END values in new columns on row 2.
The model number is the same of course, which is why I did it that way.
If the model numbers were different, I would have simply left it alone, as-is.
Is there a way to accomplish this via some functions or maybe even a VBA script?
By the way, if we have to dump all of this onto a new sheet, then so be it.
My data consist of multiple entries of same id(s) at various point of time. I need to extract the latest data of a particular ID. I was using vlookup but it extract the first entry of that ID but not the latest. and the data is entered into the database using userform and VBA macros.
a way to separate out data that has been all entered into a single column
code .....
The above information is a sample of the data. The data has been entered into 1 column, column A.
I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.
I currently have about 3000 rows of data, much of it being duplicate data. Is there a way to have it search for duplicate data, and have it delete BOTH sets of duplicate data? For example, say this is what is in my spreadsheet:....
I would want it to delete both John Smith records and both Ted Nugent records, leaving Bob Bobbert's and Michael Jordan's records.
I'd like to minipulate a single value from 2 different points - for example: I have a weight of 8lbs. on tab B and would like to change that weight using a spin control and also be able to change the same weight of 8lbs. using a spin control on TabD.
I've created a single worksheet that is essentially a "dashboard" type of report that displays data for a single employee/person. This worksheet (let's assume it's my "template") contains formulae that is driven by a single lookup cell (in this case, the employee's ID, in cell, for example, A1).
This "dashboard" report (for just one individual) needs to be duplicated for the entire employee population (let's say ~100 people); this employee population is stored as a list in a separate worksheet, in a single column.
Is there a vba solution that will copy my template and populate the cell A1 (of each duplicated template worksheet) with the the employee IDs - of course, it would be ideal if the solution can search in my employee ID list and continue down until there are no more IDs and create the same number of worksheets as there are IDs?
Because the macro is creating numerous worksheets, is it possible to name the worksheets based off of an adjacent value (in the same sheet as the IDs) to each respective ID (I'm thinking of a concatenation of the employees' IDs and names)?
One major wrinkle in this process: the above process would be ideal if the "template" worksheet won't change (i.e. no changes to formatting, no additional data elements, etc.) However, it's likely that the client will want to add/remove/change items/elements to the "template" dashboard. Assuming, at this point, all 100 worksheets for each individual/employee have been created, it would be a huge pain to manually add/remove elements from each sheet. Is there another macro that would delete ALL employee sheets? That way, I can re-modify my single template and just run the original macro to re-create my duplicate sheets (but populated with different IDs) again.
I have been trying a number of different functions!
I have the following countif function that is searching a worksheet (Cases Closed) for the name John in Column O and excluding Solutions in column x. The problem I have is there are duplicates cases in Column C that are being counted two and three times.
Is there anyway to have the following function exclude duplicates records in Column C? Just count unique records in Column C?
Basically I've made this up myself because what ill be working with has 100s if not 1000s of rows with many different product numbers that's quantities are different. What I've been able to do up to now is sort the spreadsheet by the product number so all the same rows are next to each other. My problem is however I need a speedy way of making these duplicate rows become one but add the total quantity basically everything in the left screenshot into the one on the right. What I've tried up to now is sorting them so there together and manually adding them up and putting them into one of the rows quantity, then delete the rest. takes to long. Another was to make a row underneath the rows I need into one but that takes more time than manually adding and deleting the rest.
I'm working on a employee evaluation form. I'm using a Pass/Fail system. I have a cell designated for PASS & one for FAIL. In these cells, I would put "YES" if they passed, "YES" if they failed that section. How can I prevent someone from mistakingly entering Yes in both cells?
I am trying to find a macro that will clear duplicate entries in a column of data, and then move to the next column and repeat the process until it gets to a blank column.
There are blank cells between entries in a column. I need to keep the data unsorted as the cells are linked to others in the row, therefore I dont want to delete the cells, just clear the contents.
I would like to check column B and if any of these values match values in column J hightlight the cell on the same row as the match on column E green. I don't know much about excel but this would be a tremendous help because i'm looking through quite a bit of data. I've tried using conditional formatting and i can't seem to get that to work for my needs. Let me know if you need anymore information.
I've generated a sheet where, based on the total run hours of a generator set using a given fuel type, we can determine which engine to run on what fuel when. The problem I've run into is that if more than one engine has the same runtime, the chart returns just the lowest numbered engine and eliminates the remaining engine(s). What I'm trying to do is make it so that when this occurs, the suggested engines show up in numerical order. For example instead of 1, 1, and 1 showing up on the chart, something like 1, 2, and 5 will show when they all have 0 run hours. I've attached my chart below with some engines having the same runtimes. RunOrder.xlsx
New here, I wanna do a "Prevent Duplicate Entries" into my column i try the following: http://www.mrexcel.com/articles/prev...duplicates.php http://www.theexceladdict.com/_t/t040818.htm
They all work well but if I'm using copy and paste It doesn't work! why is tat so? Second issue will be i'm wanna do prevent duplication for the whole column not just one cell.
I have a spreadsheet that has account numbers listed multiple times. I need to eliminate all of the duplicate entries...Is there a formula for this...?
=COUNTIF($A$1:$D$100,A1)=1 however I don't know how to change that to suit my needs (or even if it is at all possible to change it to suit my needs)
I will try my best to explain how my spreadsheet is set up as unfortunately I am at work and I can not use the method to show you the spreadsheet.
My spreadsheet: I already have Data Validation in use from a list that I created. I use the same validation list in Columns C, E, G, I, K-BA
I also use validation list in other cells, and it is OK if these are duplicated throughout the row.
The only cells that should not contain duplicate information are the ones from C, E, G, I, K-BA
Basically I want to stop someone accidentally chosing the same option from the list within the same ROW.
Just in case it makes any difference I should let you know that I have already had help from the forum regarding macros for this same spreadsheet, here is the macro that is currently running just in case it makes a difference if there is an answer to my current problem.
Option Compare Text 'A=a, B=b, ... Z=z Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range Dim Rng1 As Range
On Error Resume Next Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0 If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address).........................
there are unique entries like AU0896 etc. that are repeated in my list. my job is to find how many unique entries there are and add the count at the end so, basically if there are 6 AU0896 entries, then I must create a AU08966 value.
In my spreadsheet, on ( sheet A) I pick up a value to a range (O2:O22) from other worksheet (sheet B), the value is validated when a cell in the same range but other column (A2:A10) reach a specific criteria.
But in the same column if the criteria is typed again I gone a have the same value on range( O ), and I dont want that, because this value represents a total for a day, and is to be added to other cell.
I'm using this, to pick up the data Ex: (A2:A10) Criteria (O2:O10) value picked up
on SheetA, Sumif(sheetBA10:A40,A2,SheetBK10:K40)
How can I count only one of the values picked up from sheet B?
Got a workbook with 4 worksheets and sometimes we need to cut and paste a row from one worksheet to another, now thats easy now as we are all human and sometimes it gets copied and we have duplicate logs.
Now each sheet has usually not got that many rows in it, but that spreadsheet is getting sent to the highest people in the company and any mistakes look poor.
What i'm looking for is a way that i can highligh any rows that a duplicated in each worksheet. I can do it in each worksheet using conditional formatting with something like = COUNTIF($A8:$A$20,A8)>1. but don't know how to check that is not on any other worksheet. On every worksheet only need to check Col A for the duplicate information
Wihitn a large SS I have a column which has a number of entries that may include an amount of duplicate entries. Till now I have been using Sort and them manually deleting the duplicates is anyway that I can select just the Column and then without sorting go down and if a duplicate is found, then the duplicate would be deleted. Meant to say the dta is numeric.
I have a spreadsheet which has data in column A & B. I trying to do an if statement to state "YES" if data in both A and B has been duplicated. EG 111 in (A) & 222 in (B) are a PAIR, im need an if statemnet in Column (C) to say if this PAIR are duplicated in these two columns return "YES". I have tried THREE different methods to do acheive this which are in the attached document. The main issue I am having is the iff statement is returning yes for some of the number when thye are completely different. I have used concatenate, & and tried to multiply the two values to try to make it unique. I have an example of each in the separate tabs.
With I click on the Macro Button I want to display the contents as shown in sheet 2
That means
In sheet 1 there are number of items purchased shown with different rates.
After clicking on the Macro button in Sheet2 I want the following
1. All the single entries purchase should not be displayed 2. All the items purchased 2 or more times but same amount should not be displayed. 3. Only items purchased two or more times should be displayed and that also with different values. Here if the item is purchased 4 times but only once the amount is different then also it should be displayed.
I have given only few items but actual items are more.
The serial numbers should also be arranged automatically starting from 1,2,3, etc.
What is the code if I do not want to change the serial numbers. with a suitable macro.
I'm trying to get data totals for multiple entries and haven't been able to figure out how to do this using a pivot table.
For example:
A B Ben 2 Bill 1 Jane 3 Bill 4 Dave 7 Jane 4
Would make it
Ben 2 Bill 5 Jane 7 etc.
this list is 3700 cells long.
Is there an easy macro or another way of doing this? I want to generate a list that gives all data totals but just lists each persons name once. I treid attaching the file but it wont let me! here is the link... [URL]
