Excel 2007 :: Searching Range Of Cells For Certain Characters And Displaying Results
Jun 25, 2012
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HYEYDH/14
222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HDGTEY/56
333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
Supplier P/N
Buyer P/N
YHDHFF/58
555555
I am using Windows 7 and Excel 2007.
View 2 Replies
ADVERTISEMENT
Dec 10, 2013
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
View 2 Replies
View Related
Aug 15, 2012
I have attached a work book example of what i am trying to do.
Column D is what i originally did in terms of the formula and now i have to have a column display text dependant on what is in column A to C.
I was trying this formula =IF(b2>a2,"Start Target Missed",IF(c2="","Failed","Tracking")) However if this isnt working.
Is there anything i can do to change this?
View 3 Replies
View Related
Jun 25, 2012
My Excel 2007 worksheet contains a cell where a percentage is manually input. A freight cost is calculated based on the input percentage. Typically, the percentage is 3-7% but once in a while freight is excluded and the percentage is zero.
My issue is that when a 0 is input the cell appears blank and I would like it to display 0.00%. The remainder of the worksheet needs to have the zero display turned off.
View 3 Replies
View Related
Feb 6, 2013
I have a worksheet with five columns (A, B, C, D and E)
The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.
I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.
Example:
B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB
The script/code/formula must output the following:
D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4
I am using Excel 2003. Y
View 2 Replies
View Related
Feb 3, 2012
Using Excel 2007.
I have shapes in 3 different rnages of cells on a cell worksheet:
R1 = $D$5:$D & lngRows (row=32)
R2 = $G$5:$G & lngRows (row = 51)
R3 = $M$5:$M & lngRows (row = 50)
I need to set the properties of the shapes in each range differently The shapes in R1 are Left + 46 But the shapes in the other 2 ranges need to be just left My problem is in this bit
Code:
'Set properties for each shape in ShapeRange
For Each sh In ws.Shapes
I need to be able to set properties for each range separately instead of the entire sheet. Full code below (only Range $D at the moment - works)
Code:
Option Explicit
Sub AddShape3()
'Purpose: Add small rectangles to database table/fields for
' brainstorming and documenting relationships and queries
'
'Resources:
[Code]....
View 2 Replies
View Related
Jul 11, 2013
Is there a formula that I can put in AI that will mask the text character from text position 3 through the 2nd to last text position of each business name in AH? Where in the heck do you find XLGenie in the Excel 2007 ribbon? I have it installed and its in my addins list, yet nowhere to be found in the ribbon!
AH
13
ABC Rental
14
Professional Tool Service
15
Wonder Plumbing
16
Jim's Bar & Grill
17
Bobs Hair Salon
View 2 Replies
View Related
May 28, 2014
I am using Excel 2007.
Sheet 1 - I have columns filled with dates (weekdays only) For example 28-May, 29-May, 30-May up to 28-Nov. Above 10 rows below each date columns and each cell has some values.
28-May29-May30-May
0.50.50.5
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
1.01.01.0
Sheet 2 - I have Start date defined in B1 Cell, and End date defined in B2 Cell
I need defining the formula to sum all the cell values fall between the date range defined in Sheet 2 B1 and B2.
For Example if B1=28-May B2=30-May, I want the sum of all the numbers come under the range of 28-May to 30-May, with this above example, it should be 19.5.
View 9 Replies
View Related
Jul 29, 2008
I'm new to Excel 2007... whilst I'm managing to find most things I'm used to using in 2003, I have an issue in one workbook that I can't figure out.
When I click in some single cells (not all), that cell and several below it automatically highlight as a range... left-clicking initially highlights the cell, but as you release the mouse button, the others below it also highlight.
View 14 Replies
View Related
Apr 13, 2013
I have a range selected (A4:C15).I would like to deselect the range and make A4 the active cell. Is this possible without a macro?
With a macro, one could use:
Application.Goto Selection.Cells(1)
I generally select a large number of cells and would like to go back to the start cell in the range. I could do it by pressing the left and right arrow keys but is there a shortcut that will allow me to do the same?
View 2 Replies
View Related
Feb 7, 2012
I'm using excel 2007, here's what I need.
I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?
View 2 Replies
View Related
Jun 18, 2013
My group is putting "marks" in Excel (2007) work papers & sometimes they may want to add additional marks to those previously added in a selected cell. What I have below actually works, but I just got my VBA book last week & there has to be a better way.
Specifically, I've saved the "target" off in the same worksheet (That can't be good.) and delete it when I'm done writing it back. Can I save the original characters virtually, or to the personal.xlsb.
Code:
Sub addMarkInCELL()
'
Dim charCount As Integer
Dim charStart As Integer
Dim rngTarget As String
charCount = ActiveCell.Characters.Count
[Code] ..........
View 2 Replies
View Related
Jul 23, 2012
i am trying to create an easy to use search by macro VBA in excel.
I have a sheet of information where i need to find the data records from either one of the 2 search criterias: Name and NRIC.
Once a user enter either of this 2 search criterias, i need a macro to search through the data on Sheet "List" and return the results on the Sheet "Search".
Within the list, a search criteria may appear more than once, the search results should display all of the records row by row on Sheet "Search".
Would it be better to create a user form?
View 3 Replies
View Related
Sep 4, 2012
Using Excel 2007.
I setup a named formula: Spread1 =Sheet1!$E$2:$BE$2 where I entered % values to spread a an annualized figure over 53 weeks
On Sheet 2 I entered the amount to spread in $D$2 and in $E.. I entered my formula: =Spread1 * $D$2. Everything appeared great!
But then I deleted a few columns on sheet2 so first calculation is now in $G$2
Spread1: =Sheet1!$E$2:$BE$2
Calculations: Sheet2! $G2:$BG$2 (the values in I,J,k while summing to the total are in the incorrect weeks based on the spread on Sheet1, BF and BG have #VALUE! errors)
Seems odd, if I highlight "Spread1" in the formula bar and [F9] I see an array of 53 possible values and the 3 values I expect are in the place in the array (elements 5,6,7).
How I can defeat it with the possibility that a user may delete a column?
View 4 Replies
View Related
Jan 23, 2013
I have the equivalent of Vlookup code(I couldn't get vlookup to work correctly) I am using Excel 2007. I have an array 2 cols x 1652 rows. I have another column of 6264 items. If a text item in the first column of the array matches a text item (minus 3 characters) in the longer column, I place the matching row from the array next to the item in the longer column. Whats not working is that the text never gets put in the cell.
Code:
Sub Macro1()
'
' Macro1 Macro
[Code]....
View 7 Replies
View Related
Mar 6, 2013
I created a data validation formula for an Excel 2010 workbook that would only display client names who were assigned to the manager that they selected. The formula works fine in Excel 2010. However, when I open the same workbook in Excel 2007, the data validation type is reset to "Any value" and the formula is missing.
OFFSET is an available command in both 2007 and 2010. When I try to add the formula as a data validation list in 2007, an error indicates that Offset cannot access data in a different table. I have not been able to find any documentation which restricts the OFFSET command in Excel 2007 but there seems to be some incompatibility between the two Excel versions.
The formula I used was:
=OFFSET(Clients!$A$1,MATCH(Summary!$A$1,Clients!$A:$A,0)-1,1,COUNTIF(Clients!$A:$A,Summary!$A$1),1)
Where:
"Clients" is a sheet which lists all clients under each manager."Summary" is a separate sheet which contains the manager's name. Only clients matching this name will be available in the employee's client list.There are 50 "employee" sheets, each which should use this formula for data validation in the client column.
The error we received seemed to indicate that the above command could not access data on another worksheet. It is not an option for me to copy the client list onto each sheet that uses this formula. Are there any other options?
View 1 Replies
View Related
Sep 19, 2013
I have a big DB with several rows and columns.
Column B has several card numbers (e.i. 7987654345678)
Column C has a location name (e.i. madrid)
Column L has a date (the date is when the card was used)
What I want to do is identify the cards that appear more then twice on the same date and the same place.
Card Number
Location
Date
123
LOC
18/01/2013
[Code] .........
I would like through a macro or pivot to show me the results of the 2 last rows, because those are the only ones that are exactly the same.
I have excel 2007 running on Windows7
View 2 Replies
View Related
Oct 8, 2012
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
EH21 6PQ - 1 Space (8 Char)
EH12 9HG - 3 Spaces (10 Char)
E1 8DF - 3 Spaces (8 Char)
LL5 1GH - 2 Spaces (8 Char)
L5 1FG - 1 Space (6 Char)
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct
EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle
E1 8DF - 3 Spaces (8 Char) - This would be correct
LL5 1GH - 2 Spaces (8 Char) - This would be correct
L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle
View 9 Replies
View Related
Mar 19, 2013
I need a formula to use on MS Excel 2007 to remove the English letters from a cell A1 and return only the numbers(if any) & Chinese characters to B1.
Please refer to the able below.
Example:
A
B
1
so l首先欢迎大家收听我们的科学与烹饪系列
首先欢迎大家收听我们的科学与烹饪系列
2
讲座的最后一次报告 有点难过这是最后一次了
讲座的最后一次报告 有点难过这是最后一次了
[Code]...
View 7 Replies
View Related
May 25, 2012
Using Excel 2007
I have six colums (Date; Skill; ST Hrs; OT hrs: Area; Type). I copied and pasted below a small portion of info from the spreadsheet. I know when I copy and paste into a post the information under the headers don't line up exactly...it appears offset, so basically "Date" is of course the date you see..."Skill"'s columns has the first item as nb-mech b..."ST Hrs" first item will be 0.00..."OT Hrs" will be 1.50...."Area"'s first item is "Log FM NO"....and "Type"'s first item is MX07
And there are 17,000 rows of this info and forever growing...a lot of repeated items in each column such as you can see "blaster" has multiple entries on 4/1/2012.
What I am attempting to do which I have not firgured out yet (i have tried array formulas but they bog down the pc forver and couldn't figure out exactly how to make it give me the results I needed...and I also tried sumproducts but that didnt get me what I wanted....and it is being requested of me to NOT make a pivot table but a chart.
What I am trying to create is is the following:
1 - cells to plug in a date range for example cell A1 I plug 4/1/2012 and cell A2 plug 4/30/2012
2 - drop down boxes for columns Skill; Area: Type, which show only unique records in each column
Once I have that, I could then filter all 3 itmes and have only items show in the date ranges I picked and present ONLY the SUMS of columns ST Hrs and OT hrs.
Once the SUMS show up, I can then have cells off to the side that will give a percent break down of what percent of the total hours (for the chose criteria) are ST Hrs and what percent of the total are OT Hrs....and this percentage break down will show up in a Bar Chart, per day chosen in my date ranges....I'm thinking one simple stacked bar per day in the chart.
So say, based on the info below, I wanted to see the total ST Hrs and OT Hrs from 4-1-12 to 4-2-12 for a nb-blaster in LOG FM NO and type MX06.....my result would be 0.00 ST Hrs...23 OT Hrs.....and my bar chart would be one simple bar peaked out at 23 on day 4-1-12.....and within the bar it would be 0.00% ST Hrs and 100% OT Hrs and maybe the total hours for each showing also..don't need that but it may be useful.. ..and no bar for 4-2-12 (because the criteria picked had no hours for 4-2-12)
I have been messign witht is all week and just can't nail it.. I do remember a while ago I would have been able to load the spreadsheet into the post and people could see it in excel...is that option to do that no longer on this site?
DateSkillST HrsOT HrsAreaType
4/1/2012nb - mech b0.00 1.50 Log FM NOMX07
4/1/2012nb - scaffolding0.00 8.50 Bogs NOMX02
4/1/2012nb - coatings mech b0.00 3.00 Log FM NOMX07
[Code].....
View 5 Replies
View Related
Sep 27, 2012
I am using a ComboBox in Excel 2007 in a UserForm. It is drawing on a row source which populates empty cells duplicates. How do I get the drop down box results to only show unique values and nonblank entries?
View 4 Replies
View Related
Apr 19, 2014
The following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".
I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.
Attached File: Example Find Missing Combinations.xlsm
View 2 Replies
View Related
Oct 1, 2009
I have cells that look like the following
PNHWAPN
HWAPN
PN
HCC
PNHWA
HCCHWA
PNHWA
PN
Not the most exciting data, but I need to identify which cells contain the sub string "HWA". Ideally the next column would have that string extracted into it or some kind of indentifier
View 7 Replies
View Related
Nov 26, 2008
I have a number of project files that are maintained on the network.
These files are named "XXXXXX Project Name.XLS".
XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.
We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.
I have created an excel program that extracts project numbers and related financial data from the report [from home office].
I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.
The problem is opening the file based on project number only, while the file names contain Project Name as well.
So, can I look up a file by only searching for first six characters of the name?
Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.
View 9 Replies
View Related
May 28, 2014
I've got an question about using excel macros to search for information in a database with certain restriction. In my attachment you can see an example of the database I use.
Example.xls
I've got a database with certain packages, the length and the width of these packages is known.
I've also got a database with certain products, the length and the width of these products is also known.
I want to use a macro which allows me to see which packages are useable for a certain product. A package is useable if the length/width is less then 10 bigger then the product. So in the example if the width is between 10~20 and the length between 40~50.
Originally I thought I could use a macro with advanced filter and having restrictions width >10 <20 and length >40 <50. But I can't fit this in as macro because I already get the width and length from the product from another database and macro so it can't say >40 only 40.
View 4 Replies
View Related
Feb 2, 2014
i have a spreadsheet that i use for work which has 1 page that contains all the data for the workbook. on other pages i use this data, and tables, to pull information to those pages or other pages which complete my work in a more timely fashion.
that issue i'm having is when i try to look up specific values within the data page tables and there are multiple values.
i need a way to choose different values rather than the "first" value in the instance of a vlookup which only finds the first value.
in the pictures i have picture 1 (vlookup samples) has a section where i can type in the "office" code and it will auto pull "switch name" and "switch clli" via vloookup.
in picture 2 (switches sample) i have created a table, which is on another page as stated above, but if i try to search for the example "LSGT5" it only returns a switch name value of "LFTYINXFDS0" and there is also a value of "LFYTINXFDS1".
there are many more within the table(for this office code alone) but this is just an example of how i need a way to choose the proper one or somehow have a drop down without having to create a bunch of tables for each office itself.
View 6 Replies
View Related
Apr 3, 2008
Is there any way to do this, using VBA etc? I can't make the filename shorter since it's a thematic map from Google Charts.
View 9 Replies
View Related
Jan 18, 2014
I am having trouble with some formulas displaying the results of the logical test as words.. For example.
=IF(E4>F4,100, IF(F4>E4, 200))
That works perfectly but I want "100" to be "Valid" and "200" to be "Invalid"
However...
=IF(E4>F4,Valid, IF(F4>E4, Invalid))
only results in the #NAME? error for me?
View 3 Replies
View Related
Dec 8, 2009
Hi, I'm sure this will be an easy one for most of you. I need to extract data from an array for each time I have recorded a specific vehicle registration. I have used VLOOKUP but this only gives me one result. I need excel to display every enrty for the registration I am looking for. I have attached a sample file which should make more sence.
View 7 Replies
View Related
Dec 18, 2009
i put in a vlookup formula and it doesn't display any results, it just diplays the formula with the = sign in the cell. it does that with any formula i enter. i tried hitting it and everything.
View 4 Replies
View Related
