I am in the process of designing an inventory like spreadsheet with prices. If possible, I'm trying to find a well to look at a column for every instance an item shows up (lets call them apples) & then find the LAST instance of apples showing up to pull the pricing of that row only. Is this possible?
In col A, I have the dates. I would like to create a formula in all the other columns in row 1 that will look fror the first instance of a value in each column and return the date that that threshold value is reached.
The value I am looking for is 3, but the values in the columns might look like 0 1 1 1 3 2 1 3 4 2 0 etc. I want to return the date of the FIRST instance of 3. How would I do this?
Ok so I am trying to streamline the Shirt ordering process at my work. I have made a table with Employee names as Column A, then Row 1 is shirt sizes from small to XXXL. The Cell values are going to be the colors people want. I want to total the number of each size/color combination. But someone may want more than one of the same color. For instance you would enter "BK, BK" if you wanted 2 black shirts of that size. This is where the issue with COUNTIF comes in. If a cell has the same value twice, it only counts it as one because counts the cells with an instance of a value, not the actual count of instances. Is there a way to make COUNTIF or a better function for this?
I have many kitchens using the same recipes. I need to distill information down until I've got a summary of how much is being made. Uploaded is a condensed version of the point in the process I'm having difficulty with. This workbook will pull information from 8 other workbooks and give me excatly what everyone made on any weekday.
And from there, with the kind help of this forum, I figured out how to do a SUMIF based on the recipe number. And it summed up all instances of 'Recipe X' being used. However, it continues to SUMIF itself all the way down the page... which is good, because of how recipes are chosen for each kitchen. However, I only need to report one instance of each recipe.
In the uploaded example (and I apologize for the colorful sheet, but it helped me double check what I was working on.) ... I only need to report the PURPLE results elsewhere... the first instance of each SUMIF.
I have the following Private Sub test2() Dim c As Range, t As Long
With Worksheets(3) .Unprotect Set c = Range("AO10", cells(Rows.Count, "AO").End(xlUp)) t = Application.WorksheetFunction.Max((Application.WorksheetFunction. CountIf(c, ">=.01") * 2), 0) Sheets("Template").Range("D3").value = t 'Range("D3") = t End With End Sub
The code is suppose to get the value of >= 1 percent from AO10 then go down the column until it finds the second instance and then add that number to the tile.
I am trying to create a formula that will search a row left to right for the first instance of zero and return the corresponding column label. I had this working for a smaller table using a couple of hidden columns with nested ifs however the new data set is approximately 25 columns wide and it will get very messy using the same approach. See attached sample with desired results.
Using Excel 2003 SP2. The formula =vlookup("apple",table,2,false) would return the data in the second column of the array named "table" for the FIRST INSTANCE it comes across for the word "apple." Is there a formula to have it return data for the LAST INSTANCE? This is one where I say to myself "Self, there has got to be a way to do this," but I just can't figure out what it might be.
how to close the instance of excel created by my VBS script.
I run the script to open a workbook run a macro within the workbook and save it. I'd like to also close the workbook and the instance of excel.
If I close the workbook within the end of my macro I get an unknown run time error from my script. If I elect to just save the file and close it within the end of script, the workbook closes but the instance of EXCEL.EXE is still running in task manager.
This is an export from a shopping cart. Each row is an individual SKU that was ordered. So if John Doe orders 4 items on the same order, I've got 4 rows with most of the data being the same. I want to sum my "Grand Total" column, but only take one instance when the Order-ID is repeated.
What I need is for a formula that lists one example of each instance included in Column A.
As per the example data above, the expected results when dragging down from C1 through to C9 would be:
---------A----------------B--------------C-------- 1--Main Building-------001-------Main Building 2--Block A--------------001-------Block A 3--Block A--------------002-------Block B 4--Main Building-------002-------BSS House 5--Block B--------------001------- 6--Block A--------------003------- 7--Main Building-------002a------- 8--BSS House----------BSS------- 9--Main Building-------003-------
I know I could use a pivot table to do this but as I need this to be dynamic, and also to be used with Excel on an iPad Air, I can't as it doesn't support pivot tables or VB to automatically refresh it.
I want to find top 2 performer in cell A17 & A17 in my attached sheet. Column B2:B13 changes everyday, so the name of performer changes accordingly. But today my top 2 performers achieved same but my formula is giving the same name.
Is there anyway to find out the other name i.e Ahmmed Shawkat Hossain here instead of Khan Md. Abdul alim again?
Column A contains numbers that repeat themselves. in column B codes are assigned to each number. numbers in column A may have multiple codes in column B. but are unique for each instance the same number may appear in column A.
what i'm trying to do is.
have the numbers in column A show only once and then have the codes for the corresponding codes appear each in seperate columns rather than all in one.
I have data on two separate tabs. Data in columns A of both tabs potentially match. I am trying to identify and pull the value in column C of the second tab where the values in columns A match and the first instance where the value in column B on the second tab is less than the value in column B of the first tab. The values in both columns B are dates.
column A first tab = column A second tab column B first tab < column B second tab (first instance of less than) pull value from column C on second tab to appropriate row of column C on first tab.
I am working on an application for a monthly sports tournament that I run. There are three workbooks to this application: the Administration workbook, the Bracket workbook, and the Leaderboard workbook. I currently have a dual monitor setup (extended, not clone) with my laptop in front of me and a large TV on the wall for the players to see.
In order for the players to see the bracket and the leaderboard, I open excel, then I open the Bracket.xlsm workbook, then the Leaderboard.xlsm workbook, and I drag those to the TV. I then open another instance of excel on my laptop, and open the Administration workbook. This stays on my laptop and the players cannot see it.
Here's what I want it to do:
In less words, the administration workbook controls the other two workbooks. Every time a player reports a win/loss, I enter that into the Administration workbook, and that needs to update both the bracket and the leaderboard, so the players can see it on the TV. So, basically, all the data is in the administration workbook, and the other two serve as displays for that data in a format the players can understand.
Here's my problem:
When I run a macro that is supposed to update the other two workbooks, I get an error: "Runtime Error '9': Subscript out of range." I believe this is because of the seperate instances of Excel. Since Bracket and Leaderboard are in a different instance than Administration, it can't 'see' those other two workbooks. Below is the part of the code that deals with these three workbooks: ....
I have a very very long weekly table of, say, sales regions, items sold, revenue, profitability, etc.
(Very long here means 120,000 rows, and in Excel 2007 - please don't hold this against me! )
Let's assume Row 1 and Column A are for headers. So Column B is Sales Region and Column C is Item Name. The first Sales Region, beginning in B2, let's call it "America", will repeat (B3, B4....) until the Item Names are done for that region. Then the next Region starts, "Australia", and the list of Items starts again, etc.
Not all the Items will be sold in every Region each week, and even some Regions may disappear from the table one week only to reappear a few weeks down the road. The table is Sorted alphabetically, though, first by Region, then by Item.
One way to VLOOKUP week over week information from last week's report onto this week's report is to concatenate both weeks' Region and Item information (=B2&C2) seperately, and VLOOKUP by that. This returns only combinations of Region/Item that occured in the current week, and with ISERROR, 0 for combinations that occured this week, but not last week. As far as what it returns, this is fine for our purposes but with several pieces of information to VLOOKUP (#, revenue, profit, etc.) and 120,000 rows, it takes forever. Literally over an hour to copy down the week over week parts.
I had the idea that maybe it would be faster if I could define individual VLOOKUP ranges for each Region, and then look up Item within that range. These ranges would vary in placement in the table and in numbers of rows each week, as the number of Items sold in each Region waxes and wanes, hence "dynamic ranges" - I hope I've used that term correctly.
Is there an effective, efficient way to return the row numbers of the first and last instances of a given Region, and use that range for the Item VLOOKUP?
I need to highlight all the duplicate IP-addresses in my sheet except the first one.
What I did:
I chose the range (F column) -> Conditional formatting -> Highlight cell rules -> Duplicate Values. It highlighted all the duplicate values so i googled how to highlight the duplicate values except the first one so I came across a formula =COUNTIF($F$2:$F2, F2)>1
So I added the formula and now it does not highlight the first duplicate but only the second and not the third and fourth and so on.
What am I doing wrong?
Edit: I have sorted them in order and in one IP-address I have 6 duplicates but it highlights only the last one when I want it to highlight all of them except the first one.
In the last couple of weeks my copy of Excel 2010 has started acting strange. Now when I double click an xls or xlsx file in Windows Explorer, it tries to open a new instance of Excel, whic means that it tries to reopen Personal.xlsm and Personal.xlsb, and finds they are already open, so it gives me an error message. Previously it perfectly happily opened the document in another window in the same instance, which is presumably much more memory efficient quite apart from the file locking problem mentioned. How do I get it back to the way it was before? Is this another of MS's famous overnight downgrades?
-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.
I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.
Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.
What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.
I have tried "Application.Activate" and can not make it work.
I have a spreadsheet which I am trying to put in a macro to add a new page (below the current page in the same worksheet) so that users who are unfamilair with Excel can simply do this.
I have got the macro to copy the cells I want and can get it to paste but the issue is I don't always want it to paste in the same place. The current document is 1 page long but it may need to be up to 10 pages long. I am trying to get it to find the last instance of the word "COMMENTS" and then paste the copied info 3 rows below that.
I have a list of product numbers in col. A. In col. O I have a list of file names that contain the product numbers as well as additional characters. I need a formula that will search col O for the first instance of the text in cell A2 and return that value.
The next formula will return all instances that contain the text found in A2.
I use the following code to open an existing open workbook in a new instance of Excel...
Everything works fine... What I would like to do is close (when I say close I mean, completely exit/quit the application). I'm not able to do that, the original Excel instance stays open with no spreadsheet open. I would just like it force the application to quit so that after running the code I would just have one Excel instance open (with the desired workbook, which works fine now)...
Public Sub Re_open_workbook() Application.DisplayAlerts = False ThisWorkbook.Save
I am running Excel 2010. I have two separate instances of Excel running. I select data and copy it, and then paste it into the other excel spreadsheet in a separate instance. The clipboard data DOES NOT paste the desired data, instead it pastes the item most recently copied in the separate Excel instance that I am trying to paste into. It's as if the excel instance has it's own clipboard, and is unable to use the same clipboard the other Excel instance is using. Copy and paste functions / clipboard seems to working perfectly outside of the Excel program.
I have the following formula that returns the most frequently occuring string in a range. what I would like to do is return the 2nd, 3rd 4th and 5th most common occourance as well. I am not sure if I can do this by adjusting this formula or whether that would be a completely diffent formula or worst case senario it is not possible at all.
I regularly process CSV files that are downloaded from various websites. The problem is, for some reason many (but NOT all) downloaded CSV files do not open in "Microsoft Office Excel" and thus, my primary workspace. They instead open in a separate instance of "Excel.exe" which is actually kinda nice because I can treat each separately, move windows around, etc. The problem is, macros that are in my primary workbook, cannot be seen by the secondary instance(s). So while I have created a number of nifty macros to clean up all the data and format it exactly how I want it in the blink of an eye, I can't run them when a file opens in its own instance.
1) How can I force ALL .csv files to open in the primary "Microsoft Office Excel" application? NOTE: Using the normal Windows Explorer "Open With..." function and setting the default application only works for LOCAL files, and does NOT work to control how remotely downloaded .csv files are opened.
2) How can I make it so that macros can be seen between Excel instances - such that when I choose to see macros in "all open workbooks", it will actually see the macros in the open workbook in the other primary Excel instance?
I've got a very large spreadsheet wherein I need to write a formula for each row to find the first cell with any entry greater than 0 and then add to that cell the contents ONLY 5 cells contiguious to it.
For example data set is this (in .csv format for you)
I have a worksheet with a column that lists the city where an item is located. If the city is say Austin I need to find the last cell in the range that has Austin in it. I can find lots of examples where you can find all instances within the range and to find the last cell in the range that has any data in but nothing that tells me how to find the last cell in the range that has a specific value.
In the sample file attached, I want to do a lookup with the employee id in the master sheet and find the multiple results in the "open_text" sheet. From there, I want to display the open text results in one row per employee on the master sheet. If there are multiple comments, then the results will be displayed in separate cells to the right of each other (filling comment1 through comment5 if needed).
My main goal at the end of this is to be able to do a mail merge out of the master file. This is why I want the results in one row per employee.
I've created a UserForm which the user exclusively uses to interact with a workbook.
The problem is, if the user opens another workbook, it will open in the same Excel "Process" or "Instance" and the user would have to close the UserForm in order to use the other workbook they opened.
In VBA or some obscure application setting, is there a way to force workbooks to open in a new Excel Process (or new "Instance" of Excel)?
Another possibility would be to code something in the UserForm workbook that, on Workbook Open, would start a new separate Excel process....if that's even possible (can VBA execute a system shortcut or system command string?).
I am looking for a formula that will output the highest and lowest value in a range. Column A contains a range of numbers, some of which repeat and in column B i'd like to extract ONE instance of the highest and lowest value in Column A, preferable the last instance.
See example below and in column B the desired output.