Update Description Of Item If Text Matches Using IF?
Mar 20, 2014
I've got 2 columns.
Column A, Item Description
Column B, Item Cost
In Column B, it will display the item cost as either a numerical value, (example $1.00) or it will say "disco" for discontinued.
I want to update the item description in Column A by adding the work "disco" if Column B display's "disco". If column B display's any numerical value (0-999999999) then I would it to do nothing.
what IF formula I need to accomplish this?
View 6 Replies
ADVERTISEMENT
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
Sep 17, 2013
I'm working from a sales ledger file whereby I want all invoice data in one row although the Item Number & Item Description are on the first row. Rows 2-10 (for example) are the actual details of the invoice: Invoice #; Date; Customer ID; Customer Name; QTY; etc.
Is there a formula or easier way than copying/pasting the "Item Description" to the first column (J in this case) without any related invoice data?
View 2 Replies
View Related
Mar 19, 2014
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
View 2 Replies
View Related
Feb 3, 2014
I have a userform that search for a value in column A, it displays the results in the listbox. Example: Search for value "111" and it brings me back the following results back in the listbox "111 David 35". So this means 3 columns matching data is returned.
I want the following to happen if I double click on the item in the listbox it needs to update the value selected in the worksheet eg. strikethrough the row on the sheet to show item has been selected/done.
Code for my listbox populate:
[Code].....
View 1 Replies
View Related
Jul 2, 2007
Suppose I have a department List, and I made a list from that column (Department List), and I enter more staff and selected a department for them during registering, now one of the Department List Item change, How is it possible to change all the entry which has that department?
For more info see the attachment.
View 7 Replies
View Related
Sep 2, 2013
I have about 70K rows of data. This is a pull from a CRM system and one of the columns contains Free text which may or may not contain a reference ID.
So basically, I need to be able to identify which (if any) of the 60 or so Reference ID appears somewhere in the text. It may be at the beginning, somewhere in the middle or at the end.
So i created two formulas:
=ISNUMBER(SEARCH("MEBIG3",$P:$P))
I created 60 or so of these columns, all with a different Reference ID (MEBIG3 being the ID in this instance). I put the Reference ID in the Cell 1 of each of the columns. I then got a True in the cells wherever I matched the Ref ID to in the description field.
I then created another column with this:
=IF(AO4=TRUE,$AO$1,IF(AP4=TRUE,$AP$1,IF(AQ4=TRUE,$AQ$1,IF(AR4=TRUE,$AR$1,IF(AS4=TRUE,$AS$1,
IF(AT4=TRUE,$AT$1,IF(AU4=TRUE,$AU$1,IF($AV$2=TRUE,AV3,IF($AW$2=TRUE,$AW$1,IF(AX4=TRUE,$AX$1,
IF(AY4=TRUE,$AY$1,IF(AZ4=TRUE,$AZ$1,IF(BA4=TRUE,$BA$1,IF(BB4=TRUE,$BB$1,IF(BC4=TRUE,$BC$1,
IF(BD4=TRUE,$BD$1,IF(BE4=TRUE,$BE$1,IF(BF4=TRUE,$BF$1,IF(BG4=TRUE,$BG$1,IF(BH4=TRUE,$BH$1,
[Code] ........
So wherever there was a True, it returned the Cell at the top of the column which has the Ref ID in it. I put a pivot table and it worked, but the problem is that Excel just can't really handle the number of calculations. The file keeps crashing and becoming corrupt (There are 70K rows, times 60 or so columns). Also I have actually run out of columns I can add in the nesting, I really need more.
Any way of doing the whole piece of analysis all in one Cell. So basically it says "If ADM1 appears anywhere in Column P return ADM1, If ADM2 appears anywhere in Column P return ADM2 and so on".
View 8 Replies
View Related
Aug 1, 2014
How can I update column B for each item selected in a Listbox populated as below:
Code:
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
Next i
For each item selected in Listbox1, I want Column B to show "CONFIRMED"
View 3 Replies
View Related
Sep 25, 2006
I import a database with mostly unique records which has a description field that includes, among other things: Model Name, followed by predominant color. Unfortunately, there are no seperate fields for the Model Name or Color, e.g.:
DESCRIPTION
8070 CALLING WATER RED
508001 EYEDAZZLER LILIANA CARAMEL
502034 EYEDAZZLER LILIANA RED
#402022 DIAMONDS IN THE DARK RED
402024 DIAMONDS IN THE DARK PALOMINO
Is there a way to create a summary table which would count the number of occurences of the Model Name and the number of occurences of the color?
Fortunately, there are no more than 25 Model Names, and no more than 10 colors or color/combinations, so we could compare against two lists.
View 9 Replies
View Related
Jan 23, 2009
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data.
However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View 2 Replies
View Related
Jul 16, 2014
I have a price list from my supplier with the new prices.
I then have my Accounting software where I need to update the cost and retail prices. The problem I have is the Accounting software has allocated it`s own Unique ID for each item. So in order for me to bulk import this I need to keep this unique ID with the Actual Product ID together otherwise it will duplicate the product.
Here is a example
Sheet to be updated (Cost and Retail only) from PRICE LIST SHEET
Uneque ID
SKU
[Code].....
View 1 Replies
View Related
Jun 19, 2014
Check the attachment, i could not make out this using vlookup, how to overcome this problem.
test.xlsx
View 2 Replies
View Related
Jul 5, 2012
Trying to create a button that, once clicked performs the following task:
Checks whether the text in a cell in the column X2:X40 matches the text in a cell in the Range A2:U14.
If it does, the formatting of the cell in X2:X40 (eg background) must change to match that of the corresponding cell in A2:U14.
Only 1 cell in the range will match 1 cell in the column
For example:
Before click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a clear background
During click:
matches these cells
changes X4 so that it also has a yellow background
After click:
Cell A9 has "John" written in it and a yellow background
Cell X4 has "John" written in it and a yellow background
View 3 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
Apr 29, 2009
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
View 3 Replies
View Related
Jan 29, 2010
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"
View 4 Replies
View Related
Mar 18, 2014
how to find partial match of text in 2 different columns.
For example:
Column A:
Boston
Chicago
Los Angeles
New York
Detroit
Miami
[code].....
I want to be able to create a column that finds all the matching cities from A in B.
View 9 Replies
View Related
May 21, 2008
I have an excel file, and on some rows, the "B" column contains the text " Total:" (it does have a leading space). I would like to loop through the first 200 rows (maximum length of the file) and delete all the rows that contain " Total:" in column "B". I have tried:
For i = 1 To 200
range("B" & i).activate
If range("B" & i).value = " Total:" Then
rows(activecell.row).select
selection.delete shift:=xlUp
End If
Next i
When I step through that code, it just skips over the IF function as if B1 does not equal " Witness:"
View 3 Replies
View Related
Aug 11, 2014
I want to count the number of times partial text strings occur in a cell adjacent to another cell containing specific text.
A B
FB Milton v Town PHOTO
BS Fairfax v South
BS North v Town
BS Milton v South PHOTO
FB North v Milton PHOTO
FB Milton v South
I'm looking for the number of times "FB" and "Milton" occur in Column A when "PHOTO" also appears in Column B in the same row. From numerous searches I've tried figuring out VLOOKUP, DCOUNTA, etc.
View 6 Replies
View Related
Dec 19, 2008
How to check if a range of cells matches specific text.
Example:
A1 - dog
B1 - cat
C1 - mouse
I want the formula to give me "yes" if dog is present.
=IF(A1="dog", "yes", "no") is for one cell. However if I do it this way:
=IF(A1:C1="dog", "yes", "no") I get an error.
View 7 Replies
View Related
Oct 3, 2011
I'm trying to restructure a list of files at work into a format that makes some sort of sense and can be filtered into useful information.
Part of what I need to do, is match up all the drawing numbers that a certain project references. The numbers are formatted like "####A##" ex. 1234A01,1234A02,5678A01, etc. The cells that I need to extract drawing numbers from contain some description or other text (not just the drawing number) so I need to extract the drawing number from that cells value. (Ex. I need to get the drawing number 1234A01 from a cell whose value is "blah blah 1234A01 blah blah blah")
I was thinking I could just search the string for "####A##" but I'm not really sure how....I tried instr, but I think its searching for the literal value of "####A##" rather than treating the # characters as wildcards...
I tried:
Code:
if rngNames.Cells(intx, 2).Value Like ("*####A##*") then
set intStart = instr(1,rngnames.Cells(intx,2).value,"####A##")
'....
end if
but intStart remains 0, so this method is not working...
View 3 Replies
View Related
Jun 19, 2013
Doing some job costing on our lowes invoice and am using SUMIF and asterisks to account for all the different names that get used for properties by the cashiers. An example of that range column is:
67TH AVE
19112 PONCA
51ST
STERLING
9420 67 ST
1503 SW 13TH
51ST
10818
19112 PONCA
19112 PONKA
I have formatted this whole column as text and get strange results from sumif when there are exact matches. For example the "10818" string is uniform through the spreadsheet and returns a result of 0.00 if entered as "*10818*" for criteria but returns the correct amount if I use "10818" or 10818 with no quotes.
It seems to be related to the text strings that contain just "numbers" as I'm having no trouble picking up exact matches when there are alpha characters.
View 2 Replies
View Related
Oct 28, 2013
I have about 10,000 part numbers all with descriptions. These descriptions do not follow a uniform logic;
=============
Part 1: 5x10 Red Cotton candy
Part 2: Yellow 6/18 x7 TTC x11 Picture Frame
=============
My dilemma is that I need to figure out a way to remove everything aside from the "5x10" and "6/18 x7 TTC x11" from these descriptions. I just need the the item sizing.
What I've come up with so far is sorting items by description and working with batches of similar descriptions, then doing replace "Red*" with "blank". It works but it still takes me too much time and it's not perfect.
My question is there something else I can do that is easier and more accurate?
View 2 Replies
View Related
Dec 1, 2011
Searching a text string on Sheet 1 to see if it contains any item in a list (on Sheet 2 Column 1) and if so return the value of Sheet 2 Column 2 next to the list item found?
It's a budget problem: Sheet 1 has my downloaded Visa statements. I want to categorise all items.
Eg Any item containing the string 'safeway' is categorised as 'General expenses'. So the list on Sheet 2 has an item called 'safeway' and in the next column 'General expenses'.
And for the item on Sheet 1 'BPAYN BUPA AUSTRALIA BPAY MBF monthly' I have an item in Sheet 2 that is simply 'BUPA' with category 'Medical expenses'.
View 6 Replies
View Related
Mar 24, 2014
I'm looking for a macro to jump to and select a cell if it matches the text in another cell based on a drop down list. So I have a drop down list in cell c57 and a form control search button right next to it. I want the user to be able to select from the drop down, click search and then jump to the cell that matches what they selected. I would like the macro to search from B:60 - B629.
View 4 Replies
View Related
Feb 16, 2013
Excel Userform
VB:
'enables user to click [U]highlight and select[/U] an item in ListBox1 and ListBox2 item (same row in index) is also [U]highlighted[/U] (highlighted only not selected)
Private Sub ListBox1_Click()
ListBox2.ListIndex = ListBox1.ListIndex
End Sub
Question: Is it also possible to enable a user to click to select an item in ListBox1 and ListBox2 item is also selected simultaneously (same row in index). Is there excel vb code to do this?
I think the code may be along the lines of the ListBox SelectedIndex property. What would be the Excel VB code equivilant for the ListBox SelectedIndex property, if so?
View 8 Replies
View Related
Sep 30, 2009
I have a spreadsheet!
I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).
What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.
Example:
Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae
Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune
So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.
View 12 Replies
View Related
Jan 14, 2009
I need to create a formula that will give me True or False if text in a cell matches any cells in a range.
For example:
Column A and B have text:
Account
Date
Name
Species
Column C has other and sometimes matching text:
Name
Deal
What formula would I use to find out if all cells in Columns A and B are represented in Column C?
View 3 Replies
View Related
Apr 8, 2013
I have a spreadsheet where user can search for information inside a search box and the appropriate rows are returned using formulas.
I have a drop down list (Category: Model) in the search box as well as a search field (Category: Program, cell D2). Underneath the search box, search rows are returned with column categories: Program, Model, etc.
What I require is that if a user enters the specific program into the search field D2, then the dropdown list would automatically choose which model that program belongs to based on the returned row.
For example, if I typed "engine" into D2 and pressed enter,
Under the records section (Row 14 and beyond), the following record will pop up:
Program
Model
Indicators
Engine
F-16
3a
Based on the record, F-16 would be chosen from the dropdown list. Is there any way this can be done?
View 6 Replies
View Related
May 20, 2014
numberdesc
1_______yellow
1_______yellow
1_______blue
2_______purple
2_______purple
3_______green
3_______orange
4_______black
I need some way that can identify when the item in the description column doesnt match the first item of the same number- for example, here the 1-blue and 3-orange would be flagged because they should match the 1-yellow and 3-green.
I need to do this on a much larger scale (approximately 20,000 data points), so I wanted to create a formula or macro that could do this for me.. I thought making a reference page with would work but I keep getting an error.. I haven't done VBA in a while, so I may have syntax errors.
If Range("A2:A9").Sheets("Sheet1") = Range("A2:A6").Sheets("Ref") And Range("B2:B9").Sheets("Sheet1") = Range("B2:B6").Sheets("Ref") Then
Range("C2:C9").Sheets("Sheet1") = "x"
End If
View 2 Replies
View Related
