I have a report I am producing that returns some information regarding accounts we deal with. it may be best to explain what I have allready. Firstly I have The total occurences in a specific month and year using the following formula.
View 4 RepliesI have this formula in E19: ...
View 9 Replies View RelatedI tried several iterations of sumproduct, small, and few index matches. no luck.
Several thousand rows, column A is name, column B is city, column C is $.
I want to see the max, min, median for all first names.
=SUMPRODUCT(MAX((Name=Joe)*($))) - seems to work fine
=SUMPRODUCT(min((Name=Joe)*($))) - will not work, zeros come through, even if there are no 0's for Joe
=SUMPRODUCT((Name=Joe)*(SMALL($,1))) - wrong answer
I have a table that contains various aspects of information about customer cases, and I want to replicate a user 'picking up' the case by a simple press of a button.
Users have access to only one Country, so I want to be able to search a particular column for the lowest value, but check that the Country for that row matches the user's access. If it doesn't, I then want to find the next lowest value in the column, and this is what's perplexing me??? As mentioned, I want to click a button to trigger this, and therefore want to use VBA code.
I have a table of dates and values and would very much like to know a formula for returning the min and mean values for each column within a specified time (date) period.
Table is as follows
Date value(a) Value (b) ....
1/1/09 10 8
2/1/09 8 6
3/1/09 5 2
I am trying to write a macro that returns specific results from a longer list based on an entry in a cell.
For example if I have “Colours” typed in cell A1 in Worksheet 1 and there is a list on Worksheet 2:
Colours Black
Colours Yellow
Colours Green
Animals Dog
Animals Cat
Animals Tiger
Then I would like the blow answers returned in cell B1 on worksheet 1
Black
Yellow
Green
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
View 3 Replies View RelatedI want in A1 to find minimum SUM if no minimum number in row.
Here is example attached: ...
I have been trying to solve this problem for the past couple of weeks and I have not been able to do so. I have come to do the operation manually, and it really takes a lot of my time, so if you guys could help me, I would really appreciate it.
Basically the problem is this:
In column "A" I have what I call point numbers for all the data entries.
In column "B" I have the station (or location) at which I vary slightly a parameter (represented by column "E") to give me a value shown in column "D" (the "Z" value, as shown in the sheet).
So basically you can see that my station 0 has 11 different "E" parameters (0, -1, -2.62, -5, etc) and each of them has a "Z" parameter (390.76, 390.775, etc).
What I am trying to do is to come up with a table that shows a summary of the following:
A column with each of the stations (0, 20, 40, etc -- notice that I have some weird numbered stations like 411.89).
Another column with the "E" parameter corresponding to the lowest (or minimum) "Z" parameter within the station range.
And a final column with the minimum "Z" parameter corresponding to its "E" parameter, which is included in the station range
You can see in yellow the way I have been doing this and you can see that it would take me so much time to accomplish the task.
I'm looking to use MIN to look at a range of data in L:L based on matching certain criteria in A:A. In the attached spreadsheet in Totals!C1 I would like the MIN value from List!L:L for user "CHRISTOPHERJ" in List!A:A
View 3 Replies View RelatedPlease see attached file which is data for horse races (this is a small example of the data i will be working with). I need to find the largest values from the MIN and MAX column (shaded Grey) range for each race. I want to be able to add a function that will allow me to quickly identify which horse has the largest number in both the MIN & MAX columns example on spreadsheet is highlighted RED. It would be handy if it would inform me if the criteria has been met by highlighting it or by placing some text value in an adjacent cell on my spreadsheet i have used the example "Y".
It would also be beneficial if that race be deleted in its entirity if there are no horses which meet the criteria above or similary if all other selections within that race be deleted if ther is a horse which meets the criteria. If there is a selection within that race i will need to keep the row that includes the heading which is coloured blue on the attachment.
I will be working with 20,000 or more selections at a time so it is important that they can be identified quickly for ease of use.
I have tried the functions i know such as MAX and LARGE but this requires me to do alot of work when you consider the ammount of data i'm required to work with.
I'm having an issue right now with an employee list that I am working on. What I am trying to do is determine the earliest time that an employee signed in, but the list that I can export may have the same employee on it multiple times in a day.
Here is a sample of the data that I'm using:
Emp No. Time In
9900872 9:00am
9900874 9:03am
9900874 11:00am
9900874 11:45am
9900875 10:15am
9900875 11:45am
What I want to get is the minimums only, so that the list would show:
Emp No. Time In
9900872 9:00am
9900874 9:03am
9900875 10:15am
I know how I could do this in VBA, however I have everything else working without using VBA, so if possible I would like to leave it that way. Also, I do not know if it would matter to the solution, but the employee numbers are always sorted numerically, although their clock in times are not.
A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateInterest rateweekday 24-Jan-889.871 35-Jan-889.992 46-Jan-8810.053 57-Jan-8810.094 68-Jan-8810.135 711-Jan-8810.051 812-Jan-8810.072 913-Jan-8810.283 1014-Jan-8810.114 1115-Jan-889.985 1218-Jan-889.871 1319-Jan-889.962 1420-Jan-889.913 1521-Jan-889.864 1622-Jan-889.715 1725-Jan-889.71 1826-Jan-889.762 1927-Jan-889.693 2028-Jan-889.564 2129-Jan-889.555 221-Feb-889.741 232-Feb-889.622 243-Feb-889.643 254-Feb-889.684 265-Feb-889.755 278-Feb-889.931 289-Feb-889.852 UK interest daily [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
Daily At the end of day We receive sales files from our different teams, these files are received at different times due to difference in working shifts. I have collated last one month data and from this data I want to know the earliest and maximum time and date on which we received the files in last month, so that i can fix the receiving time for all regions.
View 5 Replies View RelatedI'm a big user of sumproduct but i have tried many variations on the following
=SUMPRODUCT((R9:R14="Sales Revenue")*(S9:S14)) without success.
If the data is clean ie does not contain # div/0 then i can get a fine result.
With a #div/0 then the result is #div/0. Can someone please tell me how to exclude #div/0 from the calculation please. I have searched several boards and cannot find an answer, but i'm sure its simple.
I have had a lot of luck finding what I need from the search areas, and I even found some information on the formula I am trying to build. The problem is I don't understand it and I need some help. First let me set it up for you. (I do not know the formula)
If cell L125 is has a value >0.00, I need to locate the smallest value the range of cells C125:F125, I then need to subtract L125 from that number, otherwise enter nothing.
This really has me baffled. I tried and I tried but it will not find the smallest value then subtract L125.
In my current worksheet, IF(0 = "CS") returns TRUE, but on any other file this same argument returns FALSE. The cells' formats are both general. I want it to be FALSE.
View 2 Replies View RelatedI have a cell that contains several carriage returns. How do I extract data from between 2 carriage returns?
View 5 Replies View RelatedThis code (partial) was written to interrogate a database. Cell "A2" of another tab contains a Part Number to be sought.
Target = Range("A2")
Sheets("Kanban Data").Select
Dim Nrow As Range
Dim N As Integer
On Error Resume Next
With Sheets("Kanban Data")
Set Nrow = .Columns(2).Find(What:=Target, After:=.Cells(3, 2), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If the Find expression (as pulled down from the Edit menu) is empty, then inserting a Part Number in A2 and executing the macro finds the correct record and returns the its correct row number (N, as defined afterwards in the code). Then, changing the Part Number in cell A2 and executing a new search returns the SAME row as the previous search, despite Target being equal to the new sought after value.
Macro that will look for #N/A in Col A and if found then msgbox returns the data in Col B, so as example shown below we can see there are 2 #N/A, so the msgbox should return "DAI.345, BBHT.WERFGT".
my real data will consist around 800 rows....
I have a sheet connected to external SQLSERVER data.
One of the columns includes a formula, but the formula is not evaluated but shown as text.
My challenge is making it evaluate (without using excel tricks like replace equal sign with equal sign or clicking each cell and approve it)
I tried going down the road of concatenating char(10) or char(13) to the formula string but no success.
I am using the following code to re-name filtered data in column 4 of my worksheet, but I am running into an issue when the Autofilter returns only one row of data. The End(xlDown) is selecting a range that extends to the last row of the worksheet (1,048,576).
Code:
rfiltered.Cells(1, 3).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Receivables"
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
How can I write the code so that in the event there is only one row of data, it selects just that row for the paste (and yes it would be redundant to paste over the same value) and then continues on? I would usually use a Cells(Rows.Count, x).End(xlUp).Row to find the last row of data, but I don't think that will work in Autofilter mode.
I have a three or four letter abbreviation of a last name in A1. Column C contains a list of Last Names; column D contains a list of corresponding First Names; and column E contains a formula concantenating the First Names and Last Names from columns C & D.
I have the following formula in B1:
=VLOOKUP(A1,C1:E44,3,1)
I am attempting to match up the three or four letter abbreviation with the concatenated list to return the full name (First and Last) in cell B1, however, I'm not getting the desire result. I keep getting the name in the row just above the name I want.
If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.
The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.
I 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 setup this function to pull my staff's schedule an auto generate the forms i need however the lookup function works properly sometimes and improperly others i checked the syntax and it is correct how ever the wrong data is continually returned. I have attached the file if anyone can help. the VISUAL PLAN tab is the one contaning the formula and it pulls data from PLAN, CURENT SCHEDULE, AND CATAGORY SUMMARY however the only formulas hat i have a problem with are the ones referencing CURENT SCHEDULE.
View 9 Replies View Related Is there a way to delete excessive carriage returns at the beginning of data. I pull reports from a SharePoint list where submitter entries often often contain multiple carriage returns before the text or data. I use ASAP utlitily to clean out excessive spaces before and after data, but I don't find a way to remove these multiple carriage returns so that the text entries are easily viewable when row-height is less than gigantic. Cells with extra carriage returns before the text entries end up looking like the following:
___________________________________________________________________
| |
| |
| |
| |
| Product page for CQ1-14047.LA is missing Swindex section (Core Drivers). |
|__________________________________________________________________|
a
b
c
d
1
monday
tuesday
wednesday
thurs
2
ted
bill
frank
mary
[Code] .......
We use excel for scheduling purposes and each and every day we fill out a floor chart with who is working that day. As it stands now we have to print out a template and hand write all names or type them in and print it out. What I would like to try and do is define each set of names and allow for easy access to auto fill these names. I feel it needs to be a simple process as not all of our managers experienced in excel or formulas. What I would like to try an accomplish is in sheet 1 cell A1 to be able to create a drop list or formula that that would let me return all the names for a given day.
For the purposes of this discussion we can just define each list as the day that is the header ( IE A2:A4 on sheet 2 would be defined as "monday". Also, I already have the names autofilled based off of what the our schedule has. I'm curious if I could create a drop down list with each day of the week as an option, and if they select Tuesday it will then return all the names defined as Tuesday in a column below.And the next day be able to return Wednesday names and so on.
I'm not stuck on it being a drop down list. Maybe if they could just type and =(defined name) in the box above and it would return all the names.
I have tried an =(defined name) and it does return the value but only for the first cell and it has to be in the same row for it to return the name. Which doesn't work because I would like the names that are being defined on a separate sheet.
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?