Is there a way to make Advance Filter show the most recent (highest row)unique entry instead of the first entry (lowest row)?
For instance if a spread sheet has
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
I want to keep the "1 3", "2 3", and "3 3" entry, not "1 1", "2 1", and "3 1".
I'm doing a data/filter/advanced filter/show all/unique records to get rid of any duplicate rows. I tried creating a macro and but the macro recorder doesn't know that I want to do NOT a regular paste, but a paste from the most recent entry from the Office Clipboard. Is there a formula and/or macro that will allow me to do this?
View 4 Replies View RelatedIf I use =MAX(A1:G1) I get the highest value. Is it possible to show the next highest rather than the highest and if so how?
View 9 Replies View RelatedI have this column, and consists of a collection of dates. I want:
1 - change the format of the dates of "03.01.2013" to "01/03/2013"
2 - arranging dates from oldest to newest and then sort them without repeating
01.03.2013
01.04.2014
01.06.2014
01.07.2014
[Code] ...........
Each product is represented by a serial number (column A).
The can be sorted on column A from smallest to largest prior to calculating results if that helps.
The repair list contains 1 entry per spare part used, so the same serial number may occur several times.
Furthermore, a product may have been repaired on several instances - so the serial numbers can span several dates (column B).
The solution i am looking for should return the number of unique repair dates per serial number. That way i can see, how many times each product has been repaired. Results can be displayed in an individual column.
Sample list:
Serial........Repair date
207742052008-09-04
207755082008-12-17
207755212008-12-31
207755212009-01-22
207755212009-01-22
207755212009-01-22
207755212009-02-13
207755212009-07-24
207755362009-05-20................................
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies View RelatedCan anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies View RelatedI have tried to construct a formula for columns M N O and P to extract the names of the four best marks for each student. I have used the 'Large' formula but it does work when there are equal marks in the list.
Has anyone used a formula to order the first four highest marks and show the headings?
Here is a sample of the Spreadsheet I am using.
(Using Excel 2003)
Column headings are:
(ignore the commas - they are just spacers for the data when posting)
,B,,,,,,C,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,M,,,,,N,,,,,,O,,,,,P
Foot, Athl, Base, R.Cl., Rug, Crick, Tram, Bad, TT, Gym, Golf,,,,, 1st,,,2nd,,,3rd,,,4th
,20,,,,,25,,,,21,,,,,19,,,,22,,,,,20,,,,,24 ,,,25,,,,21,,,24,,,,19,,,,,, Athl Bad Tram Gym
,25,,,,,21,,,,19,,,,,22,,,,20,,,,,24,,,,,25,,,,21,,,,24,,,19,,,,25,,,,,, Foot Tram Golf Crick
,20,,,,,26,,,,25,,,,,21,,,,19,,,,,22,,,,,20,,,,24,,,,25,,,21,,,,24,,,,,, Athl Base TT Bad
how can I get excel to show the highest and lowest figures in a range to display in another 2 cells. Can this be done without having to sort the data and remove all of the other rows except highest and lowest. I am using Excel 2003.
View 5 Replies View RelatedI 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] .....
Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
I've found part of my answer from searching for a previous thread and altering to suit my needs.
I've got the array formula:
=SUM(IF(FREQUENCY(IF(D2:D20000"",MATCH("~"&D2:D20000,D2:D20000&"",0)),ROW(D2:D20000)-ROW(D2)+1),1))
This gives me the correct number of unique entries. What I'm after is the number of unique entries that start with 1042
i need to filter and delete duplicate values. where i have some issues is that i just need keep the first entry and delete the second (and so on) entry. means when i have a list with ids and hours like that:
id1 hours1
id1 hours2
id1 hours3
id2 ...
i need to have id1 and hours1 but delete hours2 and hours3. hours is the result of a formula. i tried with advanced filter but it somehow just hides me the duplicates and it sums me the hours.
I am trying to filter a list where I can enter a value in a criteria box and it will filter the list just with those entries that meet the criteria entered in the box.
E.G If I have a list containing the name Jones 3 times, and Brown 7 times, I want to type the word Jones in a criteria box and it will sort the list to only show the 3 Jones entries
ID = 1: The B Number is the same for entries of ID =1. Thus where ID = 1 and Last is indicated, Cost = 10.
ID = 2: The B Number changes four times. In cell G20 (Last of all ID =2) Cost = 20+30+40+30 = 120. You are basically summing at each instance the B Number changes.
ID = 3: Cell G24 = 100 +30 (two instances of B Number changing)
ID = 4: B Number is always the same. Cost = 50
ID = 5: B number changes 3 times, Cost = 50+120+140 = 310
In essence, the idea is that if the ID matches for all rows of particular client, the total cost = individual cost. However, if the ID changes multiple times for a single client, the total cost becomes the sum of changed costs, but not the entire column, just summing at each instance it changes. And this summed cost must be entered whenever we see the last instance of a unique ID (This is indicated using Column F).
The problem that I'm running into is that currently I am doing this manually with my actual data set (has nearly 200,000 rows). What excel function or VBA code could I write to automate this entire process?
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
View 5 Replies View RelatedA stab in the dark here, but is there any way to be able to 'jump' to a particular position within a filtered list. My list has around 20,000 items in it and I'm having to go through each one systematically to make edits on the relevant line and it is so laborious trying to find the correct place in the list every time.
Is there anyway to be able to may be key in the first letter or two letters to avoid all the continuous scrolling to find the relevant entry?
I can view the data how I need to by conditional formatting and pivot tables but HR wants to get rid of all rows that: only have 1 accounting line per document such as Doc MI310712 in attached sample So I only want to display records if there is more than 1 accounting line for each document
My understanding of Excel is that in order to get rid of rows that a VBA macro is needed since there is no function to do it. My VBA is limited but is there a way to specify criteria in a filter to hide the rows using a formula?
I'm making a user form in excel/vbe, that will enter data into a worksheet behind the form. I would like to make a unique reference number that is automatically generated by the form as the next in the series. It can be as simple a number as 00001 so nothing too complicated I hope.
When the user opens the user-form i would like the reference to appear on the form so that they know what it is. Then when they enter the data I want the number to go in the database with all the rest of the info they have entered and also for a message box to come up reminding them to rememeber their number.
I'm wanting to filter a set of data by inputting in to a cell. And it works perfectly...
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Intersect(Target, Range("B4")) Is Nothing) _
Then
Exit Sub
End If
Cells.AutoFilter Field:=2, Criteria1:="=" & Range("B4")
End Sub
The only thing is I now need is for the code to clear the filter if the cell (B4) is blank. It currently filters out all the data if the cell is blank.
I am looking to modify my combobox from a userform to Filter the list based on the first entry on what the user will add.
I have a code that will search for all entries in my "Control" sheet and passed this on to my combobox:
[Code] ......
I have, within a module, the following function code that the user find the entry by adding the first 3 letters of the search they need:
[Code] .....
I would like to know what to change in the function to filter the list, once the user adds a first letter and then press the dropdown from the combobox, to filter the list based on all entries from the first letter to only show entries with that letter.
If A is entered, then, show only the entries strating with the starting letter A, but at the same time if the user adds more letter to filter to the first then second letter:
If AB is inserted, then, show entries with AB only.
Where do I add this code also, is this another function or this could be inserted within my function?
I need to write code to allow the user to select a range, and let the code filter out all the unique value once the range has been selected.
The following is my code :
[Code] .....
This doesn't work, it throws the 1004 run time error and says "Method 'Range' of Object '_Global' failed.
I have a column of data that i would like to filter using a macro and place the unique data in a separate column as completely independent data.
View 3 Replies View RelatedI have a very basic table of customers. In one column there is duplicate data. I guess in most cases an Excel user would only want to filter the table to show the unique records only. In my case I want to do it the opposite way round, to delete the unique records so I have multiple occurrences of strings that appear in that one column. I've used the "Conditional Formatting" trick, which is great = COUNTIF($G$1:$G$44000,G1)>1 highlights all of the strings that appear more than once. What I'd like to do from there though is to either just have that data, and to remove the unique records.
Either that, or.. is there some way to have a field/column which shows "True" or "False" if such a string has appeared more than once in a column. Auto Merged Post;I forgot to mention.. the reason I'd want a column of "True" and False" would be because then I'd have the ability to sort/group the data into all the recurring records and all the unique ones. I'd then be able to do away with the unique ones by just copying the recurring ones.
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.
1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.
2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
I'm using Excel 2003 and am trying to filter a list of values. The list has duplicate values in it. When I use Filter>Advanced Filter and select "Unique records only" the filter only eliminates some of the duplicates. If there are 4 values of 100, it may filter 2 of them or none at all. Ultimately I'm trying to end up with a list of values with no repeats.
>I think my issue is best displayed with an example:
>
>Here's my rows of data:
>
>Column A
>Bob
>Dave
>Dave
>Eric
>Simon
>Simon
>Simon
>Simon
>Tom
>etc....
....
I'll assume this is in Sheet1!A1:A10000.
>Is there a formula that can return the following:
>Bob
>Dave
>Eric
>Simon
>Tom
>etc...
....
I know how to use an Advanced Filter to sort for Unique Records Only and copy them to a new column, but I am looking for a way to do this automaticly everytime I update my worksheet.
I have a worksheet that populates an e-mail distribution list based on what you imput. Some e-mails are duplicates and I would like to eliminate them automatically before I Concatenate them into a single cell.
I imagine this could be easily done using VBA, but I am not firmiliar with writing any code so it is above my head.
i need to use the advance filter=>unique records only feature from my macro... how would i do tat? i have 3 columns... column A has records which are repeated... column B and column C's values for a corresponding column A's value are the same...
A B C
a 3 6
b 4 7
c 8 9
d 1 2
a 3 6
b 4 7
.... and so on...
i need to use tat feature so tat i can filter column A alone and then copy column A, column B and column C's value to columns E,F and G...