I need to create a "Top 5" list based on two columns. The example is as attached (the real list is approx 22 000 lines):
I need to find the top 5 largest quantites (column A) for each flag catagory (column D) and include the part number and description. I have tried a pivot table and had some issues. Also tried a LARGE(IF...) type formula and still no luck. Does anyone have any ideas of what is the best way to do this?
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies View RelatedI am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
I am looking to have one formula containing two If Statements that minus.
=IF(B6="investment",C6,0)-IF(B7="gross",C6,0)
So the result of If Statement 1 minus the result of If Statement 2.
What I am seeing at the moment in the cell is FALSE and what I want to see is the sum.
If A1 shows 10:00am and A2 shows 4:00pm, then A3 calculates the total number of hours: =(A2-A1)*24
But if A1 shows "Off," then A3 shows 0: =IF(A1="Off",0,(A2-A1)*24)
Now, if I want to change "(A2-A1)*24" to another if-statement, how do I do this? I can always set up a hidden cell (A4) that contains the results of the first if-statement, and then say: =IF(A1="Off",0,A4). But can I do this without going through all the trouble of setting up hidden cells?
Each row represents a call. If a call in column A equals "CW" and it has the highest duration (H:MM:SS) value in column B, then provide me the date (MM/DD/YYYY) for that call that is stated in column C.
i.e.
Column A --- Column B ---- Column C
AB ------------ 0:02:22 ----- 04/14/2007
CW ----------- 0:03:13 ----- 04/16/2007
CW ----------- 0:01:42 ----- 04/13/2007
Thus, the value that should be returned is "04/16/2007".
I have a problem with a formula inserted with control+shift+enter
The formula is
MEAN(IF(' VALIDATION'!$G$2:$G$59999=D30;'VALIDATION'!$E$2:$E$59999))
In the Validation sheet I have the column G with values 0 and 1, and another column E with numeric values.
Changing the value of cell D30, the formula will calculate the mean of the values in column E that have a value equal to D30 in column G.
The problem is that this function works when D30=1 and not when d30=0, in this case the formula returns N/D.
I have tried using also text values instead of 1 and the formula works . The problem is only when D30 is 0
I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."
(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:
=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")
(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.
I have 5 sheets all with different data within the H column. I want to find the top 10 highest results across all 5 sheets and then pull the worksheet name where those results come from. It would be two separate formulas, one for the LARGE value and the second for the name.
This is all I have.
O2:O11
=LARGE(Sheet1:Sheet5!$H$3:$H$102,1)
to
=LARGE(Sheet1:Sheet5!$H$3:$H$102,10)
I then have this which was the best I could find after about 30 different formulas.
=MATCH(TRUE,COUNTIF(INDIRECT("'"&{"Sheet1","Sheet2","Sheet3","Sheet4","Sheet5"}&"'!$H$3:$H$102"),O2)>0,0)
It gives me the right worksheet but it only gives me a number. I'm assuming it's because of COUNTIF but I'm not sure what else to use.
Most examples I see have the names they want right beside the values. So in my case H3 is 22 (and the largest value within H across all sheets) and if I followed their examples G3 would be Sheet1. So because it's side by side it'd be easily pulled. But the only name references I have are the worksheet name itself or A1 of Sheet1:Sheet5 so I'm not sure how to rework their formulas to suit mine.
I'm trying to create a mini-table that will give me the 3 highest and lowest values in a range (I know how to do this using LARGE and SMALL functions), but I also want to get the corresponding name (in column A) for each number. This last part I don't know how to do.
I've attached an example sheet that I hope gives a basic idea of what I'm trying to do. I'm looking for a method to fill in the data for cells A13:A15 and C13:C15 in the attached sheet.
I could modify my worksheet a bit and probably get the result I'm looking for, but I'm hoping to come up with a nicer solution, and hopefully learn Excel a bit better in the process.
I don't know if this is possible but thought i'd check. Could I put together a look up that pulls through the 5 best performing categories' so say i have 200 categories, just to pull on to a report page the top 5 and their result, and produce a report weekly so if the top 5 change it updates this?
View 2 Replies View RelatedI am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?
View 4 Replies View RelatedI was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx
I have a set of 5,800+ data points between 0 and 1 that I would like to multiply together. When I use PRODUCT for the whole set, the formula returns 0. However, I can use a smaller subset of the data to return a very small number. I'm curious if Excel has a closest-number-to-0 or number-of-cells-for-PRODUCT limitation. Is there another way to perform this calculation?
View 4 Replies View RelatedSay I wanted to concatenate a row that had 10,000 values in it. How could this be achieved without having to click 10,000 cells while typing a comma in between each one?
View 5 Replies View RelatedIf I have the numbers 1-50 repeating in columns, 1-50, 1-50 etc is there a way to determine that number "1" falls 1st, 51st etc in the sequence without doing a manual count? Or that the number 2 falls 2nd, 52nd etc?
i have this formula below which works but if the criteria is not there it says FALSE because of the B22:C45,2,0) bit i would rather it says something like "no dissertation" is that possible? ...
View 10 Replies View RelatedI have a column of numbers in A1:A200 that I need to concatenate with a ";" in between each into one cell. Is there a way to do this without clicking in each cell individually? =concatenate(A1:A200) just returns the value in cell a1.
View 2 Replies View RelatedI'm having a difficulty using LARGE and SMALL.I want to return the 1st to 5th value of an array (each in a different row) choosing months as criteria. While the LARGE formula works fine, when I replace the LARGE() formula for the SMALL() leaving everything else unchanged, the formula returns ZERO and there are no ZEROS in the original data! Here is the function:
View 4 Replies View Relatedmy colleague did something to one of our files and it is now 845918 kb. I transferred it to a flash drive and then to my hard drive but now I cant open it to see what is wrong. Is there a trick to opening this up? When it was opened on her computer it wouldn't let me add any tabs or move tabs. I think I unprotected it but that didn't change anything.
View 1 Replies View RelatedI have a list of domains (about 200,000) that looks like this:
I have a list of urls (about 1 million) that looks like this:
I want my results to only show this (since this is the only thing in the second file that does not contain anything from the first file):
I have tried programs designed to do this but they either (a) shorten everything down to the domain or (b) wont remove anything since the domains are part of a longer url in the second file.
I am using following event macro and it repeats itself more than 300 times. I am at a stage where I am getting a message "Compile Error, Procedure too large", I tried to break it into two but that's not working.
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cl As Range
Dim lng As Long
Application.EnableEvents = False
Select Case Target.Address
Case "$E$3"
If Target.Value = "Yes" Then
SelectNumber:
lng = Application.InputBox("Please enter number 0 to 100", , , , , , , 1)
If IsNumeric(lng) = False Or lng < 0 Or lng > 100 Then GoTo SelectNumber
Range("G3") = lng
Else: Range("G3") = 0
.................................................(above statements repeat more than 300 times for different cells)..............................
Application.EnableEvents = True
End Sub
I'm using Excel 2007 and the sheet has 238536 rows containing 26412 ranges.
The first 5 rows in each range contains data. I wish to input into the sixth cell of every range a formula. Not essential but I'd prefer it to be formatted blue.
I attach short s/s to illustrate my need.
I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.
In a new tab I want to formulate the total of ProductA on day 240.
A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5
In another Tab summary of Product Totals:
Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15
I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)
i was wondering if there was a way i can combine these two formulas into one large one
=IF(AF8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($L$3:$L$103)-ROW($L$3)+1)/($L$3:$L$103=AF8),COUNTIF($AF$8:AF8,AF8))))
=IF(AG8="","",INDEX($A$3:$A$102,AGGREGATE(15,6,(ROW($M$3:$M$103)-ROW($M$3)+1)/($M$3:$M$103=AG8),COUNTIF($AG$8:AG8,AG8))))
I have a list of IDs that recurs over a time period. It consists of a Start Data an ID Number. These IDs recur over and over again through one month with different start dates.
See Below:
Date ID
12/1/2013 10:00:00 AM 67890
12/6/2013 12:00:30 PM 67890
12/18/2013 06:30:05 AM 67890
From Another List I'd like to pull a max enddate that is within 24 hrs of the start date. There will be multiple end dates. Here is what the other list would look like.
End Date ID
12/1/2013 1:00:30PM 67890
12/6/2013 4:00:45PM 67890
12/18/2013 9:30:00 AM 67890
Seems like using vlookup with the ID as the lookup wouldnt work because it would just pull the first date it found over and over again.