Eliminate Duplicates Based On Different Variables?
Nov 28, 2012
I have a list of serial numbers. There are many groups of 8 same serial numbers, where group consists of 2 projects with 4 SN per project (because of 4 different events). I want to create a formula that marks one project with 4 out of 8 SN for deletion based on set of 3 dates assigned to them. In short I need to count 4 rows per project as one unit.
Serial
Project
Event description
Date1
Date2
Date3
Here is a list on what information matters when making decision if to mark project for deletion or not.
1. Project1 has no Dates entered compared to Project2. Mark Pr1
2. Project1 and Project2 has no Dates entered. Mark Pr1 (random, does not matter which should be removed)
3. Project1 has 2009 Dates, Project2 has 2011 Dates. Mark Pr1 because dates are older
4. Project1 has less Date entries filled than Project2 (same year). Mark Pr1 because less Date fields entered.
I can somewhat do it for separate rows, how I can make these rules apply for whole project as one unit related only to one SN at a time. Biggest problem is there is no pattern of dates entered. Sometimes one row can be filled another missing out info and etc.
I have a huge list of accruals and payments. Accruals (positive) are entered, and at a later date are offset by the payments (negative). Im trying to make a schedule so i can determine which are left over.
This is easy to do manually for a small amount of rows. However im dealing with 5000 rows and i do not want to manually match it would take many days to do.
Ive tried a duplicate remover. To get it to work i made an absolute value column for the negatives, and compared it to the positives column to find the duplicates. This works to a point. However, If i have three accruals for 100, and one payment for 100, all are identified as duplicates which obviously is not what im looking for.
I need to get it where one accrual is matched to one payment. if there are 3 accruals, 2 payments, 1 is not a duplicate. if there is 3 accruals, 1 payment, 2 are not duplicates.
I am attaching a file with an example of a spreadsheet that I am trying to sort out. In this example I have 3 samples (I could have many more). Each sample has 8 columns associated with it (N, M, I, F, S, MS, KM and KD). The length of the dataset is different for each sample. The MS column is the same as M but contains a few zeroes. What I am trying to do is:
1) generate one column (MSA) containing only unique values (no zeroes) from columns MS1, MS2 and MS3. The unique values should be selected within a specified tolerance (for example, 0.001, which makes 52.00706 from MS1 and 52.00701 from MS2 duplicate values although they are not exactly the same)
2) generate 3 columns ( named SS1, SS2 and SS3) with sorted columns S1, S2, and S3 so that for each value of MS in column MSA each of the three columns will list the corresponding value of S1, S2 and S3 (zero if there is no corresponding value)
how to return a value based on two variables. I was trying with hlookup and offset but excel kept ignoring my thoughts.
Here is a sample of how the data looks like and what I want to do:
excel.jpg
Depending on the ID which is entered in G2, cell H9 has to get the value from D4.
I know that ID is gonna have to be extracted with find function from column A. So every time an ID is entered (in cell G2) it would need to look for it in column A and then return the value VACLEG from column D.
I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.
Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.
I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code: Sub SummariseSheets() 'collates individual client development data from consultant 'worksheets and compiles in one sheet on summary page
[Code]....
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.
Here is the sheet that the information will be drawn from:
Part#QTYREP 4011A 4023A 4031B 401 1 A
Here is the sheet that the information will be output:
What I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F) .......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg 1. City 2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C. ..................Weight..................City..................Charge per 100kg 24...............800..................Toronto..................$5.50
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
I just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
I have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.
For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.
What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between
Code: Sub BEOA4() Application.ScreenUpdating = False Dim wsSource As Worksheet Dim wsDest As Worksheet Dim FoundX As Range Dim FirstFound As String Dim lastrow As Long
I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.
I need this deleting of rows based on a couple of variables.
I need to keep any row that Col F has the text "QCCONTROL" and I need to keep any row that COL N has the text "HOLD" and I need to keep any row that COL N has the text "REJECTED".
Sub Delete_OK_Lots() lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 2 Step -1 If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then Sheets("adage inventory").Rows(x).EntireRow.Delete End If Next x End Sub
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
I have a sheet where I have a "Status" column in each row which could be "Waiting for Band", "Waiting for Organizer", or "Ready to Go!". These options are set as a data validation list. I have 2 cells later in each row where the response can only be "Yes" or "No". I want for the Status column to be set automatically based on the responses in the later two cells. So for example
where C3 and M3 are Yes, the status column in B3 should read "Ready to Go!" where C3 is no but M3 is yes, the status column should read "Waiting for Organizer" where C3 is no and M3 is no, the status column should read "Waiting for Band"
The above is actually an exhaustive list of all of the potential outcomes(!)
I'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.
Examples: 1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001. 2) Quarterly: same idea but by quarter. 3) Monthly: No issue, just a SUMIF.
In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.
I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work.
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer) Dim initialCell As Range 'specify the upper left cell Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet ActiveWorkbook.Sheets("Calculator").Activate Set RefCell = ActiveSheet.Cells(2, 3) WS.Activate Set initialCell = ActiveSheet.Cells(initialRow, 1) initialCell.offset(numRows, numCol).Select Selection.Table ColumnInput:=RefCell 'the calculation should be automatic, if not, then calculate WS.Calculate End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
For what I'd like to do, the attached workbook actually represents 3 worksheet. one input sheet and two output sheets (output1 and output2)
I plan to use command button " CommandButton1" on input sheet to start the calculations
Here's what I want to do after pressing the " CommandButton1" :
1. start with case 1 and find the value of Input A to Input J from input table using Vlookup and paste those values to respective palceholder for inputs in blue colour area above the input table for each input.
2.for empty cell in the input table paste 0 (zero)
3. after this will give results for case 1 inputs on sheets "output1" and "output2" on basis of formulas used for each output
4.then copy the results for case 1 inputs from sheet "output1" and "output2" and paste them on "input sheet " in Output table (its below Input Table) next to case1
5.Do this for all cases (in actual workbook there will be more than 100 cases)
I have a list of about 85,000 addresses and I know that there are about 35,000 duplicates in it.
If I do 'Remove duplicates' it deletes them but takes the first one of each it finds and what I want to do is remove the duplicate with no UPRN in it (Column B)
I have code that pulls data from multiple sheets in a folder and throws a time stamp in the row/col. The data can have duplicates when adding to the master sheet. What I would like is to delete the duplicate row that is furthest from the NOW date.
Criteria for duplicates are based off of Col A. Time stamp is in Col G.
I have a column of information, and I want to find out of there are duplicates in it without alphabetizing it. Where there is a duplicate (say E2=E5), I want to check the adjoining column to see if the value of those fields are duplicates as well (does F2=F5). So, what formula would I put in D2 to see if the value of E2 has an equal anywhere in column E, and if any duplicates are found (say E10,E33, and E43), to check if F10,F33 and F43 equal F2 (the value adjoining the cell that was searched for).
to find duplicate entries in a database by using people's last names (in column E) and birthdays (in column F). Odds are if they have the same DOB and the same last name, we've found a duplicate.
