How To Keep The First And Last Rows Of Every Unique Value
Nov 29, 2012
This is a large database. I've attached a sample.
One code is for one animal, date the animal was weighed and weight. Animals were weighed several time, hence the repetition of the code.
What I need is the first and last rows only of every code. This is in order to then calculate the weight difference and divide it by the time difference to get the daily weight gain.
The trick is to keep the first and the last rows for every code, bearing in mind there's no consistent pattern for repetition of the code (as you see, sometimes 6, 5, 3, 7). Sometimes it is even repeated twice (in this case nothing is required to be deleted).
Sample.xlsx
View 14 Replies
ADVERTISEMENT
Apr 7, 2014
Col A Col B
163401 1
163401 1
163401 0
163402 1
163402 0
163402 0
GOAL -I want to be able to count Col B only once for the same set of records in COL A.
Based on Distinct criteria on COL A, I need to be able to count COL B. The count should be 1 for 163401 and not 2. Similarly for 163402 the count should be 1
View 11 Replies
View Related
Dec 28, 2006
This is an example of my table , there are more columns but here is apart of it:
id Product Name
1Frnt Fndr CR125/250 black
1Frnt Fndr CR125/250 white
1Frnt Fndr CR125/250 silver
1Frnt Fndr CR125/250 00 cr red
2Frnt Fndr CR125/250/450/500 black
2Frnt Fndr CR125/250/450/500 white
2Frnt Fndr CR125/250/450/500 00 cr red
3Frnt Fndr CR125/250/500 black
3Frnt Fndr CR125/250/500 white
3Frnt Fndr CR125/250/500 florescent red
4Frnt Fndr CR80/85 black
4Frnt Fndr CR80/85 00 cr red
I need to delete all the rows that do not have a unique ID:
Frome the table above I need it to return something like this
id Product Name
1Frnt Fndr CR125/250 black
2Frnt Fndr CR125/250/450/500 black
3Frnt Fndr CR125/250/500 black
4Frnt Fndr CR80/85 black
check out the file
View 10 Replies
View Related
Mar 29, 2007
How to remove unique rows from excel-table and leave only dublicates? any macros?
View 9 Replies
View Related
Oct 20, 2006
I have a piece of code that is designed to look at information on one sheet, and if it does not match what is on another sheet, it will copy the specific rows over. However, even after I do this once, and run the macro again, it will copy the same information over again. I need to figure out why it won't stop the copy after the first time the rows are on the new page.
Sub Datamove()
Dim i As Integer
Dim k As Integer
Dim v As Integer
Dim eRow As Long
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Set sht1 = Worksheets("Uncorrected QC")
Application.ScreenUpdating = False
k = 2
With sht1
For v = 2 To . Cells(Rows.Count, 1).End(xlUp).Row Step 1
Dim shName As String....................................
View 4 Replies
View Related
Jul 20, 2007
i'm looking to write some code that will filter through a list and either identify, or even better delete rows that aren't unique.
here's an example
xyz1
xyz1
xyz2
xyz2
xyz2
from the above lines, i'd like to see xyz1 deleted, and the first two lines of xyz2 deleted.
(obviously this is a very basic version of what i want, but it gives an idea)
also, the lines may be out of order
i.e.
xyz2
xyz1
xyz2
xyz1
xyz2
- a sort can be done so it's not that important that they're out of order...
View 7 Replies
View Related
Nov 24, 2008
I have another question to ask, that is why I am posting another thread. I have a column with blanks and data (roughly abt 300 rows as such, not unique, there are many rows repeated numbers). How can I copy the distinct values from this column to another sheet. I am able to copy unique values to another column in the same sheet. But when I try to copy them to another sheet it gives me an error. I used advanced filter option to do this. I also want to get the count of these unique values. In sql I know it can be acheived with "Select Distinct row1, count(row1) from table1 order by row1".
View 3 Replies
View Related
Jan 24, 2012
I have a function to count unique IDs
=SUMPRODUCT((A1:A30000"")/COUNTIF(A1:A30000,A1:A30000&""))
and it works OK.
How can I count the IDs only in the visible rows after applying a filter?
View 4 Replies
View Related
Mar 4, 2013
I got this code to copy stuff from Raw data worksheet which has a Dynamics Atlas table to another worksheet named input. However, I noticed that some records are duplicated so I want the code modified to only copy unique records based on criteria in column B of the "Raw data" worksheet.
Sub Copy_atlas_table()
Application.ScreenUpdating = False
With Worksheets("Input")
.Range("A2:S2" & .Cells(Rows.Count, "G").End(xlUp).Row).ClearContents
[Code] .......
View 1 Replies
View Related
Oct 28, 2013
I have the following table A
1
2
B
1
1
B
2
1
[Code] .....
I would like to extract all the unique rows from the above table so the outcome will be
A
1
2
B
1
1
[Code] .....
However I can't think of a simple macro to do it.
View 2 Replies
View Related
Jul 3, 2008
This is just a screenshot of my huge data of over 60,000 rows. Owing to the restriction of HTML Maker, I am just showing in less than 30 rows in this screenshot.
I need to extract only the UNIQUE ROWS depending upon the column called "Unique Code" to another worksheet. A particular row or a record is repeated as many as 90 times in this database. I need to extract the whole row which is unique depending upon the column C which is "Unique Code".
I have tried the Advanced Filter option a couple of times but it does not seem to work. Also, the Auto Filter option is not versatile for such amount of data.
View 9 Replies
View Related
Oct 27, 2008
I want to be able to count unique rows of data.
I have three columns (A) has a date (B) has the name of a person and (C) has an audit type.
I know I need an array but not sure how to structure it for 3 columns
DateNameAudit type01/04/2008DavidCDM01/04/2008John1 in 1030/04/2008Bill1 in 1001/05/2008DavidVehicle01/05/2008DavidVehicle01/05/2008JohnCDM
The answer for these 3 columns is 5. Rows 4 and 5 are the same so are only counted once.
I can get the arrray to count unique items in the whole range and in a column but not for each row.
I have a feeling this is going to obvious but I can't work it out.
View 9 Replies
View Related
Aug 2, 2006
I have a list in column C that the data appears numerous times. What I want to do is copy the first occurrence of that item along with the cells from columns A, B and E of that row to a new sheet called Unique Records in the same workbook. I tried Advanced Filtering but it is not working the way I thought it would.
View 5 Replies
View Related
Jun 5, 2013
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 certain data values that appeared on the repeating row (columns G - J), and included them as PRICE, COST, BEGIN, and END values in new columns on row 2.
Is there a way to accomplish this via some functions or maybe even a VBA script?
View 3 Replies
View Related
Jun 15, 2009
I am trying to use a formula that will populate A3 with the value of A2, all the way down the column. (as per the area highlighted in yellow) As you can see on the example sheet, the account code changes periodically, this has been causing me many problems!
As the list i am working on has around 100 differfent codes, it is too time consuming to copy paste manually
Reason for this is so that each invoice number in column B, will have a unique identifer in column A (account code)
View 12 Replies
View Related
Jul 7, 2009
I have one worksheet which acts like a data dump (actuals) where data is just pasted in. This worksheet is sorted and edited on a continues basis. I need this data to be sorted into their respective coded worksheets (110, 120, 130).
I would like a function in Worksheet 110, 120, 130 that captures the data for that 'code'. Now the issue is that much of the data is non-unique, dates, codes, accounts may sometimes be duplicated however i still want to display the entries separately in their respective coded worksheets. (I had a solution using column numbers however it isn't viable because the data gets resorted often)....
View 14 Replies
View Related
Apr 19, 2013
I have this data
1 355
1 243
1 567
2 456
2 443
3 889
3 890
3 123
and need to sort it in columns like this
1 355 243 567
2 456 443
3 889 890 123
View 2 Replies
View Related
Jul 29, 2013
I am wanting to find rows that with unique values in the first column, and then take those rows and display them in a more readable format. The section titled input is an example of information that would be pulled from a query into Excel. The Desired Output section is how I would like the info to be displayed. If this is possible without VBA then that is even better.
Excel 2007
A
B
C
D
[Code].....
View 2 Replies
View Related
May 5, 2014
I have an extract that includes unique attempts to complete a transaction. Sometimes these transactions fail 1 or multiple times. Sometimes they are successful in one try. other times they first fail and then are retried and succeed. I am trying to flag failures in a new column that later succeed in the same session. Here is my data:
Date
Order #
Session ID
Mac Address
Status
5/2/14
O123
A100
11111111F
Failed
[Code] ............
I would what these flagged as such:
Date
Order #
Session ID
Mac Address
Status
Successful Retry?
5/2/14
O123
A100
11111111F
Failed
Yes
[Code] ........
View 2 Replies
View Related
Jul 2, 2014
I have a table with 6 columns (A to F) and multiple rows each, with cells containing words. Taking the words in any one cell from each of the columns in order from A to F will form a complete sentence each time. I need a solution to display all unique possible combinations in column G.
The number of rows is different for each column. A successful result in column G has to include cells from all columns (A to F).
I searched this forum and found a few analogous questions/solutions, but nothing close enough for me to apply to my case. I tried using a concatenation formula, but I have to manually edit the formula in each cell to get all unique combinations (and that would mean thousands of times). If I just drag the formula down it will increment all cell rows instead of one cell's row at a time.
Here's an example : all possible unique combinations.jpg
View 7 Replies
View Related
Sep 20, 2009
I have a lot of information for various contacts, in the following format:
Name: John Smith
Address: 123 Spring Street
Phone: 555-5555
Email: example@example.com
Name: Jane Doe
Address: 456 Elm Street
Phone: 555-5555
Email: example2@example.org
Name: Richard Roe
Address: 10 Bridge Street
Phone: 444-4444
Email: example3@example.org
What I am trying to do is convert that information so that it looks in a spreadsheet as follows:
example.jpg
I feel fairly sure this is the kind of thing Excel is capable of doing, but despite much searching and experimenting can't figure out how to do.
View 4 Replies
View Related
Jun 24, 2014
I need creating a macro which compare the values of "Column B" of attached both "Sample1" and "Sample2" excel files and if any unique value found in Column B of "Sample2" file then the entire row should be get copied in "Sample1" file after row count.
For ex. the rows colored as yellow in "Sample2" file are unique and should be get copied in "Sample1" file.
View 3 Replies
View Related
Aug 29, 2012
I am using the following code to copy a unique list, but it gives a duplicate in the first 2 rows(col Q). There is no headings in the columns.
Code:
Sub CopyUnique()
Dim lastrow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "P").End(xlUp).row
ActiveSheet.Range("P11:P" & lastrow).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ActiveSheet.Range("Q11"), Unique:=True
End Sub
View 4 Replies
View Related
Mar 6, 2003
I have several columns in an excel sheet which contain values (eg. Names). The entered names can be unique or already exist in the column. I need to find out (using a function or macro) how many different names were entered. Duplicates shshould be ignored in the count.
View 9 Replies
View Related
Oct 9, 2006
I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.
I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).
I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.
All the cust numbers are unique so would I be able to use a wildcard?
View 4 Replies
View Related
Sep 22, 2008
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
View 3 Replies
View Related
May 8, 2014
I want to find the highest value in a column (MaxValue) and populate first blank cell in a column with Maxvalue+1. Basically, I want to provide each new row with a unique project number. First, I'm running a macro to insert rows which copies the formula and format from Row 4, the user enters how many rows he wants and the requested number of new rows are inserted below Row 4 (That bit all works fine) . I now want to find the highest project number that has been used in Column 1, starting at Row 4, increment the highest project number by 1 and populate the newly inserted rows with the new project number.
I have a couple of problems with code I'm trying to use: If the active cell in column 1 is highest value the code ignores the active cell, i.e. this works once, as the cell that I have just populated becomes the active cell and the highest number, i.e. the next time the macro runs I get the same number as the active cell.
Ideally, I'd like use the number of rows that the user requested in the macro to insert new rows to be used in this macro to provide a unique project number for each of the newly inserted rows. (The add new rows macros uses Dim NoToAdd As Integer, as the number of rows that the user wishes to insert). Although, I'm quite happy to run the macro several times to find and populate projects which have not been allocated project numbers.
Here's where I've got to:
[Code] .....
View 2 Replies
View Related
Jan 27, 2014
I have a spreadsheet that lists employees and their certifications. If an employee has multiple, then they will show up on as many rows as they have certifications.
The macro I have merges them into one row with a line break, but only the first column's unique value has been merged while the other columns containing their own unique values are duplicated when I want them to show up only once. Example: Jane Doe shows up 2 times on the report. Her name should only show up once on the row, not 2 times with a line break.
Here is the code. I have also attached an example of what I need. Because the attachment is a simpler version of the actual report, is it possible to specify which rows have the unique values and which ones don't?
View 2 Replies
View Related
Nov 4, 2013
I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values
I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:
GA-AG-00010-A-2013
Apache Hunting Club
709
36
GA-AG-00020-A-2013
Tiger Branch Hunt Club
1596
71, 72
GA-AG-00030-A-2013
Big "O" Hunting Club
3058
59, 64, 65, 75, 79, 84
I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.
Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
[Code] ......
How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.
View 9 Replies
View Related
Jul 24, 2014
I have data in excel sheet in the below format:
Existing view.png
How to write a VBA code or Macro to get it in below format:
Required View.png
Timestamp column is the unique key.
View 1 Replies
View Related
