Filtering Highest Revision Number For Individual Products
Apr 16, 2013
What's the easiest way of filtering a list of products to only show the highest revision for each product, preferably without using macros or VBA?
E.g. I only want to show the asterisked rows in the table below:
Product Rev
AAA 1
AAA 2
AAA 3*
BBB 5*
CCC 3
CCC 6*
DDD 1
DDD 4
DDD 7*
Potentially, other (random) data could also exist in the same row.
The example above is sorted alphanumerically but a solution for an unsorted table would be even more useful.
View 7 Replies
ADVERTISEMENT
Feb 4, 2014
This is an example of my data, lets say they are heights of trees. The number is the tree individual, t2 is the age of the tree, height_t2 is the height of the tree at the age t2, and t_ current is the age of the tree individual at present/current. I would like a formula for the t_current column that will pick out the current age of the tree individual and apply it to the age years previous. i.e the t_current age should be repeated for each height measurement for that tree individual, i have filled it in as an example.
Numbert2(age)Height_t2t_current
11236
12486
13706
14836
15926
161006
21286
[code]....
View 3 Replies
View Related
Apr 23, 2012
I need a formula which ignores duplicates and uniques in an array, which returns the number of individual values.
A comma denotes a new cell;
1,1,2
3,3,4
5,6,6
In this case, the answer should be "6".
View 3 Replies
View Related
Apr 13, 2014
I am working on my project for VBA and I need to make a program for Large Factorials sch as 25!, which is too big to fit as a Long-integer. I basically have to find a way around this by storing the numbers as arrays F() and C(). Array F is for the multiplication of each cell of memory, and C is for the carries, which will later be added. I need to separate the hundreds, tens and units of each multiplication.
To Further explain, lets say I have 12!; to find this I would take the previously calculated 11! (by using a loop) and multiply 12 by each memory cell of the array F(). So it's kind of like multiplying by hand, you bring down the units and put the carries on top to be added later. I would store the carries as one or two memory locations over of the current I, or the current loop number. So the units of the mult. would be stored as F(I), the tens would be carried and stored as C(I+1), and the hundreds would also be carried, but this time two cells over, so C(I+2)
The main problem I have is finding the proper code to split the number into units, tens, and hundreds, and then assigning them to their appropriate memory cells within their respective arrays.
Here's what I have so far:
Code:
Dim F(1 To 30), C(1 To 30), N As Integer, Fact As Long, Length As Integer, space As Integer
Sub LargeFactorialCalc()
Open "F:project.txt" For Output As #1
F(1) = 1
N = InputBox("Enter the number you would like to take the factorial of: ")
[Code] .......
View 3 Replies
View Related
Sep 7, 2003
I'm trying to run a number of individual web queries and would like to paste them one under the other in worksheet2.
Sub ImportData()
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet1").Range("D11"), Destination:=Sheets("Sheet2").Range("A1"))
.Name = "yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False.....................
View 9 Replies
View Related
Jul 21, 2005
For thos of you who use Excel as a drawing tool (painful I know), have you ever created a macro, vba script, anything to draw a revision cloud around certain areas? I know there is an autoshape cloud but thats worthless for this type of application.
I have searched a little and haven't found anything. . . . I figured I would ask here b/c collectively y'all know everything
View 9 Replies
View Related
May 23, 2014
I want to get highest number from filenames in a folder.
for example below are files in folder
AB-1234-001.plt
AB-1234-002.plt
AB-1234-003.plt
AB-1234-004.plt So I want to get "004"
XY-12345-001.plt
XY-12345-002.plt
XY-12345-003.plt
XY-12345-004.plt
----
----
XY-12345-075.plt
XY-12345-076.plt Here I want to get "076"
file format is same but some times 4 digits in middle (AB-1234-001.plt) and some time 5 digits (XY-12345-001.plt)
My idea is , counting files ignoring suffix (eg 001,002,003) and count howmany are in folder. So code counts based on AB-1234 and return me value how.
View 3 Replies
View Related
Aug 15, 2007
I'm using this CF to show me the largest number in a range;
=F10=MAX(F$10:F$33)
I now want to also show the second largest number in the same range.
View 9 Replies
View Related
Jul 22, 2008
I have a few rows in an excel sheet and I want to highlight the highest number(s) in each row.
I've been playing around with conditional formatting, and using the formula:
=$C23=MAX($C$23:$C$55)
in the conditional formatting box and selecting a formatting type, I can automatically highlight the highest number in column C (row 23 to 55). However, this formula only seems to work when I'm selecting columns of data rather than rows. If I want to highlight the highest number in row 23, the formula I'm using is:
=$C23=MAX($C$23:$M$23)
Which I think should highlight the highest number From C23 to M23, but it doesn't - nothing happens.
What am I doing wrong?
View 9 Replies
View Related
Aug 17, 2008
in cell A1 i have the balance. i would like to record the balance every time it reaches a new high in cell B1. how would this be possible please using excel 2007
View 9 Replies
View Related
Oct 26, 2006
I have a table of information which is sorted in ascending order. I have created a vlookup to find the highest value as close to 1 as possible.
Say it returns the value 0.95.
i now have another cell which i need to find the second highest value from the column of data.
however i dont know how to do it. because the second vlookup function just returns the 0.95 all the time and not the second highest value..
E.G.
Column A
0.70
0.75
0.81
0.84
0.87
0.90
0.95
So in the cell with the first Vlookup i get the value 0.95 returned.
I need then for the second cell to return the next highest value in this case 0.90.
Then third cell third highest
fourth cell fourth highest value etc.
View 9 Replies
View Related
Feb 20, 2007
this is a formular that i used to divide
=IF(Z6=TRUE,K6/MID(D6, FIND("1=",D6)+2,LEN(D6)-FIND("1=",D6)-1),K6)
the problem i have is that if i have 14 in cell k6 and 1=10 in d6 the answer i get is 1.40 what i need the formular to do is always round it up to the highest number
ie 2 not 1.4
example 1=10 20 answer 2
1=10 25 answer 3
1=4 11 answer 3
1=4 20 answer 5
View 2 Replies
View Related
May 31, 2007
I have a dilemma. What I want to do is: If I input a date in a cell I want the adjacent cell to take the next highest number from a list on another worksheet. My example attached
View 3 Replies
View Related
May 4, 2009
I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?
View 3 Replies
View Related
Apr 7, 2014
My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...
Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..
When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of
Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x
I had
Code:
Combobox1.list = x
And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).
View 2 Replies
View Related
Mar 17, 2009
I have a spreadsheet that references another spreadsheet, however the other spreadsheet will get updated from time to time. When it's updated the file name changes to reflect the revision:
"C:Projects9-022_Tunisia1_Design9-022-R00-Inst_Database.xls"
When this file is revised the "...R001..." will increment with each revision but the rest of the file will stay the same.
Sometimes I have to reference a file in another directory:
"C:Projects9-030_Brazil1 Design9-030-R00-Inst_Database.xls"
I was planning on placing information in the following cells of this spreadsheet ("C:Projects9-022_Tunisia1_Design9-022-R001-Cause_&_Effects.xls):
Cell A1 - Project Number (i.e. 09-022, or 09-030)
Cell A2 - Project Name (i.e. Tunisia, or Brazil)
Cell A3 - File Rev Number (i.e. 1, 2, 3, etc.)
I was thinking my "INDIRECT.EXE" would look something like this:
=INDIRECT.EXE("'C:Projects"&A1&"1_Design["&A1&"_"&A2&"-R00"&A3&"-Cause_&_Effects.XLS]Index'!Q5")
The value on the spreadsheet tab "Index" that I'm referencing in cell Q5, is "FG-5901" but all I'm getting in the spreadsheet were this formula resides, is an error "#NAME?"
Part of my problem is that I don't fully understand the function of the "&" symbol in the formula. Sometimes I've seen it placed on both sides of a cell reference (i.e. &A1&), and other time only at the being (i.e. &A1).
View 6 Replies
View Related
May 26, 2007
I have the following macro which picks up a cell's value and then searches and replaces it throughout the whole workbook:
Dim sh As Worksheet, r As Range, c As Range, s, arr, x As Long
arr = Sheets("Exclusives").[b2:b250]
s = Array("Exclusive", "", "", "", "", "", "", "", "")
For Each sh In Sheets
If LCase(sh.Name) Like "*family*" Then
sh.Activate
For Each r In [b8].Resize([b65536].End(xlUp).Row, 1)
For x = 1 To 249
If r.Text = arr(x, 1) Then
r.Resize(1, 9) = s
Exit For
End If
Next
Next
End If
Next
I would like this to change so that it only identifies and changes the value on one worksheet - titled 'total'.
View 9 Replies
View Related
Aug 18, 2009
Our main spreadsheet in the office is a shared filed which is accessed by 3 pc's.
We have been using this set-up for nearly 3 years and no problems until this last week. Now, over the past week, this message pops up when we try to save the file on the PC's not hosting the file.
"Excel was unable to save the workbook because there was a problem reading or applying part of the revision history. You can unshare the work book or save to a non shared workbook format to fix the problem"
I have shared/unshared/reshared and shared again but a week later, I still keep getting this message popping up. Any ideas?
One thing I have noticed is that we have set it in the advanced tab of the share workbook options not to keep revision history, but sometimes it also shows 'keep history for 30 days'.
View 9 Replies
View Related
Apr 23, 2012
I have a spreadsheet with 5000+ rows. On one column there is a user number, the user number repeats few times. Therefore, there is a different number of rows for each user.
Also, the first line for each user has a date on a different column.
I am interested in filtering out the users who do not match a certain date range. The issue is that the code I found hides all of the rows without dates and also the rows that are with the range of dates.
View 2 Replies
View Related
Feb 6, 2014
I have a table with numbers in a column and various other data. I use this data to track over 30 and want to filter everything thats over 60. Currently I use compound COUNTIF to check 2 criteria using :
=COUNTIF('1SBCT'!F:F,"No*")+COUNTIF('1SBCT'!H2:H3,"")
But would like to an additional criteria in that it will count everything over 60 after with counts the 2 conditions above.
Example: counts how many have a No* or blanks and once those are sorted checks for over 60.
View 8 Replies
View Related
Feb 25, 2007
I have a list of names in one column and in the next column I have a list of numbers. The names and numbers go together. In anohter cell I would like to extract the highest number and the name related to it. The list changes often.
I would like to be able to put a heading saying that "name" (in anohter cell) made the highest score and the name is entered into a cell automatically.
View 9 Replies
View Related
Oct 9, 2009
column a has dates & column d has values - what is formula to find highest number in values and return it's date (using =large(Dxx:Dxxx, 1) to find highest number
View 2 Replies
View Related
Jan 28, 2013
I have a sheet, along the top is the Months of the year, to the side I have a list of products. At the bottom of each month in a cell I want to display the name of the product that has sold the most for that moth, is this possible:
I can display the highest number i.e Highest item sold in January was 108, but I want it to write the product name.
View 3 Replies
View Related
Feb 10, 2009
Formula: ......
I am looking for the value in column G, a formula that will search B-F and return the Group with the highest percentage for each zip.
View 3 Replies
View Related
Aug 12, 2006
I have name and a number in this
format: cell a1 Jones---56. In cell b1 I have another name i.e.,
Johnson---45. How can I subtract 45 from 56, take 60% of the difference
and in cell c3 show the following; Johnson---6. All numbers are to
rounded down to 0. The final result would look like this;
a1 Jones----56, b1 Johnson----45, c1 Johnson----6. I have other cells
with the same type of info in them. Example; a2 Smith----34, b2
Jones----78, c3 would show Smith----26.(rounded down) I always want to
subtract the lower number from the highest and take 60% of the
difference.
View 11 Replies
View Related
Jan 17, 2008
I am attempting to pull the Highest number out of a row of cells. Then grab the info 23 lines below. What I have is a spreadsheet that pulls from other spreadsheets and tracks all of my sales for the year. This is divided up into tabs for each day of the week. I am a formula to find the highest sales for Tuesdays {LARGE(Tuesday!B8:BB8,1)} then in the cell below it I need it to show the date that days sales happened on. That date is listed 23 lines lower. I have tried using {HLOOKUP((MAX(Tuesday!B8:B88)),Tuesday!B8:B88,23)}. This kinda works. It pulls from the 23rd line but only from the first column. Not the column that has the highest sales. I have searched for a solution but I must not be typing in my question correctly.
View 10 Replies
View Related
Dec 4, 2009
I have a data listed from A1 to G1. What I want to do is
Check out all the numbers written in those cells, take the highest number among and write to the following cell (lets say A3)
also
Check out all the numbers written in those cells, take the lowest number among and write to the following cell (lets say A4)
View 9 Replies
View Related
Dec 5, 2008
In sheet 1:
I have a list of customers and their current credit limit
In sheet 2:
i have a list of all payments received in the last 12 months.
my customers credit limits are set by multiplying their highest payment in the last 12 months x 1.25
(e.g. payment of $1000 x 1.25= limit of $1250)
what i want to do is:
1) look at the customers in sheet 1
2) check sheet 2 to see if they have made a payment
3) return their highest payment (if any) in sheet 1 Col. C
4) i can work out the rest
View 9 Replies
View Related
May 4, 2006
I have a whole number value in cell F18. In cell F24 I want to enter a formula that allows me to divide the value in cell F18 by 4 and if the result is not a whole number, round it up to the next whole number.
View 2 Replies
View Related
Aug 20, 2012
I am importing some data from a source which every time I just want to have latest revision of documents and I want it to be dynamic so that every time I import data the deletion would be automatic the data I import is something like this
DOC NO.
DOC DEScription
status
[Code]....
and as you see I have duplicate documents with different revisions and I want to have the latest last revision only.
View 8 Replies
View Related
