Extracting Data Via Locating Row And Column Number
Apr 27, 2012
Sheet1
DEPARTMENTLOCATION POSITION TITLEPOSITION NUMBERCODEKXNJCEO34500A3100KXNJCEO34501A3200DXDLMGR42001A5600DXDLMGR42002A5700NXNLCHIEF23001A1200
Data Range: A1:E6
Sheet2
DEPARTMENTPOSITION NUMBERLOCATION POSITION TITLECODEKX34500ANJCEO3100KX34501ANJCEO9100DX42001ADLMGR5600DX42002ADLMGR5700NX23001ANLCHIEF1200
Data Range: A1:E6
What I need is a formula (Not a VBA), is to reconcile both sheet2 and sheet1 ensure that the codes appearing for each position number in sheet1 gets updated based on the codes for each position number appearing in sheet2. So, for instance, the code for pos #34501A should be changed from 3200 to 9100 in sheet1.
The only issue with sheet2, though, is that the column number for position number could be different each time new data gets copied into sheet2 (thought the header information stays the same). I know that it can be done via Vlookup if the place of the column doesn't change each time, but I just
How to locate the correct column and row in a range of cells to search and then extract information based on certain conditions.
View 7 Replies
ADVERTISEMENT
Jan 12, 2010
Take a look at these columns below;
.00 810.00 .00 1,729.00 .00 1,594.00 .00 1,599.00 .00 498.00 498.00 .00 .00 1,852.00 .00 454.00 .00 50.00
I want to find the cell reference number of any value of column A (which is greater than 0) in column B. I could only manage to find values with my formula i.e.
=IF(A1>0,VLOOKUP(A1,$B$1:$B$9,1,0),0)
View 9 Replies
View Related
Feb 14, 2013
Im trying to develop a formula that sequentially numbers in column a depending on if there is a value in column B
We have documents at work that have alot of text with random spacing between that needs a sequential reference number so would like the formula to be able to look at the value in column b and if its not blank add a number . I would like this number to be the previous cell above + 1
the formula ive started looks something like =if(B10""), ???????,""
can not get excel to reference cell the last cell above with data.
View 3 Replies
View Related
Oct 16, 2007
how to get the function in excel 2007 for the following problem please . Col R i am cutting and pasteing from a web page info like this 1/1 or 13/14 or 9/10 . when i paste to my worksheet it comes out as 3-Mar or 10-Sep or 12-Dec , but everything 13 and above is coming out as per the web page eg , 13/14 or 15/15 . I am actually only wanting the first number or numbers to the left of the slash / mark , so col R2 might be 1/1 i want only the 1 to appear , 13/14 i want just the 13 and so on , they are only ever 1 or 2 digits ,
View 9 Replies
View Related
Mar 11, 2013
Macro for a repetitive task. I have data in every 3rd column (max of 11 columns) that I want to extract and append the data in column A in sheet 2. The data contains some blank cells and I'd like to remove them also.
I've attached an example in this workbook : tractdata.xlsx
View 4 Replies
View Related
Oct 24, 2008
How can I locate 1000 or the closest number from column 5, and all the data to the left of it? I have tried all the commands I could find, and nothing has worked for me.
This is the data I want to pull from the table.
1.463149.6271.0440.985999.87
View 11 Replies
View Related
Jul 24, 2012
I am having problems extracting URLs from data in Column (column A) and displaying them in Column B. I watched 'MrExcel's Learn Excel #664 - Extracting Links' on youtube [URL] (about 2.40 is where the final code is displayed) and it is the perfect solution with the following code:
Sub GetLinks()
For Each hl In ActiveSheet.Hyperlinks
Cells(hl.Parent.Row, 2).Value = h1.Address
Next hl
End Sub
However when i go to run the macro in Office 2007, I receive an error: runtime error 424 object required, and the 3rd row is highlighted when i hit the debug button. This is actually my first time using VBA but as far as I can tell, I replicated the exact same code which is supposed to populate urls in column B.
View 1 Replies
View Related
Jul 14, 2012
I have the following data in column b and I would like to extract into individual cells.
I would like to extract upto the first number, number text after first number etc
I have several rows and the data lengths are variable
Liverpool FC 2 Sheffield United 1
Manchester United 0 Everton FC 3
Middlesbrough 2 Manchester City 0
Norwich City 2 Chelsea FC 1
Oldham Athletic 1 Crystal Palace 1
Queens Park Rangers 3 Southampton 1
View 6 Replies
View Related
Jan 28, 2010
I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.
I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.
Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.
Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!.......
View 14 Replies
View Related
Aug 21, 2013
I have a workbook with a master data list including member names, member locations, member phone numbers, and various items checked out or on loan to members. I then have multiple sheets breaking down the data for quick reference. So for example Name, Member #, Location, Phone, Item A, Item B, Item C, Item D etc... I need to be able to have all the Members that are in certain cities displayed in it's own worksheet. I have played with the aggregate function, if and functions,... and I am dying. This is for a motorcycle club to keep track of who has ordered what, how much they owe, how much they've spent etc.
View 1 Replies
View Related
Jun 15, 2009
I sent this before but the formula I was given wasn't correct (as you will see from the file). In Column B of the second sheet (Data) of the attached file, I want to bring back the relevant name form the first sheet (List), which involves searching through the long descriptions from column A on the Data sheet for the name on the List sheet.
View 2 Replies
View Related
Mar 20, 2014
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
View 4 Replies
View Related
Feb 22, 2012
I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).
I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).
In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.
Code:
Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)
[Code] ......
So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.
View 2 Replies
View Related
Jul 1, 2013
I am using Excel 2010.
In my worksheet I have 'Column A' and 'Column B', In 'Column A' are product I.D. numbers. In 'Column B' is a text description of the product, whose I.D. number is in 'Column A,' and should also contain the I.D. number from 'Column A' somewhere in the midst of the descriptive text. However, some of these in 'Column B' do not.
I need to create a function that looks for the value in 'Column A' and determines whether or not it is present in the text of 'Column B'. Therefore, spitting the answer out in 'Column C' so that I can copy it down for 100,000 cells.
View 5 Replies
View Related
Aug 11, 2009
looking for an equation or macro that can extract numbers out of a sentence.
Eg, 'There were 300 children in a school, and 10 classes of children' I need to extrcat the 300 and the 10 into 2 cells.
There may be more than 2 numbers in longer sentences. Is there anyway to do this?
View 14 Replies
View Related
Mar 4, 2014
Cell A3 has the formula: "=MOD(H16-H15,1)"
Cells H16 and H15 are time entries
If H15 = 12:00pm and H16 = 2:00pm, the result in A3 = 2:00
Cell A5 has the formula: "=A4*A3"
In cell A4, I enter a number, say "4", which produces the result "8" in cell A5
Now...I want to add to the formula in cell A5 to make it "=(A4*A3)+A4". The result I WANT in this example should be 12 but no matter what, the answer always ignores the "+A4" part and always just gives me A4*A3.
So the question is, how do I extract the result "8" from cell A5 as a number to which I can add (or subtract...or ANYTHING) other numbers accurately?
View 3 Replies
View Related
May 14, 2014
I have a column with codes coming after a # symbol and want to extract just the code from the cell. The code could be 2 or 4 characters in length. Some examples:
#AA abcdef
From this I would want the formula to return #AA.
#ABCD qwerty
From this, I would want the formula to return #ABCD.
The #Code should but may not always be the first word in the cell, so it needs to find the # symbol and then take the next x characters until a space is found. Note that the reason code may be the only text in the field, so a space may not be there at all. For instance,
#RR
Should just return the #RR.
Edit: This actually has become more complex now. The cell could be
#AA{text}
And for that, I would just want the #AA.
The formula would need to begin at the # and return just the next x capital letters.
View 9 Replies
View Related
Jun 25, 2014
We are changing management systems here and in order to do so we need to provide the names and addresses of over 400 different people. Our current management system stores people's addresses as a whole for example; 32, Example Street, Town, County, Postcode.
I cant extract just the numbers because I only need the house number and not the postcode.
After searching several forums I thought I had solved it with this.
Cell A1 is the extracted address from current management system reading 32, Example Street, Town, County, IU13 8TY
Cell B1 is =LEFT(A1, SEARCH(" ",O15,1))
Cell C1 is =SUM(MID(0&B1,LARGE(ISNUMBER(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))*ROW(INDIRECT("1:"&LEN(B1))),ROW(INDIRECT("1:"&LEN(B1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B1)))/10)
That only gives me the number "3" though and I need 32.
Basically A1:A400 will be addresses as above and in cell B1:B400 I need just there house number at the start.
View 3 Replies
View Related
Jun 1, 2009
I need Excel to create Date's of Birth from following data in column A. If the data are 7 numbers long, I need to add a '0' to the start and have used the custom format of 0#######. This works fine but I need the data in column B to just display the first 4 digits. I have used =LEFT(A1,4) to do this, but it's not picking up the 0's which I've added.
View 11 Replies
View Related
Apr 30, 2013
Is there a way to extract a phone number out of a string of text. The phone number will always be expressed the same way (eight digits seperated by a space half way through) - 9999 9999. But the text before and after the number is random. Is there a macro to do this on a large scale? The method needs to be applied to hundreds of these strings, each on a new line.
An example of the string of text with the phone number underlined:
Alesdro 0 63PnantCey4281 5919 Aledio daSisaon' Brglry
View 7 Replies
View Related
Jul 4, 2014
I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquery
Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST
I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00
Formula, the text in B1 and need the price at A100.
View 9 Replies
View Related
Apr 27, 2007
I have a text field which contains multiple numbers ( reference #s and phone #s). I need to extract the reference # which will be either a 7 or 8 digit number. That number will not fall in any particular place in the string.
View 9 Replies
View Related
May 17, 2007
I am trying to plot data that I have read out of a PLC (via csv file) . The date/time data shows up as follows: 2007-05-14T02:15:00.020-07:00
In this example, the suffix .020-07:00 is extra information and causes problems with graphing the info. Also, the cell is a not a number value, and I can not force it to be a general, number format.
I would like to truncate the cell or extract the time out of it such that I can plot the PLC data in a meaningful "parameter vs time" graph.
View 4 Replies
View Related
Jul 14, 2014
"Type Mismatch error" in the macro below. I am trying to implement logic, where if value in column F, equals "1ma+89", then extract 89 only in column G. I also have other values in column F, where I have set specific rules for them, to extract numbers.
I have attached a worksheet with example data and highlighted red cells, represent incorrect output from the macro.
test2_14July.xlsm
View 4 Replies
View Related
Oct 2, 2011
I have a column of cells, each with a with text string that has three possible formats where I want to extract the total number of seconds.
The three formats are:
nnn hrs nn min nn sec (example: 1 hrs 42 min 58 sec)
or
nn min nn sec (example: 55 min 1 sec)
or
nn sec (example: 10 sec)
The result I want is a numeric value of the total number of seconds in the text string.
For example a text string of: 1 hrs 42 min 58 sec
Should give the result: (1 x 3600) + (42 x 60) + 58 = 6,178
View 9 Replies
View Related
May 10, 2014
I have a cell containing alpha and numeric. I would like to extract the highest number in the cell
the cell looks like this
the answer I would like is
====G99876554321000
9
T1111111100000000
1
I am using Excel 2010
View 3 Replies
View Related
May 14, 2009
I've got a table similar to the below (but with a few more columns & rows). I need 2 look at 2 cells one contains a $value (say $75,000) the other contains a code (say C). I need it to return the correct value - in this example 3.00%.
This is going to be too messy to use a vlookup, I'm sure there is something better. Is this where you use Match or Index ... both of which I don't understand how they work. Or something else ?
ABCD
$ 1 $ 50,000 1.00%1.50%2.00%2.50%
$ 50,001 $ 100,000 2.00%2.50%3.00%3.50%
$ 100,001 $ 250,000 3.00%3.50%4.00%4.50%
A B C D
$1 $50,000 1.00% 1.50% 2.00% 2.50%
$50,001 $100,000 2.00% 2.50% 3.00% 3.50%
$100,001 $250,000 3.00% 3.50% 4.00% 4.50%
View 9 Replies
View Related
Jan 12, 2009
extract the values from a field and inserting as column.
I have attached the workbook, with the output worksheet. The output sheet which is what i required as result.
I need to extract set of word between "/" and insert it in the exact column name, if any column name is not there we need to create that column and insert that word corresponding to that row and column.
View 2 Replies
View Related
Jul 6, 2008
I have a range of date values that span B1:BA1 and I need to find the next date value greater than TODAY() and return the column number.
View 14 Replies
View Related
Sep 15, 2012
I need a macro that identifies duplicates within a spreadsheet. Unfortunately, the utility provided by Microsoft is not adequate because the text strings that are potential duplicates are nestled inside other text.
Fortunately, however, there is a unique text string that will locate the duplicated data. Below are two examples:
Plus -- Zero Box ==> Zero Box Medium ID=7288 (submittedby Anna Johansson)
Basic -- Zero Box ==> Resolve Medium ID=6397 (submittedby Kazy Suzuki)
The data that needs to be identified as potentially duplicated is the four digit number (i.e. 7288 and 6397). You will notice that they are preceded by “ID=”.
The spreadsheet has over 26,000 rows of data and 1700 plus IDs. The characters ID= proceeds the four digit number in all cases.
I simply need a macro that will provide a list of the four digit numbers that are duplicates. This will allow me to easily locate and manually delete any surrounding, unwanted data from the spreadsheet.
The data that the macro needs to search is contained in and restricted to Column C.
View 2 Replies
View Related
