Returning Text Against Text Based Criteria?
Jul 19, 2014
I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.
I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.
Example
What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap
Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx
Category files
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
View 1 Replies
ADVERTISEMENT
Apr 24, 2014
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies
View Related
Apr 14, 2014
I am looking to return text based on the maximum value in a table.
Dog
2
Cat
10
Horse
4
Fish
27
Lion
1
Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier
I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.
View 1 Replies
View Related
Jun 17, 2009
I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.
A B C E F G
M S Get value from G M S 5G
M T M T 6M
M U M U 7H
M V M V 8K
View 9 Replies
View Related
Jun 4, 2012
I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:
Payment Status
05/08/2012 10:30:12
INV
05/17/2012 08:27:37
INV
and so on...................
View 9 Replies
View Related
Oct 14, 2008
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
View 10 Replies
View Related
Mar 18, 2009
Trying to sum a range based on the criteria of the 1st position within each cell in the range. Cells have data like "A-8", "A-4", for a total of 12 for "A". This is the formula I have that is not working.
=SUMIF(left(C4:P4,1),"A",RIGHT(C4:P4,1))
View 6 Replies
View Related
May 29, 2007
to run a macro or any other methods to repeat the same head rows in my database as followings:
Let's say the main head row is in row 1.
Item names such as Item A; Item B; Item C ....in column A
What I want is to insert the same head row after the list of each item.
Item name (head row)
Book A
Book A
Book A
Item name (head row to be repeated)
Book B
Book B
Item name (head row to be repeated)
Book C
Book C
View 9 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
Jan 26, 2010
I'm trying to create a formula that will allow me to pull test from a list (auto populate if possible). Essentially you will see on the second tab, a list of projects "Cali" for example. I'm trying to find a formula that will allow me to show the Customer and Life Cycle on the first Tab. If possible the Project Name too.
Essentially I want to be able to have all the data inputed into Tab 2 and let Tab 1 condense it into the designated fields. So basically what will allow me to see all of the "Cali" projects, and from that generate the Customer and Life Cycle (and Project if possible) on Tab 1. Keep in mind this does need to be automatic updating, so that as we input more information on Tab 2, it will automatically kick into Tab 1.
View 2 Replies
View Related
Sep 4, 2007
I am trying to get the sum of some cells (integer varies in column G), but comparing one column content (exact) and dates in a different column.
I tried the following:
=SUM((Tony!G30:G44)*(Tony!D30:D44="Crazy")*(MONTH(Tony!E30:E44)=7))
Column E would contain a date, such as 01-07-07 or 1st July 2007.
In the D Column, keywords such as "Crazy" are concise and standard. However regarding dates, am I better off finding a formula that looks for cell content (Contains "july", as opposed to ="July"), or using a month function (but getting it to work)? How can I do this?
View 3 Replies
View Related
Sep 27, 2007
I tried to modify a sumproduct formula given previously by Kris which is to sum base on strings, to solve another problem but with no luck. My attempt without success is in BLUE column whereas the correct answers should be that in the RED column. Basically my problem is that if a condition is true, I want to concatenate the values of 4 cells with a space in between them.
View 4 Replies
View Related
May 13, 2008
I would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies
View Related
Aug 24, 2009
I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.
Can anyone help me out in writing the Visual Basic code?
View 8 Replies
View Related
Apr 23, 2008
I have a need to sum values in the right column of a 2 column table based on multiple criteria of the values in the left hand column. The table has 500 rows.
The two criteria are:
1 - that the cell contains a specific text string ("product a")and
2 - that the cell contains a text string for the month in question ("Jan")
The other limitation I have to work with is that the formula is in a different workbook to the source data. I have a worksheet that lists the external workbook/sheet and have been using INDIRECT in the DSUM.
I read through the examples here:
[url]
Basically they recommended DSUM as the most efficient.
I can get the DSUM working, but it needs the workbook to be open before it can evaluate the expression and therefore I get an error when I select to update values upon opening the workbook.
So in summary I wondering if anyone can show me how to write a SUMIF formula based on the above criteria.
View 9 Replies
View Related
Aug 17, 2006
Need a way to count the number of times a certain bit of text occurs in a range based on a given criteria? Say I've got a column of names and another column with true or false in it (next to each name), how could a do a total of how many times 'TRUE' occurs next to each name?
View 3 Replies
View Related
Oct 17, 2007
I've attatched the workbook here with some dummy values in place of financial figures (We don't base our profit on RAND() formula's )
Essentially, I would like to search for CAN in A and SER in B and if they're found, to alter them to SYD (A) and CAN (2). It's essentially an automatic find and replace, but based on 2 criterias rather than one.
View 9 Replies
View Related
Mar 1, 2014
I'm trying to set up conditional formatting in a spreadsheet where there are 3 possible correct letters that can be entered into a range of cells and if one of these letters is not entered then I would like the cell to be highlighted.
To be more precise, it's an accommodation spreadsheet and in each cell if someone needs a room for that particular night (each night is represented by a separate column) then a letter is used to indicate what type of room they require i.e. "k" is a king room, "s" is a Suite and "t" is a twin. I don't want to force people to have to select from a list which would be the obvious solution to this as it is simply too cumbersome for the amount of data entry we do.
However i want to ensure that if someone accidentally enters anything other than t,k or s then the cell is highlighted. We work in large spreadsheets covering months of dates so to manually have to find any errors is hugely time consuming.
View 10 Replies
View Related
Jun 27, 2012
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
View 6 Replies
View Related
May 12, 2006
Trying to count the number of fields that meet a criteria base in 3 different columns. Worked on this for few hours but still keep hitting the wall of no right answer. I have attached an example sheet.
Trying to find the Number of fields that are not blank in the range of B9:B28 and have no date (are blank) in range(C9:C28) and have a number that is < 7 in range (D9:D28).
View 3 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
Jan 2, 2013
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
View 11 Replies
View Related
Sep 17, 2012
I have 2 excel files, let's call them 1.xlsx and 2.xlsx (excel 2007)
File 1 is the file where I want data copied into and file 2 is the file I want to copy data from.
File 1 has certain text strings in every say 5th column in always row 2. I want to find those strings in file 2 and if the string is found, go 6 rows down, copy the cell, and paste it into file 1, 8 rows down the text string. this is the code I came up with, but it doesn't work
Code:
Option Explicit
Sub get_data_from_2()
Dim i As Long
Dim j As Long
Dim FinalColumn As Long
Dim RngFrom As Range
[Code] .......
the bolded part gives me an error.
View 3 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
May 17, 2014
I'm using Excel 2007
I have the below data in sheet 1
In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD
DEB/CRED
ACC1
ACC2
[code]....
View 7 Replies
View Related
Oct 25, 2013
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
View 1 Replies
View Related
Mar 21, 2006
here is my text strong:
PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
101915744. MMU - DO NOT BREAKAP:
I want to return text starting with ALLOC but stop at and not include AP.
View 10 Replies
View Related
Jan 18, 2007
| item1, item2, item4 |
Elsewhere I have a set of points for these like this:
| item1 | 4 |
| item2 | 2 |
| item3 | 3 |
| item4 | 1 |
(the |'s represent cell borders, if it isn't obvious)
What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.
I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
View 9 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
