Extracting Records With Partial Text In Multiple Columns?
Jan 3, 2013
I have a questions regarding extracting records using a partial text search in multiple columns.
I would like to do a partial text search of the letter "j" in the Name column, and search for the number "1" in the Phone column and extract the corresponding records.
The formula I have been using is as follows: =IF(ROWS(C$2:C2)>$B$2,"",INDEX(C$2:C$6,SMALL(IF(AND(ISNUMBER(SEARCH("J",$K$25:$K$29)),
ISNUMBER(SEARCH("1",$L$25:$L$29))),ROW($J$25:$J$29)-ROW($J$25)+1),ROWS(C$2:C2))))
However, the formula is not working correctly.
find my sample data below:
A
B
C
1
Partial Text Search
[Code]....
View 5 Replies
ADVERTISEMENT
Mar 20, 2013
I have sales rep data with column headings as Sales Rep, Sales Date, Sales Item Code and Customer ID. There are only 2 sales reps in the data, I'll call them Rep A and Rep B. I need to extract rows where Rep A and Rep B have made sales on the same day to the same customer (it doesn't matter what type of sales). I don't want to extract sales by only one rep, it must be both reps on the same day to the same customer. I have tried a pivot table but it identifies all sales. Can this be done?
View 2 Replies
View Related
Aug 10, 2014
I have a range of columns i.e. 23 columns (i.e. B through X). Someone can write records in these columns (starting from B21).
Duplicates are considered the rows with similar data in columns 3 and 11. I know about the removeduplicate method and works really well but i want the duplicates not to be removed. Instead another column shall be checked for date of entry (user will entry date in format dd/mm/yyyy). The newest entry will change the value of the cell in column 4 (islatest column)to TRUE while all other records will be FALSE. This will work with the filtering of data on a pivot table on another worksheet.
View 7 Replies
View Related
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 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
Feb 7, 2014
Certification and Training tracking.xlsx
I want to create a certification only list on a separate tab of training that has been completed where a certification has been issued (as indicated by a "Y" in the "Certification?" column on the training tracking tab) and then populate from some of the fields vs. all of the fields.
What I have now, only pulls the first occurence, not all occurences. I saw that I could have identified the multiple columns that needed to be populated, but it didn't work either, so I'm fine putting a separate vlookup in each column.
View 6 Replies
View Related
Apr 23, 2007
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
View 9 Replies
View Related
Aug 14, 2009
I have a set of workbooks, with multiple sheets within each which I receive each month from field units. The formats are identical.
What I want to do is to extract data from one column on one specific sheet within each workbook to a summary sheet on a new workbook. In the snapshot below, I would like to collect data from the "Actual" column (in yellow) and then paste it on a summary sheet.
Monthly reportingEntity 13. Risk reportingJanuaryMarket Risks - LoansJanuaryTargetActualVarNumber of competitors 110%Market share 1%1%0%Ranking in market 110%Concentration of portfolio – region-wise (number)1%1%0%Concentration of portfolio – region-wise (value)1%1%0%Market growth rate 1%1%0%Inflation rate 1%1%0%
This would appear in a new summary worksheet as below: (the cell labelled "Entity 1" would then appear as the column header in the summary sheet.)
Monthly reporting - summary sheet3. Risk reportingTarget rangeEntity 1Entity 2Entity 3Market Risks - LoansActualActualActualNumber of competitors 1Market share 1%Ranking in market 1Concentration of portfolio – region-wise (number)1%Concentration of portfolio – region-wise (value)1%Market growth rate 1%Inflation rate 1%
The steps I imagine are:
1. Open all workbooks in a specified folder (folder name upon prompt)
2. Search each workbook for a sheet titled "Risk Reporting"
3. Copy the specified cell (for the column header) and the specified column into a new sheet in a new workbook
4. Move on to fill up the next column, and so on ...
Also is there a way to order the copying so that the columns always line up in a specified order? (e.g. Entity 1, followed by 2, followed by 3 ...). One way I guess is to fix the column headers in my summary sheet, and then fetch the data from the corresponding worksheet, by matching the names.
View 9 Replies
View Related
May 5, 2008
i am having data in ColA & ColB in both Sheet1 & Sheet2
1) some records are both in Sheet1 & Sheet2
2) in Sheet1 itself some records are duplicated
3) in Sheet2 itself some records are duplicated
what i want is to extract the all the records in Sheet1 & Sheet2 in Sheet3
without duplication of records..!
View 10 Replies
View Related
Apr 27, 2014
The link to my file is: [URL] ........
I am using excel 2003.
My query goes like this:
I need to find out "total lifted quantity"(i.e. the sum of 'first state lifted quantity'+'second state lifted quantity'+'third state lifted quantity') for each "state" for each "size" and each "grade".
However when I am applying a pivot table (as seen in Sheet1) it is not giving data in lucid form. I don't want to split the data for three states in three different tables (as seen in Sheet 4).
View 1 Replies
View Related
Oct 25, 2006
At the moment i am having great trouble taking two sets of records, comparing the two based on certain criteria and then extracting any records (rows) that do not appear twice, that is unique records.
So here it is:
I have two lists of sales.
1. our list from our point of sale system with order numbers and other details, entered by the sales agent.
2. the company that provides the products we sell for them sends us a list of these sales back to us from their end so we can see outcomes of these sales, that is the progress of the order, like cancelled, accepted, etc
The two lists need to be compared to see what sales are missing from either one..
so, I could colour the second list RED and the first list GREEN and add the second list underneath the first list (on the same sheet) and then sort by ORDER NUMBER, which would provide a red, green, red, green, red, etc pattern and i could easily identify sales that are unique, but there are so many thousands of sales this manual process is impossible.
IS THERE A WAY TO AUTOMATICALLY COMPARE THESE TWO LISTS BASED ON ORDER NUMBER AND SHOW ONLY THE RECORDS THAT ARE UNIQUE? THAT IS, GET RID OF SALES THAT APPEAR TWICE AND LEAVE ONLY RECORDS THAT I HAVE TO LOOK INTO WHY THEY ARENT ON BOTH SALES SHEETS...
Here is an example of the data i am using:
ORDER #
1630923-
1634849..
1634849..
1634972+
the numbers do NOT have the .. - or + next to them, thats just there to show you what list each is from.
so as you can see the order numbers with ".." next to them are reconciled, in that they have a partener record and do not need to be shown at all..
and the order numbers with "-" next to them are from our list
and the order numbers with "+" next to them are from THEIR list
i want to end up with a list like this:
ORDER #
1630923
1634972
1635643
1641970
1648112
1649716
1653854
View 10 Replies
View Related
Jan 12, 2010
I have created a database at work which collates various information on the sale of products. Part of this information is the date that each particular product is up for review (In DD,MM,YYY) format. Using the internal clock function I have produced a formula that calculates the numeber of days until expiry.
However, using this information I want to be able to run a macro that looks at accounts that have for instance 5 days left until expiary and populate a new worksheet with various information extracted from my 'main' database. For example, search all the porducts that are due to expire tomorrow and then populate my new workseet with data from cells such as
Product 1 = Cell A1, Cell a2, Cell a6, Cell a7,
Prodcut 2 = a1, a2, a6, a7,
Product 39 = a1, a2, a6, a7
View 2 Replies
View Related
Mar 19, 2007
I have a master list that is updated in real time by linked worksheets from several people. While my actual report covers 7 managers and 7 different products (“contract types” in this example), I have summarized and simplified the input in the attached file. There are three pertinent dates – the date assigned to the manager (col A), the date completed by the manager (col F), and the date approved by the executive (Col G).
These dates will span month ends and I need to be able to provide monthly reports that provide details on the number of contracts and the dollar value all contracts assigned and completed by the manager during the month (say, February) as well as any contracts assigned in any month which remain uncompleted at the current month-end (e.g. the $7,500 Smith contract assigned on Feb 21st that remains uncompleted and the Feb 26th $200 Jones contract which was uncompleted at Feb 28th, but completed in early March, in this example). Can this be done directly in a Pivot table from the full master list, or will I need to have separate sheets in my workbook where I extract the pertinent records for that month?
View 2 Replies
View Related
Jan 23, 2013
I have a spreadsheet full of data and I need to extract only those lines of data which can be identified by the last three letters in a cell. I am adding an example spreadsheet which highlights the rows I am trying to extract based on the contents in column B but only where column B ends in KY.
Example data extract requirement.xlsx‎
View 5 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
Feb 19, 2008
i would like to find code for a macro(s) to delete selected columns and delete row by partial string..
due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..
View 8 Replies
View Related
Dec 18, 2013
Is there a way to do a COUNTIF on just partial text?
I know how to count how many cells in a column contain the text "cars" for example, but how would i write a formula that include cells with other text as well?
For example if 55 cells have "cars" as a value and 20 cells have "fast cars" as a value
I would want the formula to count 75.
Basically, it needs to count how many cells cars appears in, in a specific column, regardless of whether its alone in the cell or part of a sentence.
View 11 Replies
View Related
Apr 16, 2009
I am looking for a lookup to look for text within Column A that matches text in column D and brings the word in that is in column E ...
View 9 Replies
View Related
Jul 11, 2014
How to check for a partial match looking in one cell, A1 for example, and checking to see if the contents of B1 show a partial match (example attached).
I can't use the vlookup because I only want to look at cells specifically, not a table/list. Please also note that the attached example has no formulas.
Forum Example.xlsx‎
View 5 Replies
View Related
Apr 25, 2012
I am copying text (contest results from another website) and posting into excel. As it is formatted on the original site, it posts the place, followed by a period, then the team name, then a dash, then the score.
Example ... 4. Smokers Purgatory - 662.2858
To place it in our database we need the period removed from the place, and the dash and score removed.
Example ... 4 Smokers Purgatory
Is this possible to automate in excel or is there a way to format so that excel seperates the place, team name and score? Or should I be looking for a different method?
View 6 Replies
View Related
Jun 18, 2014
I have about 100 text files from which I need to import specific sets of numbers into excel. The part in the file looks like this
Mg24(MR)1917.4198.5
Mg25(MR)250.453.6
Mg26(MR)264.464.2
I need to import the numbers in the 1st row but they need to be transposed. and than of cause I need to do that for the other 99 files I have.
View 14 Replies
View Related
Oct 30, 2008
I have VBA code that imports a text file using a fixed width separator within the code. The code is working just as I need it to work and placing the information/data in the columns that I have specified in the code.
The issue I have is I only want to import certain data that is contained within the text file. It is going to be a little hard to explain, so I have attached a sample of the text file with dummy data to better explain. The file is broken up into different segments. Each segment is identified by a “Base Number” in column A of the text file (once it has been converted into an Excel file).
For my example, I only want to import data for Base Number 38451. The information for this segment is easy enough to identify when the data starts and when the data ends. As in this example, the first occurrence of 38451 begins at row 257. Once the first instance of this number is identified, the first row of data that I require is always two rows after.
I think the coding should go something like this, but I just can’t figure out how to put it into my
If cell in column A = 38451 then
- start importing from 2 rows down
- continue to import until next row in column C is not = date
If row in column C = blank then
- search in column A until cell = 38451
If cell in column A = 38451 then
- start importing from 1 row down
- continue to import until next row in column C is not = date
Else
- stop import
I have included two files. (1) the text file with the data (2) the Excel file. There are two tabs in the Excel file “ReconSheet” (contains the data that is imported from the text file) and “Desired Result” (this is how I want the end product to look).
View 5 Replies
View Related
Jun 8, 2012
I have the following code to find text on a sheet. It works perfectly except that I want it to find partial text in a cell as well.
Thus, if the text in a cell is Goodwood, it will find it if I type in Goodwood as my search. I also want to find Goodwood if I only type in Good.
The Lookat:=xlPart does not seems to work.....
Dim rFound As Range
Dim iCount As Long
Dim Text As Range
Dim MyRange As Range
Sheets("All Codes").Visible = True
[Code]...
View 2 Replies
View Related
Mar 28, 2013
I have a long list of categories in the column A (seen below). To this list i want to assign keys that will support with further calculations (in a column B).
An example of such a key would be in the case of the given example "ACTION SPORTS" and "FOOTBALL". The whole list of these "keys" hast 17 elements.
It is evident that i will need some kind of a partial match. The one i have found on the internet, limittin itself just on the first or last number of characters wont work.
Would it be possible to select my "key - list" (all 17 of them). Then excel would asign a key, whenver it would find a partial match, matching the key?
Appendix:
The Data list (Column A):
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
[code].....
View 6 Replies
View Related
Oct 21, 2008
I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:
Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson
For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.
If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.
View 9 Replies
View Related
Aug 10, 2006
I have a problem, in a table, the cell H5 has this formula. .=IF(E5=20, COUNTIF(G5,">=10y")+COUNTIF(G5,">=12y") +COUNTIF(G5,">=14y")+COUNTIF(G5,">=16y")+COUNTIF(G 5,">=18y")). every time that G5 has a value >= "10y"or "12y" until "18y", H5 should add 1 or 2 until 5. when I fill out C5, with an answer value in G5 of + 18y, H5 = 5, everything works fine. writing 02/08/1988 in C5 the result in H5 is 5, but when I change the value of C5 for 02/08/1999, H5 should be "0" but it continues with the value 5.
View 7 Replies
View Related
Apr 9, 2008
consider the following master data
1AB Cat
2HB Dog
3BX Rat
1ABC Tiger
Now I have the following data, which will lookup the master data presented above and return the corresponding animal - For eg.
1AB29489284 CAT
1ABC0395935 Tiger
2HB29492492 Dog
The above CANNOT be done by VLOOKUP, since the class - Tiger, needs to be differentiated from CAT by the 4th letter. How can the above problem be solved???
(This question recently came in one of the Recruitment Exams)
View 3 Replies
View Related
Jun 3, 2014
I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula
=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)
However, it works with some text but won't work for some.
For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.
View 2 Replies
View Related
Dec 18, 2013
I am trying to search partial txt with in data range.
View 4 Replies
View Related
Jan 2, 2014
Below is a small sample of Column A from a spreadsheet I'm working with. All cells in the column begin with a 6 digit number:
115383_BOSTON
115384_NEW_YORK
115385_PORTLAND
115402_LOS_ANGELES
115403_WACO
115010_SAINT_PAUL
115011_SAN_DIEGO
115130_WEST_NYACK
115021_CHAPEL_HILL
What formula would allow me to enter only a 6 digit number and return the entire cell contents, i.e, search for 115403 and get a return of 115403_WACO ? I have tried INDEX and MATCH, but I'm not getting the results I need.
View 7 Replies
View Related
