Return Text From A Mixed Text/value 3d Array Result
Dec 2, 2009
I've been searching the forums and web trying to figure out how to extract a text value from a 3D array result, to no avail or well beyond my understanding.
I've attached a file which shows an abbreviated version of the search (from the original 49 col x 400 row, which shows the name to date array which can return a sum.
What I'm looking to do is identify within this array result if there has been a text entry, and return the text as opposed to a sum of the remaining values. This is essentially a double check to make certain that there aren't hours scheduled when other events such as vac/loa/etc are also booked.
View 10 Replies
ADVERTISEMENT
Aug 6, 2009
I need to multiply an array of logical results ( returned as {1,0,0,0,1,0 et.}) with a text array (a reference column) and return the text in the reference column in case the value in the logical array is 1.
View 3 Replies
View Related
Jul 12, 2002
I have a column in which the cells contain values such as "012321 BGH YRK". Is there any way you know of that I can remove just the text from every cell and keep the numeric values? A mass operation because there are literally hundreds of these cells. I am using Excel 2001.
View 9 Replies
View Related
Oct 26, 2007
I have a spreadsheet where in cell E2 there is a drop down box with the following options to select: Warranty Replacement, Insurance Claim, Billing Issues, Retention Opportunities.
In cell F2 is where wait time minutes are generated depending on what is selected from the drop down box in E2. I am trying to create a nested IF formula for the following scenario:
Warranty Replacement = 20
Insurance Claim = 20
Billing Issues = 15
Retention Opportunities = 20
View 5 Replies
View Related
Dec 18, 2007
I need an excel function for this...
Say,
Cell A2= 8
Cell A3= 1
If A2 < (9*A3), then Cell A4 will say "Plastic", otherwise it will look to the next function, which is If A2 < (10*A3), then A4 will say "Compact", otherwise, it will look to the next one which is If A2 < (15*A3), then A4 will say "Semi-Compact" otherwise If A2> (15*A2), it'll say "Slender". so at the end, Cell A4 can only be one of these: 'Plastic' or 'Compact' or 'Semi-compact' or 'Slender'. How can i combine all the functions together?
View 2 Replies
View Related
Jan 30, 2014
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
View 7 Replies
View Related
Mar 18, 2009
I have an Array formula that returns a value from several rows below:
=MAX(IF($P$8:$AQ$8="Today",$P13:$AQ13))
Row 8 contains only text and only one occurrence of the word "Today".
Row 13 has only numbers. The corresponding value directly below the column containing the word "Today" is returned successfully.
I now need a similar formula to return a text entry located in the same column but on the previous row (row 12) directly above the value just returned and this row contains only text entries.
Using the above formula and referencing $P12:$AQ12 returns 0 as expected.
View 4 Replies
View Related
Sep 23, 2008
I have a mixed string (i.e. containig character and numbers.... but the format is same PAR1 or PAMR1 or PR10 it can be 4 characters or five) i want a formula or a macro which splits it in two parts one containing characters and the other one containing nos.
View 9 Replies
View Related
Mar 7, 2013
I have an array of data with multiple codes stored in text...
I want to set a formula in a cell to look for all instances of specific text in an array. Then compare the value of all of the column headings, which are numbered across the top of the array, which contain a match in that column and return the highest value.
Example: 10x10 array, columns 1,3, and 5 contain "text" somewhere in those columns. Formula would return 5.
View 2 Replies
View Related
Jun 17, 2014
A column holds numbers and numbers with suffixes. The conventional sort function pushes the text values to the bottom. I need them sorted along with the numbers.
Microsoft's KB says to add a column formatted as text and then to RETYPE ALL OF THE VALUES! Splendid. But my file has hundreds of records. Re-typing all of them would be a major pain. On the other hand, if I had started typing while trying to find a way to sort the darn thing I might be done by now.
Here's what I know so far: If we create a column, format it as text and then populate with 1, 2, 3, 4, 1a, 2a, 3a, 4a, etc. it will sort exactly correct (1, 1a, 2, 2a...) after selecting the "sort numbers and numbers stored as text separately" when the Sort Warning appears. If you try to create the sample I typed here, you need to be careful that the area is formatted as text BEFORE entering the text. You apparently cannot change the format of an existing column of data to Text or if you do it does not have the desired effect when sorting. It might be useful to know if this can be done too. MS suggests not.
View 11 Replies
View Related
May 8, 2009
Counting with text and symbols mixed in. This is what I'm wanting to do:
For example:
View 5 Replies
View Related
Jan 28, 2014
I'm trying to formulate:
If it's mixed case, copy it. If it's all lower case or ALL CAPS, then make it PROPER.
i.e., if 'bananas' is in A1 and B2 says =PROPER(A2), then B2 says Bananas, because PROPER capitalises the first letter of each word.
I would like to do
[Code] .....
View 5 Replies
View Related
Apr 11, 2012
I've been given a spreadsheet that is poorly constructed but I'm not allowed to alter.
The layout is:
Box Name Box Date ID1 ID2 ID3 ....
Where there is not a fixed number of IDs in each box
(There are five different sheets so I'll have some fun concatenating results)
Given a list of IDs I need to return the box name and box date that the ID's file sits in. The spreadsheet has blanks everywhere and the numbers contained in rows are non-consecutive and smaller values can appear later in a column than larger ones.
I think I need some combination of index, offset and maybe sumproduct? TBH, I avoid sumproducts like the plague, preferring to utilise sumifs and dynamic named ranges since this improves clarity so I'm basically a newb at them.
Any skeleton framework that you think would work to return the text values for the LHS where the number could be anywhere? Once I can see a rough example I should be able to get to grips with the logic and wrangle it into the necessary solution.
View 5 Replies
View Related
Feb 22, 2012
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
View 9 Replies
View Related
Aug 4, 2012
I have text of size 14 and 18 mixed in cells in a column. Cells are font size 18 or mixed with both 14 and 18 size text. I need to sort out the text with one column of size 14 and another of 18 only. I am thinking of copying and pasting the column twice and run a macro in first column to remove the text of size 14, and another macro to delete text of size 18 in second column. I need the leftover text to be in same rows.
I tried everywhere and couldn't find a macro for mixed text cells. I am using Microsoft Excel 2010.
View 9 Replies
View Related
May 14, 2009
I have a table with subtotals that I need to find the largest value for the subtotal results and then return the cell contents for the corresponding row.
I have attempted to use the hlookup function, but keep getting a #ref error (probably because I am just not that familiar with the entire formula requirements).
I attempted to nest in the 'largest' function to the lookup function, but have so far been stymied....
View 14 Replies
View Related
Apr 10, 2013
I'm trying to get something like - If a cell contains certain text AND certain text then display set text OR If a cell contain certain text AND certain text then display set text.
For example:
Vauxhall Corsa 1.5Litre Petrol = vauxhall corsa petrol
Vauxhall Corsa 1.5Litre Deisel = vauxhall corsa Deisel
Vauxhall Nova 1 litre Petrol = vauxhall nova petrol
So it the formula is effectively searching for the car type and the fuel type and then returning the relevant set text. So far I'ver only been able to find IF functions criteria to identify one feature of the text.
I'm thinking it's got to be variation on =IF(ISNUMBER(FIND("Vauxhall Corsa",A1)),"Vauxhall Corsa Petrol") but there should be something else in there like =IF(ISNUMBER(FIND("Vauxhall Corsa"&"Petrol",A1)),"Vauxhall Corsa Petrol")
I know this is wrong but just trying illustrate my thinking. I need it to pick up vauxhall corsa AND petrol. I understand that I would have to stick a lot of IF functions together in a string, but that is fine as long as I can ask it to pick certain text AND certain text within a cell.
View 6 Replies
View Related
Nov 20, 2013
I am looking up a risk matrix to return the risk rating... it is very simple, it works on one sheet, but on a different sheet (looking up different data, but same basic format) it returns exactly the opposite rating.
My formula is: =INDEX($G$6:$K$10,MATCH(I19,$G$5:$K$5,0),MATCH(J19,$F$6:$F$10,0))
Here is F4 to K10
I have data validation on I19 and J19 based on the cells below
Consequence
Priority
Very Low
Low
Medium
High
Very High
[Code] ..........
BUT... when I put Very Low and Very Low in the two cells (I19 and K19) I get Very High as the return.
View 1 Replies
View Related
Jun 12, 2014
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C
For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
View 3 Replies
View Related
Apr 30, 2014
I have 10 cells in a column. I have a drop down list in each that is the same in each. If all 10 cells have the same item selected from the drop down list, I want a separate cell to list Yes or No. I've tried a few variations with no luck.
View 4 Replies
View Related
Feb 28, 2013
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
View 6 Replies
View Related
Oct 24, 2009
I have an array 4x4 in size
I need to be able to tell it to look up and find a specific value..
possibly within a chunk above or below a value
and then spit back a true/false which I will turn into text
example
E1 = 99
I want to look up in the 4x4 array of B5:E8
when it finds any value that matches it , in this case its in B1 (99), it spits back a true into box E2
In an ideal world I want it to find any value that is within 0.25 of the value either way
meaning if the value in E1 = 99
then I want it to return true for any value that is greater than 98.75 and less than 99.25
View 10 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
View 9 Replies
View Related
Oct 15, 2007
I am trying to Lookup a country to see if it is classified as a "Developed Country".
My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging.
I have tried an IF statement using the Match function and it does not work.
=IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View 4 Replies
View Related
Jun 12, 2014
How would I pull only a specific word from a text cell? If you see my example, in one column I have various job titles. If A2 contains "Manager", then in B2 return "Manager. If A1 contains "Coordinator", return "Coordinator. Etc.
Excel Forum.jpg
View 3 Replies
View Related
May 3, 2006
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
View 8 Replies
View Related
Jan 28, 2014
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
View 9 Replies
View Related
Sep 14, 2007
I have two cells (c1 and c2).
c1 contains (2+3*4)*3 without "=" at start.
c2 must contain the result of c1 it means 42.
In the attached file I have a few variants I tried to solve it but they both don't fit.
Formula.xls
View 5 Replies
View Related
Feb 5, 2014
I have the formula in O62 cell :
[Code]....
In formula bar when I sellect complete formula and press F9 key, I can see:
[Code] .........
results.
How can I write this visible result to "O62" cell or, " another cell example"P62" as a text value.(without to copy & paste).
I want to make this with a formula ( if impossible by macro).
View 13 Replies
View Related
