Dual Condition Lookup Over 20000 Rows
Apr 30, 2009
I have a 20000-row sheet generated by a daily report and I only want to work with about a quarter of this data; any rows that contain "PH4" in the M column. What's the best way to automatically single out this data to work with?
My initial solution was to create a seperate sheet to pull off all the columns with PH4 in the M cell, eg cell A8 would be:
=IF([WEB_StoSta_Rep.xls]Sheet1!$M8="PH4",[WEB_StoSta_Rep.xls]Sheet1!A8,"")
Then I could run lookups off that sheet. However, this feels like a sloppy solution to me, and Excel throws up an error when saving, unable to save external link values with available resources. Is there a better way of going about this?
View 10 Replies
ADVERTISEMENT
Jul 10, 2008
I browsed the first few pages of searching for "row delete macro" and couldn't find anything that suited my needs very much, so here goes:
What I need to do is automatically delete rows where the entries in column B (date) and column E (string) are the same. It is a very large group of data, so filtering is not exacly an option here.
Here is the twist on it...
I have a column F that contains either "Accepted-Active" or "Accepted-Closed". If I delete a bunch of rows that contain the same column B and E entries, I want to save the row that contains "Accepted-Closed", otherwise I don't particularly care which of the rows is saved.
View 9 Replies
View Related
Aug 13, 2009
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
View 6 Replies
View Related
Sep 1, 2008
I need a formula to workout what is the most up to date firmware that should be installed on the machine. This will always be in column B. The second formula should work out the variance between the installed firmware and recomended. The formula cells will be normally held on a different summary sheet.
View 2 Replies
View Related
Dec 11, 2007
I consider myself a beginning intermediate Excel user and am really learning a lot through this forum but have not run accross the answer to what I'm trying to achieve. I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet. It seems Vlookup is the approach to use in combination with multiple if conditions or an array. But I am not yet comfortable with such a complex equation. Can someone provide me with some guidance or an equation that would work. So far this is how I've been learning. Looking at equations and deciphering how they are written.
View 5 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Aug 21, 2008
Given three criteria (category, name and date) I want to find the specific value within the table or array. refer to attachment.
View 4 Replies
View Related
Jun 21, 2008
I have an imported report in a spreadsheet. It imports to three columns. I need to check each row in column A for three seperate criteria and delete the rows I don't need. I need to delete blank rows and check next row for page header info. Delete these and next rows to next blank cell. Check next row for page header and not delete if not page header. Several rows down will be a cell with 23 blank spaces before the word Reg: and sometimes other words past this but always this first. This row is to be kept. I looked at the FAQ's example of Deleting but I don't think it will work. I also need to put a key word in column A at a point where I want to stop. This report is a couple thousand rows long so a VBA procedure would really save time. I have a procedure I use to check for two zero's in two cells that hide these rows but I couldn't modify it to work on this report.
View 9 Replies
View Related
Sep 15, 2014
I know how to format a cell so that if I type 5 it will say: 5 SF And then in another cell if I type 15, it will say: 15 Units
Is there any way possible to format a cell so that if I type (its actually calculated via formulas) these two numbers in one cell that it will format them both?
EXAMPLE: One cell that has 5, 15 will read: 5 SF and 15 Units
View 6 Replies
View Related
May 13, 2009
I have a spreadsheet, in columns A & B are some numbers. I'm subtracting B from A - =sum(b1-a1) in column C. Can a formula be written, if the sum is equal or greater than 8, it will display 1? If the sum is 7 or less, it will display the sum. Or, does this need to be in a different column?
View 2 Replies
View Related
Jan 15, 2014
I want VBA to open two separate sessions of IE on two separate displays.
I have not been able get the display part to work and not finding anything so i am not sure if i need separate sessions but would like to do so to be safe (doing it manually on the computers using i have to have separate sessions of IE to do full screen on the two monitors).
current code: (this opens two IE windows on full screen on the default monitor)
Code:
Sub Test()
Dim objIE As Object
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = True
[Code] .........
View 9 Replies
View Related
Aug 17, 2009
One formula is already present in the cell to show a value or date. I also want to use a formula to populate the cell with a comment from the contents of another cell.
I know the individual formulas, but just need to know how I can use both of them to show the results in the same cell.
View 9 Replies
View Related
Jan 15, 2010
[IMG]file:///C:/Users/Paul/AppData/Local/Temp/moz-screenshot.png[/IMG]I need a fairly simple formula (I think) I have this table in another worksheet. I would like to be able to input select from a list "integrity" in A1 and from another list select "20" in A2 and then in A3 this mythical formula (if A1 = Integrity and A2 = 20, then 120%) I suppose i could write a ridiculously large if,then statement but I was thinking more of using a duel vlookup function.
View 9 Replies
View Related
Jun 21, 2007
TAKING A BIT FURTHER THE POST Find Min Value In Multiple Columns And Match The Header
Here's my problem: I am trying to find the MIN value in a column with dual headings and match the corresponding product which makes my formula with three criteria.
see the attachment to see the structure of the tables and further explanation.
View 4 Replies
View Related
Jul 31, 2007
How can I turn this into a dual axis chart whereby it shows the number of current stores open (like it does right now) as a bar chart. But also as a line chart shows the store potential (%) - so for example, PSUN has 960 current stores and has a 96% store penetration rate.
View 9 Replies
View Related
Apr 3, 2012
I can accomplish what I want with the "Spin Button", but I'd really like to use the command button instead...
Can the command button be used for 2 functions?
Right now, I have a Spin Button, that performs this function:
Private Sub SpinButton1_spinup()
Range("E11").Formula = "=c11"
[Code]...
Is there a way to have the Command Button perform the same function? Not very familiar with VBA...I'd even prefer to go with the "Check Box"...but frankly, I simply don't know enough about the command functions to make it work.
View 3 Replies
View Related
Jun 26, 2014
I am trying to create a macro that checks and displays the screen resolution for both the Main monitor and the secondary monitor.
Currently I have this code that displays the resolution on the main screen only:
Declare PtrSafe Function GetSystemMetrics32 Lib "User32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long
Sub ScreenRes()
Dim lResWidth As Long
Dim lResHeight As Long
Dim sRes As String
lResWidth = GetSystemMetrics32(0)
lResHeight = GetSystemMetrics32(1)
sRes = lResWidth & "x" & lResHeight
MsgBox sRes
End Sub
However I want to then display another message with the secondary display's resolution (or on the same message box).
View 9 Replies
View Related
Sep 1, 2009
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.
I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.
I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;
Sub RVorMECHTYPE()
With Selection.Validation
.Delete
.Add Type:=xlValidateList
If Range("$B$4").Value = "RV" Then
ValList.RV_MECHANISM
Else
ValList.VALVE_OPERATING_MECHANISM_TYPE
End With
Range("E4").Select
End If
End Sub
Compile error: End With without With
That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.
View 9 Replies
View Related
Nov 19, 2006
What is the best way to filter a list of data with dual headers, using both those headers as the filter criteria? I’d like to extract 1 column of data to copy to another location using something like Advanced Filter to select 2 criteria ( Upper header and Lower header), and copy the one column with the matching header values.
The worksheet has the following peculiarities:
- 2-row headers
- Top row headers (main categories) are merged, spanning several columns (I can un- merge if necessary)
- Bottom row headers are sub-categories and have repeating names
Although the top headers are merged and the bottom headers have repeating names, each of the 2 headers combined creates unique labels for each column.
View 3 Replies
View Related
Apr 9, 2014
I believe that someone showed me once that you could view the same workbook on two monitors with the vien arrange all mode, you could make changes on both screens and the changes would be saved to the same file similar to arranging two views. I cannot rememebr how to do this or even if it is possible.
View 5 Replies
View Related
Sep 26, 2013
I know what i need to do to get it to open the file I want. I dont know how I set the position of a file that i am opening. In this case a .pdf file.
I have searched and came up with a lot of info for userforms, but I am not working with a userform in this case.
View 1 Replies
View Related
Feb 3, 2008
I have a splash page I created and I work on dual screen monitors. Typically I have Excel open on the left monitor and my VBA window (alt-F11) open on the right. If I close my Excel workbook and reopen it, the splash page will always open on the screen that had the VBA window on last. In my case Excel opens on the left monitor and the splash page shows up on the right window. I would like to have the splash page open on the side that Excel opens on (in my case the left side). Wondering if there is some VBA code I can add in my splash page code that would allow me to override this default setting?
View 2 Replies
View Related
Sep 25, 2012
I would like an easy/fast way cut an entire row of data from one worksheet ("Open Work") to another ("Closed Work"). Each worksheet has a matching Header Row from column A - N. In "Open Work" I have column "K" with various values, but I'm looking specifically for the "CLOSED" value. If a cell in column "K" = CLOSED, I want to cut the entire row and paste it into the other worksheet "Closed Work".
That was a no-go! It copied limited rows AND duplicated them.
VB:
Sub copyrows()
Dim tfCol As Range, Cell As Object
Set tfCol = Range("A2:A9") 'Substitute with the range which includes your True/False values
For Each Cell In tfCol
[Code] ......
View 6 Replies
View Related
Apr 10, 2014
I have an excel file with thousands of rows and I'm trying to pull certain data from one tab to another. In the example, I want to pull all Listings that are "Open" (I know, just filter), but if the listing is "Open" I want to pull all of the other statuses it once had as well in the new tab. Notice how a listing can have multiple statuses.
I was thinking I could filter by Status (Open) and then do a vlookup (using the listing number as the lookup number), but it doesn't work.
View 4 Replies
View Related
Oct 6, 2008
I'm working this spreadsheet and i want to automate of deleting the entire rows once the last two columns contains both Zero values. the worksheet cotains thousands of row to clean-up.
View 3 Replies
View Related
Dec 15, 2009
I want a macro to delete the entire row based on the value in Column Q ....
View 14 Replies
View Related
Jan 18, 2012
I have data in the format below. I'm looking for a piece of code that will look down column B and if there are 30 rows in which all the values are zero then delete all rows from row 1 to the last row of those 30 rows.
Input_data  AB106/01/2012 13:03:170206/01/2012 13:03:1816306/01/2012 13:03:1916406/01/2012 13:03:2016506/01/2012 13:03:2117606/01/2012 13:03:2224706/01/2012 13:03:2316806/01/2012 13:03:240906/01/2012 13:03:259.51006/01/2012 13:03:26191106/01/2012 13:03:27211206/01/2012 13:03:28161306/01/2012 13:03:2981406/01/2012 13:03:3001506/01/2012 13:03:31571606/01/2012 13:03:32801706/01/2012 13:03:331091806/01/2012 13:03:341331906/01/2012 13:03:35156
View 1 Replies
View Related
Apr 21, 2014
I want to get some blank rows in a worksheet after certain characters.i have some data in a sheet. at the end of each cluster of data it ends either with 1-0 or 0-1.
if excel finds 1-0 or 0-1 at the end of a bunch of rows, it should add 5 blank rows under it.then it should search for the next 1-0 or 0-1
If data ends on A25 where you find 1-0 or 0-1 then add 5 blank rows under it from A26:A30,likewise it should be given till it reaches the end of the range.range can be defined as A1:A1000
View 4 Replies
View Related
Nov 2, 2007
I need macro to perform the following steps. I have also enclosed the image of the file for reference.
1)I need to move or copy the data in sheet one to sheet two.
2) In sheet two I need to select customer BBBB and delete all the rows beginning with PO# of 17, 18, 46 or GL4.
3) finally I want to delete all the rows in the sheet which are not equal to class Invoice. (Presently I do this using custom filter as "doesnot equal" = Invoice") ..
View 11 Replies
View Related
Mar 21, 2007
Currently i have this to delete entire rows that have cells which contain any words with "Security Market" in it.
How can i extend this to include many more variables like e.g. " Total" "ABC" "XXXX"
Dim rag As Range 'Rows with "Security Market"
Dim sec As String
sec = "Security Market"
Do
Set rag = ActiveSheet.UsedRange. Find(sec)
If rag Is Nothing Then
Exit Do
Else
Rows(rag.Row).Delete
End If
Loop
View 5 Replies
View Related
