Return 2nd And 3rd Instances
Feb 24, 2010
I have text strings containing 3 commas at all times (see below for example). All instances of the commas are names at either end of the comma (Last, First). My request is how would I be able to retrieve *only* the 2nd and 3rd names (in separate cells) and omitting the rest of the text?
Example:
A1 - Source
2/1/2010 Random - Text - Containing 1LastName, 1FirstName 2LastName, 2FirstName 3LastName, 3FirstName Some Other Text 555555556 1234567890 1 0 0
B1 - Requested
2LastName, 2FirstName
C1 - Requested
3LastName, 3FirstName
View 9 Replies
ADVERTISEMENT
May 12, 2014
I have four columns, Play Date, 1st, 2nd, 3rd.
I have a total of 364 rows of data for this table.
My output table looks like the following:
{mod note - HTML not working and removed}
What I want the output from a formula(???) to do is put every 'Play Date' for each 1st, 2nd, & 3rd column for every number from 0 to 9. So under Column A for zero and 1st I want every date that is listed in the source table in column 1st to list out. All I can get is the first instance of a date and nothing else.esn't use that would be great.
FYI the formula that I used to get the one entry in column A row 4 was this:
=INDEX($AK$4:$AK$367,MATCH($A$2,$AL$4:$AL$367,0))
View 2 Replies
View Related
Mar 21, 2013
I have a spreadsheet where cells L4 W4 have headers (no spaces in this range), e.g. Cust1, Cust2, Cust3 etc... etc...
Column C contains product names ....
Each product will have a value under each heading (but not all), e.g. Product A = Cust1:10 Cust2(blank) Cust3:4 Cust4(blank) etc... etc...
What I need is a formula that looks along the row for each product and returns the header name if there is a value. Therefore, in the example above the formula result would be Cust1Cust3 ....
View 2 Replies
View Related
Dec 31, 2007
I am looking for a formula that will return the cell numbers of multiple instances of the item found using VLOOKUP. For example if I am looking for
"A123", this may occur in more than one cell in the search range. I want to be able to return all the cell reference numbers of that instance.
View 9 Replies
View Related
Jun 23, 2009
Sheet 1 has one column
Customer Number (unique)
e.g.
111
222
333
444
Sheet 2 has two columns
Customer Number
Email Address
e.g.
111-----billgates@msn.com
111-----billgates@microsoft.com
111-----billgates@hotmail.com
222-----davidbeckham@mufc.co.uk
222-----davidbeckham@fa.co.uk
333-----me@test.com
444-----you@test.com
Now, when I do a vlookup on sheet one to obtain all email addresses for customer 111, it only returns the first email address - e.g. billgates@msn.com
Ideally, I would like;
Is there a function that can achieve this?
If not, can the multiple occurrences be returned in one cell, separated by;?
View 9 Replies
View Related
Nov 26, 2008
The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.
The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied
Private Sub FindAll()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............
View 4 Replies
View Related
Apr 28, 2009
i have a spread sheet which has a very complex simulaton in it. excel is not the ideal place for it, but it is not possible to port it wholesale to something more sensible. i have modified the code so that it is possible to run two copies of the spread sheet (in two seperate excel processes) at one time. Due to the dual core nature of the machine this has almost no overhead.
I have created a controlling spread sheet that is cabable of launching multiple copies of the simulation and starting the code executing. the problem is the controlling spread sheet always waits for the code to return before it continues, hence it would open two seperate versions of excel, start the first simulation executing and then wait until it completes before starting the next one. is there any way of starting a function in another work book without waiting for the execution to complete?
View 2 Replies
View Related
May 8, 2006
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
View 14 Replies
View Related
Aug 17, 2012
I'm using WinXP with Excel 2003 - I have a column of highway sign description data (16k+ rows).
Example:
Curve Arrow Right
Curve Arrow Left
Turn Arrow
Reversing Curve Arrow Right
Winding Road Arrow(plus many more unique entries)
I'm using SUM and COUNTIF to total the number of times "Curve", "Reversing", "Turn" and "Winding" appear in the column.
My formula is:
Code: =SUM(COUNTIF($F11:$F16196,{"*CURVE*","*REVERSING*","*TURN*","*WINDING*"}))
Which works great EXCEPT what I really want is the number of cells with any of those key words, not the total count of those words. The example above should be 5, but since row four contains more that 1 of the key words I'm getting 6.
View 4 Replies
View Related
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Jan 26, 2009
With the following formula, I can lookup all instances of D2 and get the average of all its corresponding values in column A (see attached). But how do I lookup a specified number of instances (say the first three) and get the average for those values in column A? For now, we’ll just have to assume that there are more than three instances of D2 on the worksheet I attached.
=AVERAGE(IF($B$2:$B$26=$D$2, $A$2:$A$26))
Would the numbers need to be in an order by date to lookup just the first three instances?
View 6 Replies
View Related
Jan 13, 2013
I have a large dataset with a company name in Column A and a Town in Column B, e.g.
ABC Company London
ABC Company London
XYX Company Swindon
STJ Company Bristol
STJ Company Bristol
STJ Company Bristol
DEF Company London
DEF Company London
DEF Company London
and I need to identify every instance where the same town appears for each instance of each individual company and add in Column C incrememnt the town name, so the above should end up looking like this:
ABC Company London London
ABC Company London London-1
XYX Company Swindon Swindon
STJ Company Bristol Bristol
STJ Company Bristol Bristol-1
STJ Company Bristol Bristol-2
DEF Company London London
DEF Company London London-1
DEF Company London London-2
I'm thinking I need to use some sort of lookup, but because of the volume of the instances of each company I have no idea how many instance appear in the data set. How could I go about achieving this and incrementing the town names in Column C automatically for each instance?
View 6 Replies
View Related
May 28, 2014
What I want to do today is, I want to get the extension (Type) of a file from the string. I have managed to get formula for that.
PHP Code:Â
=IFERROR(RIGHT(C12,LEN(C12)-FIND(".",C12)+1),"")Â
But the problem is, if the name of the file also have any dots, it is not giving correct result.
For Eg, if the string is ABCD_V1.0.mpp, I am getting 0.0.mpp not .mpp
NB: The file name may or may not have dots. So cannot tell that, it is always the second instance. Need to find the last dot and trip the things before that
View 11 Replies
View Related
Dec 13, 2013
Basically, I have a table with rows as person, and variables as features. If a person meets a feature, the that cell writes 'X', now, I hope to create a master variable that capture all the features, basically counting how many features a person has met. For example, Joe has three features; "have balls," "cool guy", "good with ladies". In the cell in question would be "X" "" "X". And if I count, then it should be 2.
View 1 Replies
View Related
May 9, 2014
I have four columns with data. I need to Correlate columns 1 and 4. eg. Column 1 = All instances of Column 4. I must do this by matching the values of Column 2 to Column 3. There will/can be multiple instances of the same values in all columns.
View 1 Replies
View Related
Apr 28, 2009
I have four different sheets with email addresses in an excel file. There are some email addresses which are present in more than one sheet. I wanted to find these email addresses and remove its multiple instances.
I can't use the simplified version of vlookup, as I can't enter each email address to see if there is another instance.
Is there a way where the excel sheet can search within itself for duplicate values and if not delete/at least point them out, so that I can delete them manually?
View 3 Replies
View Related
Jul 2, 2013
I am having trouble pasting between two spreadsheets in different instances of Excel.
I am copying a range of cells and then attempting to paste using ctrl + v/ right-click + paste in a workbook in the other instance of excel. It just selects the area where the cells would be pasted but nothing appears.
The only way I can get it to work is by using the clipboard task pane and clicking paste from there however this isn't a long term option as I need to code this into a macro. I also don't understand why there is a difference between pasting from the clipboard panel and pressing 'ctrl v'.
There are not macros running at the time and pasting into another program e.g. notepad works fine.
View 2 Replies
View Related
Jul 11, 2009
I'm using the following code which is working well. It finds an instance of the string "A83". Once it finds it, it moves down x number of cells and populates it with a new string. It then moves down another x number of cells and populates it with another new string, etc.
The problem is, it is only looking for "A83" one time and performing the appropriate actions at that point. What I need to do is look for *every* instance of the string "A83" and perform the appropriate actions.
View 10 Replies
View Related
Feb 5, 2010
I have one main workbook that, when opened, opens three other workbooks each in its own instance of Excel. I'd like to know how to close all three of these upon closing this main workbook.
View 2 Replies
View Related
Jan 7, 2005
I want to return the 2nd field for Jim in a single list:
Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM
I want to pull Jim's accounts to another worksheet:
HP
Toshiba
Lenovo
When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?
View 14 Replies
View Related
Mar 14, 2008
I have a problem, I have a formula which counts the number of instances that occur and assigns the value as 1 for every instance, however I want the formual to also recognise that if a number of instances occur in succession a value of 1 should also be assigned.
E.g. if a person is absent for 1 day the formula assigns a value of 1
if a person is off for 3 days in succession the formula assigns a value of 1
View 13 Replies
View Related
Apr 14, 2009
I need to count instances in a column where the error #N/A is returned. I thought that the following formula would work, but I'm getting #NAME?:
View 2 Replies
View Related
Feb 10, 2012
I am trying to copy a range from one workbook to another. The complex part is that the the two workbooks are different instances of excel and one of them is hidden.
In the below code inSheet is a worksheet on a work book that is hidden.
Code:
batchSheet.Select
Range("K" & lr).Resize(1, 10).Copy
inSheet.Select
inSheet.Range("O7").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
A formula like this is working fine. The above gives me an error.
Code:
inSheet.Range("C16") = batchSheet.Range("D" & lr)
View 9 Replies
View Related
Aug 19, 2012
In a cell i need this info when
column a = month
column o = staff member
column m = discount given
if no discount is given column m will show 100%
i need the total of all sales made with a discount i.e not 100% and not blank, in a certain month, by a certain member of staff
step 2: i need the average of all these for each member of staff shown in a different cell
i already have the total sales counted per staff member so this will show me who is doing deals and who is doing the biggest deals.
View 4 Replies
View Related
Dec 5, 2006
I would like to count the number of instances of different schedules shows in one department. For example for dept. 1, I would like to know how many different schedules are in that dept. I have 48 schedules and 60 departments (numbered 1-60). I would like to know how many "different schedules" dept 1 has and so on. I don't think the frequency function will work. Is there a way I can use sumproduct to get the result I'm looking for? The information is on a separate worksheet in colums (Col. A holds the dept and Col. B holds the relative schedule).
View 9 Replies
View Related
Feb 13, 2009
40,000 rows,
Column A is a Port Code . . . always 4 digits
Column B is a 2 digit code representing a mode of of transportation.
I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .
View 9 Replies
View Related
Apr 17, 2009
Using Excel2007 / Vista
Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client
Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious.
But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence.
One week a client may not appear at all, or they may appear from 1 to 6 times.
I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.
View 9 Replies
View Related
Jul 28, 2009
I have an excel file with around 15 sheets. While i open it, i am getting 5 instances of the same file, that means after i open it , i could see 5 copies of the same file. If i make changes in one file, in others also its getting reflected.
View 9 Replies
View Related
Sep 10, 2009
how to label/define what I need so I'm not sure what needs to be provided...
I have two columns of data- phone number, city. Each row is an individual record of a call from that phone number to that city. And, I need to ID, for the eventual end goal of deleting, those phone numbers that called multiple cities.
View 13 Replies
View Related
Sep 13, 2007
I just installed excel 2007 and encounter a strange problem. When I open 2 or more excel instances from windows explore by double-clicking excel icons, those instances are not linkable, they seem absolutely independent to one another (in View/switch windows only sees one workbook). However when I open second file from within the first instance (Ctrl+O), I see 2 in the view/switch windows and it works fine.
View 9 Replies
View Related
