I have one workbook (workbook A) with data in it (the data is never in the same place i.e. same cells) and one I want to send data to (workbook B)
In workbook A the data is displayed as follows:.....
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
I need a formula to extract data after "<li>Color: " & before"</li>"
A
B
1
ORIGINAL
RESULTS
2
blah blah blah<li>Color: White</li>some informations
White
3
blah blah blah<li>Color: Red</li>some informations
Red
4
blah blah blah<li>Color: Black</li>some informations
Black
5
blah blah blah<li>Color: Red</li>some informations
Red
I have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.
[Code] ....
Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.
This is an example with the real values I want to extract Example.xlsx
This is the formula that works
[Code] ......
Example: Milburn Corp- -Invalid date range
Desired outcome: Milburn Corp
Lets say Milburn is in cell A1. I used the following formula which did not work. I kept getting a Value error message
=a1,find("- -",a1,1)-1)
Code such as the one below carries formulas in columns 3 and 10 down to new rows when inserted. However, I need to allow a user to overwrite the formula in col 3 with any text value. But if the user overwrites the formula on the last row, and then manually enters a new record below, then the code in the last row of col 3 does not get copied down. Is there a way to search upward in columns 3 and 10 until reaching the last cells with formulas, and apply the formulas to the new cells in cols 3 and 10 on the new row? Or, is there a way to store the formulas:
=IF(A21<>"",B21,"") in column 3
=IF(B21<>0,IF(C21<>B21,A21&", "&LEFT(C21,2),A21&", "&LEFT(B21,1)),"") in column 10
Storing these somehow would be good if the user overwrites the formulas in the very top row of data (row 2).
If Intersect(Target, Me.Range("A:A")) Is Nothing Then Exit Sub
On Error Goto CleanUp:
With Target
If .Value <> "" Then
Application.EnableEvents = False
.Offset(0, 3).FormulaR1C1 = "=RC[-1]=R2C3"
.Offset(0, 10).FormulaR1C1 = "=RC[-1]=R2C10"
End If
End With
CleanUp:
Application.EnableEvents = True
I have a column of text strings in a2.a??? this is my data. Each text string has a persons name somewhere in it with other text around it.
In column c2.c??? i have text names of people names im searching for. eg. bob,jane,harry
what would be a pratical use of the vlookup function to return the name in column b2.b??? if the name from C was located in the string in A this is a non case sensertive requirement.
i have is that at current i have a load of data (6000 cells worth)
What i need to do is to go through the data and highlight anywhere where it may have the word "in/outsourcing" (The data is based in cell AA which is just a description of text)
Other than doing Ctrl F through each cell is there a faster way in which excel can search through the cells and even highlighting the cell where the word occurs.
I thought if the word "sourc" is searched this would then pick up both
I have two worksheets that Data might be present,
Holdtickets and "another sheet"
Here is my code:
Code:
Dim i As Long
Dim LastRow As Long
Dim wsEachSheet As Worksheet
Const strDestSheetName As String = "Hold Tickets"
Const strDestSheetName2 As String = "Enter Order"
[Code] ........
I don't want to show the location as being the worksheet "Hold Tickets" so i had to remove it from the search, But the data is still important
I need a macro code that will locate the data in "another sheet" and return the values from there, but if it is also present in the worksheet "Hold tickets" then
This part of the code will need to look like this:
Code:
Sheet1.Range("B7").Value = wsEachSheet.Name & "Hold Tickets"
I am using the following code to find a NAME in my work sheet and to enter their status. But this macro is not compatible if a duplicate or Similar name exist.What I need is I need this macro to activate the search NAME Cell and then a VB box with OK & NEXT button has to appear. if the activated NAME is exactly the one I am searching, then I will click the OK button then it should display the "Enter Employee Status" box and as continue in my code, But If I press NEXT, the macro should search the next similar NAME and activate it, This process should continue until I click the OK button. (Same like CTRL+F function with an additional Status entry button)
Code:
Sub Button1848_Click()
Application.ScreenUpdating =False
Dim BeginCol AsLong
[Code]....
I want extract data from 1 database and I know how to do it with the Pivot Table. Unfortunately, doens.t show it the way I want. I have attached one file with my problem - pls, check sheet RPT.
View 3 Replies View RelatedI have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.
View 2 Replies View Related I have a pivot table, and I would like to be able to have a formula or vba to extract the data to separate sheets. The pivot table looks similar to;
Row Labels
Count of FileSemester
John Smith
8
[Code].....
The pivot table consists of employee name (John Smith), followed by class code and qty. I have a separate spreadsheet for each employee and what I need to be able to do is to extract the class & qty into these spreadsheets.
I'd like to have a formula in Column C which would extract the quantity from either Column A or Column E depending on the value in Column D.
A ---- B ---- C ---- D----------------E-----------F
QTY -UOM----------Tariff #----------QTY 1-----UOM1
46----PCS---------3402.12.5000----94.3--------KG
85----PCS---------3402.12.5000----174.25-----KG
4-----PCS----------3701.10.0030----4.8---------M2
5-----PCS----------3701.10.0030----1-----------M2
15----PCS----------3701.10.0030----1-----------M2
[URL]
You can download the excel from the above link.
This excel includes some text data, in each cell there is an email address along with additional text. I only want to get the email addresses from the entire data.
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
I have a list of email addresses in column A (eg: andre@mailburst.co.za , pete@ vodacom.co.za). Is there a way to ectract say only the addressesending in "@vodacom.co.za"?
View 9 Replies View RelatedI am having trouble getting a formula to work with exactly what I need. I need a formula to find the total quantity of each product. The file I'm searching from does not list the product name or description in the subtotal column. The host file is similar to below and I just want the product name and total quantity in the new worksheet. I will have a list of all products we carry in the new worksheet and I want to pull the total quantity ordered for each item, and if none were ordered return a quantity of 0. The program the host file is exported from also lists a header at the top of each page, so for example there may be a header in between the last occurrence of Product D and the Subtotal.
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Product A Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product B Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Product D Description Sub description Date Ordered Quantity Ordered
Item Subtotal Total Quantity
My problem is that whilst dealing with large datasets, i'd like to be able to create a macro which will give me a pop up box to input different types of keys words to search within a dataset. Once these keys words have been found, i would like excel to highlight the entire line so it can be extracted into a new worksheet.
View 3 Replies View RelatedIn column A I have various part numbers with alph-numeric characters. In column B I have a similar list. In column C I have the quantities for the part numbers in column B.
What I need is for say a macro or forumla to look at each part in column A and match it with the part in column B and in column D insert the appropriate quantity from column C.
I'm not sure if this falls under say a filter, extraction or search type of function.
sheet 2
column C is list of chemicals (to give context)
column A is a list of the publication numbers they apear in
column B is GOING to be the list of publication titles they are in
sheet 3
column A is a complete list of every publication number in our library
column B is the corresponding publication title
what i would like to do is extract the title from sheet 3 and input it into column B sheet 2 corresponding to whatever pub number is listed in column A of sheet 2
the code i have so far is in column b sheet 2 "=INDEX(Sheet3!B:B,MATCH(A2,Sheet3!A:A,0))"
now the problem i have is this works perfectly when only one pub is listed in a cell in column A sheet 2 but when there is more than one pub listed (ie "pub number" next line in cell "another pub number") it gives a N/A result.
is there anyway to call up multiple pub titles from the multiple pub numbers within one cell
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies View RelatedI need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.
I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.
Here is the source data:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
In the attached sample sheet, Col B has data that will more often than not be duplicated. Each Line will have a corresponding value in Col L.
What I need to be able to do is this:
Starting from row 2 (important) is find duplicates and then place the corresponding figures for each from Col L as a Sum into Col N.
So '45678' appears twice in Col B and the Sum of the figures in Col L for '45678' is 1, so 1 will now show in Cell N2.
'55685' appears only once in Col B so it will have the "Sum" of 12 in Cell N3.
'99954' appears 3 times in Col B and has a sum of 9 from the Col L values so that will be displayed in Cell N4 and so on.
Hammer Sample #2.xlsx
I was trying to figure how to write a formula that would return the contents of a cell in a table.
Here is the situation....
I have a 10 row by 10 column "grid". the top and left side of the grid contains a random number from 0 to 9. The numbers do not repeat.
It would remind you of a block pool.
If I have two numbers, 0 to 9 , is there a way to return the cell that corresponds to the intersection of those numbers in my table?
It would be both a Vlookup and an Hlookup which I can't do.
I need help creating a formula to find data in one column that starts with different letters and counts them up.
For example one column would contain the words amoung others:
DPDE
DNPD
EPRO
EOTH
YBRC
YUND
In the other column I want to count up the amount of times a word starts with the letter D or E or Y. The count comes from another spreadsheet, which the CMRF Tool (See attachment) is pasted at the bottom of.
The Monthly case report file is the file I want to get the count from. These would come from Column O once the data is there. The count would end up in column D of the CMRF tool which is pasted at the bottom of the monthly case report file.
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:
F - Michael 8735
M - Dave 093
automate a data table I set up in excel. I need the formula to look through a bunch of raw data I have, and add quantities in each row, but only from the rows that have the same machine numbers. I attached the document of raw data I've collected, and on a separate tab, an organized table. I would like this table to automatically be updated with the appropriate data, as I enter it as raw data.
View 3 Replies View RelatedI am very green with Excel. Can anybody tell me what 'data sheet' means and where to find it? =If('data Sheet'!$G$11=1,'data Sheet'!$Bi$197,'data Sheet'!$Bj$197)
View 5 Replies View RelatedI have this data set,
A
B
C
D
E
1
mzi
2
5
6
12
[Code].....