I have a spreadsheet that has 3 columns. Out of which only 2 are in use.
I have attached an xls for your convenience.
The Date column is in play and Test column are in play.
Basically I want to know if there is a way, where I can
shift a value down and duplicate (in the Test column)
eg:....
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies View RelatedI've got a problem with one of my macros. This code should import a text file. The name of the imported file is the same as the value of the cell that is active the moment the macro starts. The way I've written it, the code can't find the needed text file.
Sub addfile()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:aprojects" & ActiveCell.Value & ".txt", Destination:=Range(ActiveCell _
))
.Name = ActiveCell.Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0.........................
I'm trying to have one cell be the constant place I enter data and then when I hit enter, I'd like the data I entered to be moved down to the cell below it. Then for every new number I enter in that first cell, it would auto shift the previous data down and add the new data to the cell below it.
Basically, I'd like to record data but I dont want to have to scroll to the bottom every time to enter new data since this list will be continuously updated. And I'd like the most recent data entered to be on top.
Then I want to take the top 100 cells in that column, and do a running average of them.
I have a Start Shift and an End Shift time,
Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
i am using the code posted by Reafidy on [html] http://www.ozgrid.com/forum/showthread.php?t=62708[/html] to create a list of all possible combinations. The only problem i have is that sometimes the total no. of combinations exceed the excel row limit of approx 65,536. once the row limit in column 1 exceeds 65k, the remaining combinations automatically shift on column 2 and then on 3 n so on.
View 6 Replies View RelatedI'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.
For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?
IIn my example I have result data from the 2013 USPGA Tour, ordered by player (column A) with subsequent columns detailing their finishing position in each event.
I simply want to condense each player's performance data into one row - as doing it by hand after each event is very time-consuming.
Is there a way of using a simple formula, macro or pivot table (or whatever) to merge each player into one row, but keep each column in the same position (ie to correspond to each weekly event).
I am trying to do is when a user enters in a GL (a 4 digit number) from the list (tab 2) I want it to appear in the summary box below. However the same GL number can be used multiple times so I will have duplicates. The data entered above will always change there is over 200 possiblities the user can enter. So I don't want the data to be specific it will populate based on what is entered by the user.
Can this even be done?????
Say if the user entered:
Column C Column G
4606 $20.00
4606 $20.00
2134 $15.00
2301 $35.00
4606 $100.00
The data in the GL and currency column in the summary box would appear as the following:
Column C Column G
2134 $15.00
2301 $35.00
4606 $140.00
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View RelatedI'm a first timer here an I'm hving a problem with data. I have 3 sheets in a workbook, I want to transfer all the data to one sheet. I have 3 columns labelled Number, Name, & Sales, on each sheet some of the numbers and names are the same and I want to be able to match them up and put the sales from each sheet into a new column, so the final sheet will have 5 columns in total, if the numbers and names don"t match I just want to add those to the bottom of the matched ones.
View 10 Replies View RelatedI need a code please that will look in Column C in every workbook (excluding the sheets “Blank”, “Orders”, “Summary” and “Archive”) and then it will highlight duplicates in Red and Bold.
I found the following code but I need it so it looks on every page excluding the ones mentioned above.
I have a list of items which I want to return the number of sheets in stock at the end of the month which uses a lookup.
My problem is though is that products can appear on the list more than once, therefore I need an IF formula which if it’s a duplicate (other than the first instance) I need it to appear as zero.
I have attached a spreadsheet which may be clearer than above.
Your notice on the spreadsheet the same product appears in 4 difference racks however I only need it to show once the total sold and total receipted. I have shaded in grey the desired result.
Bit of a tricky one this, I have two spreadsheets both with data I need to merge the two and only keep data which is present in both sheets. Each row has a unique identifier. I have copied the sheets into one spready and sorted the data, I thought there might be some kind of edit go to function but cannot find it.
View 2 Replies View RelatedAll i want is to delete duplicate rows or data in query 1 column.......
Query 1Query 2Confidence_0.118_Length_701McdonaldsConfidence_0.118_Length_701Magic moments
Confidence_0.118_Length_701ZamaicanConfidence_0.176_Length_582Urobi
Confidence_0.176_Length_582McdonaldsConfidence_0.176_Length_582Rum
[Code] ...........
I have a table simmilar to this:
Reptile Dog Red
Reptile Tiger Purple
Mammal Tiger Stripes
Mammal Tiger Spots
Mammal Racoon Black
Mammal Cat Plaid
Mammal Dog Brown
Mammal Dog Purple
Reptile Dog Red
Reptile Tiger Purple
Tree Dog Orange
Tree Pine Green
I would like to use this data to populate within a seperate worksheet that reads:
Mammal, Tiger, Stripes
Mammal, Tiger, Spots
Mammal, Dog, Brown
Mammal, Dog, Purple
Essentially pulling all of one duplicate item within a column. The problem I ran into is when I run a vlookup within the entire table, it gave me duplicates.
Basically, it gave me...
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal, Tiger, Stripes
Mammal Tiger Spots
I know this is because of the array, just forwarding to the next item, and rerunning the lookup... since Mammal is not at the top... it has to wait till the array gets to the area of "mammal".
How do I create the list, so it will not create the duplicates... like in the example I gave.
I have a spreadsheet in which invoices are listed. Some of these invoices have been cancelled with a negative invoice, so I want to match the negative invoice to the corresponding positive invoice so that accurate averages of all the data can be attained.
RefSurnameNameMonthTimesheet Rate PHValue
24963DoeJohnJun-0712-Jun-0719.5975.15
30693DoeJohnJun-0720-Jun-0719.5975.15
30693DoeJohnJun-0725-Jun-0719.5975.15
30693DoeJohnJun-076-Jul-0719.5975.15
30693DoeJohnJul-079-Jul-0719.5975.15
30693DoeJohnJul-0713-Jul-0719.5-975.15
41270DoeJohnJul-0713-Jul-0720.761120.95
30693DoeJohnJul-0726-Jul-0719.5-975.15
41270DoeJohnJul-0726-Jul-0720.761120.95
30693DoeJohnJul-076-Aug-0719.5-975.15
41270DoeJohnJul-076-Aug-0720.761120.95
30693DoeJohnAug-076-Aug-0719.5-975.15
41270DoeJohnAug-076-Aug-0720.761120.95
41270DoeJohnAug-0710-Aug-0720.761120.95
I have a set of data which are deal numbers
So week 1 i could have
Deal
ASD
ADF
AGF
Then next week i would get another list of deals
ASD
AHJ
ADF
AGF
AIG
Is there a formula i can use which well tell me if a new deal has been entered in the following week.
So in the above AHJ and AIG would be new deals
(i would simply copy the deals and put them in Column A and B and the formula to go in column c)
I have an excel spreadsheet containing alot of data which looks something like this:
Material Plant
1 ABC
1 DEF
1 GHI
2 ABC
2 XXX
3 ABC
4 ABC
4 DEF
5 DEF
5 XXX
5 YYY
5 XYZ
As you can see, one material can exist in more than one plant.
What I want to do is to seperate the materials into 'blocks' (with a blank row) so that they exist seperatly. (I can then easily total up someother value in column C). Example:
Material Plant
1 ABC
1 DEF
1 GHI
Message board virgin here, but I need help with an excel issue so excuse me if I don't follow protocol.
I'm using vlookup to return the salesperson by serial number, but run into a problem when my criteria has a duplicate value, but my answers does not.
Here's my example:
Serial#, Salesperson
123 Bill
456 Bob
456 Suzy
789 Bob
i have is a worksheet, columns A thru J. Column B has names in it, Column F has dollar amounts. i need to create a macro to find the duplicate entries in column B, add them in column F (which would overwrite the existing amount) and then delete all the duplicates. all of the columns between A and J contain data. i was able to do this on an totally different sheet using columns A and D. but i did not have other data in the sheet. it's been a long time since i completed this other sheet and i need a refresher course in vba.
Sub finddups()
Dim Sh As Worksheet
Dim LastRow As Long
Dim Rng As Range
Set Sh = Worksheets(1)
Sh.Columns(5).Insert
LastRow = Sh.Range("A65536").End(xlUp).Row
With Sh.Range("A1:A" & LastRow).Offset(0, 4)
.FormulaR1C1 = "=IF( COUNTIF(R1C[-4]:RC[-4],RC[-4])>1,"""",SUMIF(R1C[-4]:R[" & LastRow & "]C[-4],RC[-4],R1C[-1]:R[" & LastRow & "]C[-1]))"
.Value = .Value
End With
Sh.Columns(4).Delete........................
i need to convert this old macro to work with my new sheet. since the underlying ideas are the same i think it should work. however i am unable to get it right. i always end up deleting all my data.
I now need to look through 4524 rows (columns A,B,C) and identify any duplicates based on column A. After all duplicate data has been identified, I would like to delete out all non duplicates. I searched previous posts, but couldn't find anything that worked for me.
View 5 Replies View RelatedI have a worksheet that has 3 duplicate values in a particular column, I need a macros that will highlight two of the duplicates row and then another macro to delete the entire row. The duplicate element are in column R. find attached worksheet.
Copy of OCL 2010 (3).xlsx‎
I have a spreadsheet with 3300 rows. In column A there is a list of company names and in column H there is a corresponding Sales Rep name.Column A has many duplicate company names. I would like to run a macro that will find the a company name and then delete all the rest of the rows that contain that same company name.
Attached is a sample of that spreadsheet.
I 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.
I am using the following macro to insert the word "Duplicate" in the first blank column next to a duplicate row. My data is sorted by the first column. Data Example:
12345 a
12345 a DUPLICATE
11111 b
23123 b
Here is the macro I am using and it does not work. It marks the first duplicate it finds then goes into an infinite loop. Any Idea where I went wrong?
Sub MarkDupes()
x = ActiveCell.Row
y = x + 1
Do While Cells(x, 1).Value <> ""
Do While Cells(y, 1).Value <> ""
If (Cells(x, 1).Value = Cells(y, 1).Value) Then
Cells(y, 3).Formula = "Duplicate"
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop
End Sub
I have a table (is populated in a macro already) that has two header rows (1 & 2), inputs a blank row on row 3, then starts data input on row 4. The data can potentially run to row 1000. The columns that are used are B through M. The part name is in column B and the quantity is in column C. The part name can repeat with a different quantity and be put in a different row than a previously entered value. I need to combine those x number of rows based on the match of the part name and sum the quantities then delete the remaining matches. Columns D-M are not unique.
I have attached a sample. The sample is very simple with just two matches but the matches can more than two. The matching rows are highlighted. duplicate rows.xls
