In the attached spreadsheet I would like to create a list of part numbers if column D E & F all have a #N/A value. Right now I'm using a True/False but this list contains around 3000 items and I don't want to have to scroll down to find the True values. Is there a way I can just create a list of corresponding part numbers to the True Values?
SAMPLE.xlsx
I have 6 worksheets in my file. In sheets 1-5, column A2:A26 list people's names. Some people's names appears on more than one sheet. Not all cells are populated with a value.
ex.
SHEET1
COLUMNA
Bill
[Code]....
My attempt was... =INDEX('Week1:Week5-!$A$2:$A$26,MATCH(0,COUNTIF($A$1:A1,"Week1:Week5"!$A$2:$A$26),0))
where the sheets were Week1-Week5 and the values on each sheet was A2:A26. But I think there's an issue with Excel being able to 3D reference for these types of functions.
I have long text values that include names in cells A1:A100.
In cell D1:D10, I have a list of names that I would like to check if any of them is found in each of the cells in A1:A100 and if yes, return TRUE in column B.
So in a way, what I need is a a bit like the SEARCH function, only that I need to find multiple FIND_TEXT values. If just one of the names in D1:D10 is found, the formula should return TRUE.
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
View 8 Replies View RelatedI have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
I'm trying to come up with a single formula to create a single column list from a table with blanks.
a
b
c
d
e
f
g
To
a
b
c
d
e
f
g
I know I've done this before but having trouble visualizing today.
If I have a table as noted below with the following assumptions:
- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.
A
B
C
1
Item
Calories
Include
[Code]...
How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?
I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx
I am trying to create a top ten list of my clients, my clients are listed by customer numbers. The data is listed as follows:
Client ID
7164
7164
7164
5781
5781
6392
So my results should be:
Top 1 7164
Top 2 5781
Top 3 6392
I have been trying to use Mode, but that only returns the top value.
I'm trying to write some VBA code to do the following (don't think its possible using formulas only). In column A i have a list of codes (A1:A32) in column B i have a value (between 1 and 32 in this case, but feasibly could be any integer) I am trying to achive an output in column C which would take the code from col A and then "count up to" the value in col B;- for example "AAA" in col A, and "4" in column B would return AAA1, AAA2, AAA3, AAA4 in column C. For the next code say BBB and corresponding value, would start its sequence in the next empty cell below AAA4 in col C.
View 9 Replies View RelatedI want it just to give me a list of every instance a certain word is used in a column, just like on the autofilter. But each value should in the cell beneath.
Basically I have a list of clients, now when using the autofilter I can select to see the cases from just that client.
But what I want is it to list all the clients relevant to certain months in a column.
I am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this
1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.
2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc
3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)
ex/ route A worth $1
route B worth $2
create a dropdown list but thats about it, i have not been able to link any values together.
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}
however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.
Example:....
i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.
A 1
B 0
C 0
D 0
But I can't determine how to get this done.
As a 'true' result of an 'IF' statement, I would like to return a data validation list, as a false result, the term "Not I/C"
The below isn't right, but its the best explanation of what I'm trying to do........
=if(A1="Yes",(the list, eg B1:B5),("Not I/C))
I have one tiny qs which I hope someone can answer for me.
I need to allow a user to see a dropdown list (with options) only when the previous cell has the word "complete".
For example, cell B1 can have:
On going
Complete
Blank
If B1 has complete then I want the user to be able to select a number from a drop down list in C1 otherwise I want it to say "wait" or if its Blank to be [Blank].
I tried the Validation > Custom and put in
=IF(B1"Complete","Wait",OptionsList)
Where OptionsList is a defined list of 5->1 but this lot didnt work. I tried similar things in conditional formating but nothing again.
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
I am trying to creatre a formula which list numbers from rows to columns by deleting the vertical spaces i.e.
A1 lists empty
A2 lists 3
A3 lists empty
A4 lists 7
A5 lists 1
A6 lists empty
result:
Columns: B C D E F G
3 7 1
similar to stacking values into a nice hoz column and not noteing any spaces
I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.
My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad
[URL]....
[URL]....
I am aware there are people with more pressing problems than computer games and as such
I would like to be able to compare the "standards" in Column A with the Assessment status in Column B and the Assessment period in Column C in order to generate a compact list in another workbook (ideally) or tab (if not). A list might typically contain all the standards that have been M (mastered) during a given Assessment period. I realize that filters would achieve this to a large extent but I was hoping to automatically populate another worksheet or tab.
View 3 Replies View RelatedI want to lookup a value using * and ? and have the result tell me whether all possible values have a "yes" next to them.
Let me explain. Example.
If I have this in table1:
Records Yes/No adfcjks7as Yes 91521cg N/A 215a786 N/A 9194215g Yes 2154a6 N/A 614asdff No asdf562 Yes as895as Yes a5s5d8d6 No
And I type *as in cell A1
THEN I want cell A2 to lookup "*as" in Table1[Records] and determine if all possible records have "Yes" next to them in column Yes/No. The result is "ALLYES" if its true or "NOTALLYES" if its false.
In this example the result is "ALLYES" because adfcjks7as and as895as are the only records that *as applies to and "Yes" is next to them in the same row. If there is only one possible value then it still applies.
Of course * and ? will be used in different arrangements to find possible values.
I am looking for a Macro that would randomly select 20 items from my inventory list for performing spot checks. Column F of Active Coil Log tab is where the data would need to pull from, however I would like all data in the row to go along with it. I've explored the RAND functions, but they don't seem to be the right fit .
View 2 Replies View RelatedHere's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
I am creating a template for formatting data that I download from a wholesaler to upload to a platform. The large database of around 1300 records that comes with category and subcategory names, among all the rest of the data, that I download There are more column titles and information than I need, so my template will have blank headings that I will know to simply delete after I paste. Maybe there is a Macro for that too.
The category and subcategory names to appear in certain columns. I want those names to appear in multiple columns within the same records. How to create a formula/macro to copy all of the data in one column to a different column with a different heading and keep those headings different and save them within my template. I have a list of the 240 with CategoryID, ParentID, Category and Subcategory all corresponding. These always come and go in CSV format, so I cannot create additional sheets to save within my template without a hassle.
In two ID number value columns, I would like all of the text names replaced with corresponding numbers for upload to the platform that requires them. I have been trying to figure out the best way to create a macro to save within my template for daily use, but so far I have not been able to figure it out. I have been watching videos on vlookup, index & match, find & replace...I have not seen what I need just yet
OK, SO HERE IS MY PRODUCT TEMPLATE SO FAR: [URL]
I'm afraid I've run into some trouble with the Listbox function, which I really love, but I can't quite get past this hurdle.
I want to have a listbox which populates by checking an entire column for values, ignoring blank cells, and, if value is present, to also check to see if any value is present in the cell one column to the right and one row down from the cell that has the initial value. If both these conditions are true, to then display both values in the Listbox. In other words, the Listbox would contain two columns.
During this routine, when checking value is present in the second cell, I'm guessing that an IF statement would increment a variable signifying the cell reference by + 1 for column and + 1 for the one row down?
I have a spreadsheet with about 300 products listed in column A. In row 1, I have 30 store listed across. This produces a grid of data with sales for the past 3 months. I would like to easily have a list of the product and store that had 0 sales. I have tried a combination of INDEX/MATCH as an array formula but can't get it to work.
View 6 Replies View RelatedI'm looking for some help. I need a function that can check if each of two seperate values is less than one. If less than one then divide by 2. Then give me the average of these two values. So it could be that it takes 0.5/2=0.25 for one value and 3 for another to give me the average of 1.625.
I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap
1.System data populates sheet
2.Pivot table pulls info I need.
3.Lookup list cross references hand entered data sheet and puts value next to pivot table.
4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
I'm using multiple data tables to run sensitivity analyses on a very large model. At the end of the analysis, I've got a matrix that tells me which scenarios are TRUE or FALSE (based on multiple criteria). I'm looking for an elegant way to extract the combinations that give TRUE.
Column1
Column2
Row1
FALSE
TRUE
Row2
TRUE
FALSE
Desired Output
True Scenarios:
R1, C2
R2, C1
I need to make an IF statement that when it's true it need to retrieve the values from a different column. With what function is it possible to just retrieves values?
I need to return the value from pivot table. Forgot to mention this.
