I can solve my problem with a pivot table, and with VBA easily...however, I'm interested in knowing if this can be done with formulas (array formulas using index/match I'm assuming).
Goal: On sheet1 I have one column with products, then the column next to it will have an "Y" in it if the product is to be selected (blank if not). On sheet2 I want to create a list of the products that were selected (having the "Y"). The only thing stumping me is that I do not want spaces between the product list on sheet2...just a nice continuous list. Example:
I have several worksheets (Labeled Sheet1,Sheet2,sheet3) What I need to do is to step through each row in sheet3, and do a search in sheet1, it the data was found, then return the row number. I then will need to copy data from sheet1 (rowfound columnA , through rowfound CoulmnBd to sheet3 current working row columnK
In my spreadsheet, on the first worksheet called "Working". Column A, called "Results", contains carrot ^ delineated string values in each cell (i.e."john^apple^pear^banana^grape^love^heart^pickle"). The majority of string values in every cell in my "Results" column match a "source" column of Pick-List Values, called "Fruits" in the same spreadsheet, however found in a different worksheet called "Lists" (also in Column A). I want to perform 3 functions against my "Results" Column on worksheet "Working":
1.) Report In Column B: Analyze column "Results" by cell and return carrot delineated string values for only those that match my "Fruits" Pick-List
2.) Report In Column C: Analyze column "Results" by cell and return carrot delineated string values for only those do not match my "Fruits" Pick-List
3.) Report In Column D: Identify and return all unique values in "Column C" as a List.
Attached is a simple example of what I'm trying to accomplish called " Sorting.xls"
What I am trying to do is, check if the value of c2 in update is the different to the value of c2 in original then run a code (for the moment just says msgbox so I know it works).
is it possible to have a cell return all matches from a list and have the list of matches reduce as you type, then be able to select one item from the list? this is a typical feature on internet sites, but can it be done in Excel?
I created a userform for a giant spreadsheet with tons of data in it. There are three criteria. I created two command buttons - one that's "search" and one that's "close." The close works fine, but I don't know enough to program the "search" command. I'd like it to pull all data that matches the three criteria - rows H, D and M. So, if you answer all three, the macro will pull the rows of data that matches and paste it into another workbook (that opens in another window).
I'm not sure userforms are typically used for this sort of thing...
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.
I'm trying to create a leave calendar for staff. I would like to paste in the staff members sick and annual leave dates.
I would then like a conditional format that would highlight the leave dates onto the calendar, so i can visually track attendance and check for any sick leave patterns. My calendar looks like below for each month, and although it shows just the date in excel, i have formatted it so its actually the full date (ie instead of 1 it is 1/7/2013, but just displaying 1)
Whilst i can easily track and input the public holidays (on an individual basis) i'm looking to highlight the whole calendar area, say cells (c5:i10) as the search area and then have the leave data as a whole search area (at2:at150)
Above is part A of my question. To further complicate it, if it can even be done. The leave data i get from my system only has the start date of the leave, in another column it has the end date, and in another column it has days absent, if we can do it so it adds the days after (total leave days) to highlight in the calendar that would be amazing, if not, i'll just amend the data to have every date of the leave listed
I have a Excel file with alot of data in it. I need a macro that will create a report for me and relive me from alot of manual work. The 1st sheet is named "Projects". This contains the search arguments. The 2nd sheet is named "Database". This is the sheet where I want to search in. The 3rd sheet is named "Report". This will contain the results of the search. So the going will be something like this:
Copy row 7 from "Projects" into row 7 in "Report". Then use the value in that row, column E (named Search code). Search for rows that has this value in column E in the "Database" sheet. Copy all those rows to "Report" sheet. Copy row 8 from "Projects", leave two rows of space and paste into "Report". Repeat the procedure mentioned above. Repeat this until reaching a row in "Projects" that has no value in Column A.
So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
I need to return all the unique matches from a set of data.
For example, when a product is selected, I need to list all the possible venues it is sold from. I will use this venue list to populate my drop-down listbox, as opposed to having a 100 venues in the list, I just wish to have the applicable ones.
Example of data set I need to lookup in: prod 1 venue1 prod 2 venue 2 prod 1 venue 3 prod 1 venue 10 prod 2 venue 6
I would like to use a formula if I can (not a macro), to list that prod 1 is only sold in venue 1,3, 10.
It would be great if one could use a vlookup type of formula and it could return a list of each occurence of the look-up'ed cell.....
I have the following formula which, if text is in A1, will lookup from a list of worker's comp codes to locate the code in A2 and return the wording for that =IF(ISTEXT(A$1),VLOOKUP(A$2,Codes!$B$3:$C$720,2),"")
However, if there is not an exact match to the code in A2, it returns the wording for the next closest code.
Is there a way to make it so that it will only return the wording for the exact match and return "invalid code" if the number in A2 is not found on the list on the "Codes" sheet?
I have a produced an Excel workbook which uses a VBA sign in/out userform. Once you sign in on the Userform the sheets update. A list is completed of the times people enter and leave.
To make the code easier I currently have the name being returned to the excel sheet and performing a “match” function to return the row number. This row number is then used to carry out what I need to happen in this row. However, as you can see from attached doc (and the brief example below), based on IDnumber "2", the match function returns row 5 not row 8. I need to have the row number returned for the IDnumber where the Out cell is blank. This should be the last occurrance of the IDnumber
I have two columns, one with the target text (D:D) and one with text I want to compare it too (A:A). If the text in column (D:D) matches the one in (A:A), I want to return the value in (B:B). I attached a sample of the problem i'm dealing with.
I've been looking through past threads and I can't find what I need to do anywhere. There was one formula that came close: =SUMPRODUCT((A2:A100="Aimbry")*(B2:B100="LW")*(C2: C100)). but this adds up all the matching results.
What I need to do is match the info in columns A and B and return a number in column C. The problem is that there are multiple rows with identical entries and I just want to return one of them. So I was thinking a version of the above that produced a mode or average would give the desired result.
I have two separate workbooks, each with a great deal of information, such as name, ssn, dates, statuses, etc. I would like to compare the SSN column for both workbooks, and when it finds a match (which there would be many) to copy the MOS cell from the same row to the other workbook. Here is my breakdown with names:
Workbook 1: Macros.xls Workbook 2: MASS 162.xls The SSN column is column C in both workbooks When there is a match found between the SSN column in Macros.xls and the SSN column in MASS 162.xls, I need to copy the MOS cell (column E) from the MACROS.xls workbook to the MASS 162.xls workbook into the same row, but in column M, which is currently empty.
If I searched for the value "abc123" I want it to return Headers 1 and 2 in a seperate column. It would not matter if the same value is in one column multiple times
So the results would show me the Column Heading for anything that reads: "abc123", "ABC123", "AbC123", "aBC123"
I have a list with 8 columns. In the majority of instances, Columns A & B will have duplicate information. I need to be able to search columns A & B & return the information from them PLUS all info contained on the same row's from columns C/D/E/F/G & H. EG:...
My column headings are as follows... Type Region Contact Phone Email ADD1 ADD2 ADD3
In column A (type) would be the type of contact eg:business/personal/family etc.
So... If looking for family I'd need excel to show me the region, contact, phone, email, add1, add2 & add3 for each family record. Hope this makes sense! There are over 1000 records i need excel to search through & return matches.
I'm an intermediate excel user at best so finding it complicated. Tried vlookup but that stops when it finds a match & anything else seems over my head.
I've got two worksheets ("June" & "July"). On both worksheets, column A is comprised of ID numbers and column B contains dollar amounts. I need to compare the ID numbers in Column A on each worksheet, and if they match I want to copy the ID number and the amount to a third worksheet ("Results").
The main worksheet is a roll up page of sorts. It contains all of the information on all three other tabs. All updates entered on the main sheet will be distributed accordingly to each sheet the needs it VIA hlookup. At times, certain updates will be highlighted certain colors. From what I understand, there is no way to copy the color formatting using formulas and return the value.
So, I am trying to figure out a way to use VBA for this. I have dabbled a bit in VBA have think I have found it, except I have run into a substantial road block.
I will use the .hasformula to first check all cells in a range on the other sheets. If there is a formula, it will be an Hlookup only. Once it finds the formula, it then needs to copy the color of the source cell the hlookup is using to return data. It needs to copy the color of the cell, not the color of the text.
I have a formula that basically work, except is really complicated and slows the whole sheet down and then, for some reason, returns a circular referencing error or something. There must be a better way!
So the back story is that I have a spreadsheet that I'm using to track ~1200 employees, including salary, job title and, where I'm having trouble, reporting line.
Column [R] is the Line Manager (direct manager) and Column [S] is Department Head. Columns [T] - [Y] is "Manager", "Manager +1" (manager's manager), "Manager +2" (manager's manager's Manager), etc up to Manager +5.
'Line Manager' is input manually. 'Manager' = 'Line Manager'. Manager+1 to +5 just vlooks up off the same sheet. This part works fine and will update the whole reporting line if you change the manager.
Department head is where I'm having a problem! Essentially what I'm looking for is for the formula to look at the 5 cells to the right (the manager +# columns) and look for 'Mr Boss' and return one cell to the left since department heads report the the boss. There is one exception, its that some immediate reports of Lucy Lawful also are counted as Department Heads. For this purpose I vlooked up this list of exceptions on another sheet. My formula is this (entered as an array):
Below is an example file. In my real file the formula works for everyone, not sure why its not working for half the employee now, but probably cos it's a useless formula.
I have a range of cells (C29:BB29) containing zeros and integers, I want to lookup the smallest non-zero value from that range in another range (C11:BB11) and return the cell address from C11:BB11 to use in a further SUM formula. Is that possible?
I am wanting to allow the user to select a cell in a worksheet and return the cell address and information/value from the cell. For example, if "Dog" is in cell A1 and "Cat" is cell A2 and the user initiates the Macro I would like the macro to to pop up a message box and say something like "Select a cell in Column A". When the user clicks on cell A1, I would like the message box to pop up and say, you selected "Dog" in cell A1.