I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.
I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!
I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:
Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.
I have two lists of components, a component from List A is added to one from List B and a total in £'s needs to be shown, simple enough I hear you say HOWEVER the LENGTH of component B is variable.
For example
Input part # ABC in A1 and £50 is shown as a total in D1, then input part # 123 in B1 and the length of 100 in C1 and the total changes to £100. Then if you change the figure to 200 the total changes to £150
I did a search & couldn't find anything related to my question: If I have a range of known cells (A1, A3, A5, for ex.) & want to find which cell has the largest value & then return the cell of that contains the largest value (not the value itself), how do I code it it VBA?
View 4 Replies View RelatedI want to find the largest cell in a column so I can use “auto fit selection” without cutting off type.
View 9 Replies View RelatedI have an excel sheet that will create a standard filing system.
in a folder there are folders numbered like this:
"ENQ 0123, ENQ 0124" and go up 1 digit at a time.
there are hundereds in this folder. I would just like to know if there is a quick way of finding the latest one so i can then tell my code to generate the next one.
I am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
Any way to determine which is the best selling single of that week and print a 1 in the row associated with that CD.
View 1 Replies View RelatedI have lists of numbers in a columns and would like to elliminate high outlier values in each column. Each list is different so there's no way to just set a max value and say that any value above it is an outlier. What I'd like to do is to average the top 5 or so max values for example, and then say that any vale above this average is an outlier. So a formula such as =average (max1,max2,max3,max4,max5) would work if it's possible to derive max1, max2 and so on. Or maybe there's a different way of accomplishing this?
View 3 Replies View RelatedHow can I get the largest number in this list by using a function?
View 4 Replies View RelatedI have created a monitoring spreadsheet for a public School system. It pulls data from 5 seperate excell files. however when a lookup value that is further down the list on the largest table it returns no data. other cells can lookup that same number and return correct information but from a different table.
The table that has issues is A1 - Y12430.. if the lookup value is low in the first few thosand rows it works.. but if thye value is in say row 9,000 it can not locate it..
I have attached the worksheet i am currently working on -snapshot of a 2.9 mb file :-).
I have a table i need the information compressed based on the values being greater than '0'. The original table Column A-E runs down 15000 rows but i am only interested in the values that are greater than '0'. I would like to see G3 to contain the first cell from A to contain a value greater than 0, G4 to contain the next sell with a value greater than 0 -- so on and so on.
This method will apply to columns G-K, their may be another formula that i am unfamiliar with that will get this to work.
I have already tried to play around with INDEX and MATCH, i think i am on the correct path but i am not looking at something the right way.
I have a table showing interest levels in training courses from a group of schools, eg:
English Maths Science
School1 3 4 2
School2 7 1 0
School3 3 2 5
I want to identify the column heading for the first, second and third most popular courses. ie for School1 the most popular course is Maths, second most popular is English and so on.
I have tried using the OFFSET function, which worked if I provided the cell location of the required value. I then looked at the ADDRESS function to provide the cell location: eg For School2 find the 2nd most popular course:
=ADDRESS(ROW(A3),COLUMN(data?)+MATCH(LARGE(B3:B5,2),B3:B5,0)-1)
But I have got stuck with what I should enter for COLUMN(data?) as I do not know in which column the second largest value is.
I am sure Excel has the required functionality.
What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.
I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.
I have two lists of product data, one for buyers and one for sellers (these are listed as A, B, C). The product names are not exactly the same (Eg Playstation and playstation three should be matched), I would have thought using the FuzzyLogic add in to match these would be the way forward! I need to rank the sellers by how many of their items appear on the buyers list
View 8 Replies View RelatedINDEX($D$19:$D$22,MATCH((LARGE($E$19:$E$22,1)),$E$19:$E$22,0))
I use this formula to give me a "name" associated with the largest number in a list. But I can't get it to work when I use it on a win/loss number such as
"7-1". It won't read it.
I'm wanting a function to display the largest number starting with 7,8 and 9.
I have a list of 4 digit numbers, beginning 7,8, or 9, and want 3 different cells.
I need:
One displaying the largest number in the group starting with 7.
One displaying the largest number in the group starting with 8.
And one displaying the largest number in the group starting with 9.
Could I make use of an IF function and the MAX function?
Something like:
M20:
=IF("1st digit = 7", "display the largest of the list")
M21:
=IF("1st digit = 8", "display the largest of the list")
M22:
=IF("1st digit = 9", "display the largest of the list")
I've used =MAX(H5:H75), but this always displays the top "9" number.
I want something so I can show the top 8xxx number, and the top 7xxx number too.
On Sheet 1 I have a list of employee names (John, Bob, Ross etc...)in column A and in column B I have a list of employee bonus points (1, 5, 3 etc...). On Sheet 2 I have the same setup but the list of employees on sheet 2 is a lot longer than the ones on sheet 1, all employee names are on sheet 2.
I need a macro that will go down to each name on the list on sheet 1, column A, and copy the employee bonus points then go to sheet 2 and find the that employees name in the list and paste the bonus points in column B. This must be done until the last name on sheet 1 is found and all points are copied to their corresponding names on sheet 2.
I keep getting this error message: Error 429: ActiveX Component Cannot Create Object
I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic."
But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.
I have been deleting some VBComponents by using the .remove command and they get tagged for deletion but do not actually get deleted until after my VBA sub ends and control is returned to excel.
I would like to save the changes before control is returned to the user. I'm not really sure why the deletion is being held up until execution ends, it even waits until all calling functions have also finished their execution.
I'd like to find out the formula where numerical valued between 1-2000 equals True and numerical under 0 equals False. Examples are in the attachment.
View 3 Replies View RelatedI have this nested if statement, but my last IF statement needs to incorporate another component.
View 2 Replies View RelatedWhen I try to open an Excel-file from my employer with Excel UserForms I recieve following error messages
Code:
Component not correctly registered
and
Code:
Unexpected error (336)
I can see the code behind the UserForm, but not the UserForm. I've made the same mistake myself last time I created an UserForm, but I don't know what to do different.
I have a control sheet that needs to fetch numbers from a different worksheets depending on the worksheet name that I type into a cell. So if I type "sheet1" into cell A1 then I want the formulas on the sheet to automatically change fetch the data from sheet1. Then if I type in "sheet2" for example, the formula must then look at sheet2 for example. The source sheets are all identical in layout so not bothered about the actual cell ranges. I can do this in VBA but would like to keep this simple in Excel formulas.
View 2 Replies View RelatedI am creating an interactive tool in Excel.
One of the Fields is "Employee Name"
I created a validation list that includes the names "Mary, Joe, Michael, and David"
When I plug in the name "Mary" I want to pick out her qualifications from a list I have created on a separate worksheet.
Passport
Mary
Joe
David
Lunch
Mary
Joe
Michael
Car
Joe
Michael
David
I want Excel to have the ability to find the name "Mary" from those three lists and reply back to me with the headers of either Lunch, Car, and/or Passport based on the lists Mary appears in. I want this to change depending on the Employee Name and I pick and the lists they appear in.
I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.
I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.
I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.
I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.
I have a spreadsheet with the first 17 tabs containing data and the last tab the 18th one contains a list in col a of 4,000 unique code items.
Is it possible to check if any of the codes items exist in any cell of the first seventeen tabs.
Excel 2002
I have a number (22.3) in a cell that is there from an Hlookup function. I am then using a vlookup to lookup the 22.3 (from the hlookup) in a column. The number is in the column, but the vlookup function cant find it. If I were to type over my hlookup formula so that I am hand-typing in "22.3" instead of it being their from the hlookup, then the vlookup works just fine. I hope that is not too confusing. I dont understand what is going on. I checked to make sure none of my cells are rounding and therefore creating a look-alike number when it really isn't and that is not the case.
I am trying to generate excel report output from access by click of a button. Part of the code is as follows:
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xlc As Object
Dim DBS As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
'If we do not want the first row we set Header to false
blnHeaderRow = False
'Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having
the following error in the line
Set xlx = GetObject(, "Excel.Application")
Run-time error '429'
ActiveX component can't create object
Following bit of code runs fine if placed in Word VBA:
Code:
Public Sub TaskUsageInExcel()
Dim tsk As Task
For Each tsk In Tasks 'Gives RTE 429 when run through Excel
Debug.Print tsk.Name
Next tsk
End Sub
So I referenced 'Microsoft Word 12.0 Object Library' in Excel and ran the code and it gave me this error 429. Noticeable part was, no instance of word was running at that time.
So I modified the code as below:
Code:
Public Sub TaskUsageInExcel2()
Dim wdApp As Word.Application
Dim tsk As Task
[Code]....