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.
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.
What it does is open a singlesheet Workbook and copy that sheet onto the sheet with the macro. It only copies this if the cell E1 matches in both sheets.
Now I'm trying to change this - I want to match a range - A1:V1 should match
I have a workbook with 2 worksheets, Sheet1 and Sheet2 have the same column headings in Row 1 but they are not in the same positions.
What I need to do is read the column header in Sheet2 Column A (Cell A1) and copy the data from A2 to last row and paste it in Sheet1 in the first empty cell under the same column heading, which may be the Column D position instead of Column A like in Sheet2.
It needs to loop through all the columns in Sheet2, copying the relevant data to under the correct header in Sheet1.
I was going through "To do list with progress tracker" template in Excel 2013. I noticed when i scroll down the page the heading column (A,B,C,D,E,F,G etc.) automatically matches with the inserted table headings.
View 1 Replies View RelatedI currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.
View 3 Replies View RelatedI 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
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 RelatedI 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] .........
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.
[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 RelatedHow 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 RelatedI 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.
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?
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).
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.
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.
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 RelatedI 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.
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 RelatedI'm looking for a macro that can sort emails by 2 very distinct ways, with 1 special find function as well. Not too much eh? ........
View 9 Replies View RelatedI've been trying to solve with a 2 column drop down. What I am hoping to do is use two columns to aid the user in selecting the correct entry. So the first column will be a number and the second column would contain what the number means
If:
Col1 | Col2
001 | Horse
002 | Cow
003 | Dog
Both columns show when the list is selected, but when the user selects 003 | Dog, the cell would be populated with only the 003.
I need a vba code which inserts a text/heading into an excel chart.
Today I wrote the heading inside the code, in this way:
Is there a way to hide the formula bar and headings for a specific worksheet(s) with a workbook but not all the worksheets? In other words, I want to hide the formula bar and headings only for certain worksheets. When I select hide on the show/hide ribbon, it hides it for all the worksheets within a workbook.
Also, is there a VBA code that can be written to hide formula bar and headings for a selected worksheet?
I have a workbook. Numbers On The Left And Letters At The Top of all sheets cannot be seen. How can I make them visible?
View 2 Replies View RelatedI use currentRegion to add the item to the combobox, but i don't know how to add without including the first cell in the range,as usually, it is the field name.
View 5 Replies View RelatedI've got a few worksheets that have staff names on Col A. And these names are under sub headings of what position they work in (Foremen, Operatives etc..).
Before I had access to them, the names were not in order, now I've change it to A-Z, for each staff under their position.
I know you can make your own custom order list, so the list goes top to bottom : Supervisor, Formen, Operative, Office.
What I sometimes need to do is sort Col B or C. But is am unable to sort it back to it's original state
Attached is an example of how I would like my spreadsheets to look. But is there a way to use Sort & Filter so it sorts the entire table by position, keeping the correct name under the correct position heading.
New Microsoft Excel Worksheet (2).xlsx‎
I use a macro to format a debtors ledger. This report shows the amounts that customers owe broken down by the age of the debt, ie Current, 30, 60, 90, 120 days.
To make it easier to read I want macro code that will replace these mostly numeric headings with months ie April, March, February, January, December.
I want to save a worksheet as a CSV but Excel saves the column headings as the first line of the saved csv file. I don't want the column headings in the saved file.
View 3 Replies View RelatedMy questions is how to "freeze pane" a row of headings on the Results page and post the results onto row 2 and downward *without* deleted row 1.
If you look at the attached file, you will see a tab that I created called "My desired Results Page" that simply has a frozen row of cells. Instead of just displaying the rows from the Details page on the Results page, I'd like them there but with the headings at the top.
Is there code that can do this?