Extract Range Of Cell From Another Spreadsheet To New One?
Aug 20, 2014
Usually I use data connections (using http://www.....) to pull several worksheets(tables) from another spreadsheet. Currently I had a set of worksheets that created in an unstructured manner, the tables are not entered from Row 1 (see attached mockup). Besides using VBA programming, if there a simple way to connect and extract these tables to a fresh spreadsheet for computation. So the users can continue to perform data-entry and this consolidated spreadsheet will refresh whenever open for reporting and visualisation.
View 4 Replies
ADVERTISEMENT
Jan 29, 2014
Scenario:
A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook
A3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)
I want to extract text from RIGHT till the word "by"
So the result must be:
B1: by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook
B2: by Steven Pressfield (Apr 20, 2011) - Kindle eBook
B3: by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook
View 1 Replies
View Related
May 29, 2014
I have a very large spreadsheet that was exported from an ecommerce site with close to 1000 products. I have one column that I need to extract some text from. This column holds all of the html from the product description and is huge. I only need to extract the actual description of the product, but am having a very hard time figuring out how to do it. I've tried using the mid, left, and right function; but not all of the html is the same so it's not really working the way I need it to.
I have multiple tags throughout the html that I can use with the mid function, but there is more than one occurrence of them. So, how can I tell it to start at the 4th occurrence? I've spent countless hours searching, but I'm a complete novice when it comes to excel and I don't even know what to search for. I end up looking through sites that explain how to pull the Y out of XYZ, which is what I need, just on a much larger (and more complicated) scale.
It was suggested that I set up a macro that will find the 4th occurrence of the word, and then uses the mid function to pull the data out, but when I try to find the word, it says it doesn't exist even though I can see it right in front of me.
View 10 Replies
View Related
Dec 17, 2011
[URL]
My Excel Spreadsheet has columns named "Hospital Name | Overview | Address | Phone | Fax | Contact Person 1 | Contact Person 1 Title | Contact Person 2 | Contact Person 2 Title | Contact Person 3 | Contact Person 3 Title"
There is the list of hospitals starting from "Abbott Northwestern Hospital". I have two things in my mind to extract the information. If you click on the first hospital name "Abbott Northwestern Hospital". Below is an example text which I would like to transfer it automatically in columns given above;
This text should go in column named "Overview":
Abbott Northwestern Hospital bring twins into the Twin Cities -- along with triplets, quadruplets............
This text should go in column named "Address": 800 E. 28th St.Minneapolis, MN 55407
This text should go in column named "Phone": 612-863-4000
This text should go in column named "Fax": 612-863-5667
This text should go in column named "Contact Person 1": Jeffrey D. (Jeff) Peterson
This text should go in column named "Contact Person 1 Title": President
This text should go in column named "Contact Person 2": Daryl Schroeder
This text should go in column named "Contact Person 2 Title": VP Operations
This text should go in column named "Contact Person 3": Sandy Schmitt
This text should go in column named "Contact Person 3 Title": VP Strategic Development, Allina Hospitals and Clinics
View 9 Replies
View Related
Sep 1, 2012
I am trying to write some code that will extract several pieces of data from MSN Money and print into a spreadsheet.
So could outline the code (or the general idea) that would be needed to extract one data point from the internet (an example using MSN money would be great). I haven't been able to find a simple example that highlights how this works in VBA without the extraneous confusion.
To be clear, I don't want to use macros or the standard excel query tools as these won't, I believe, scale well and serve my purpose as I build up into extracting much more data.
(For example, I want to know how to extract 2011 revenue from this page: [URL] ........)
View 7 Replies
View Related
Jul 18, 2006
I have attached an excel spreadsheet
how to set a macro to extract the following datas from it.
Game Title
Description
URL
Picture can be set at 1 side to the right of the URL
View 3 Replies
View Related
Mar 15, 2013
I am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B,
else if the first letter occurs from H-M, display H-N in cell B
else if the first letter occurs from N-R, display N-R in cell B
else display S-Z in cell B.
View 2 Replies
View Related
Nov 27, 2012
I used to use Excel many many years ago for all my basic business databases and pivot table reports (so was at a basic competency level) and want to use it again but it has been quite a while and I can't remember how to do the following.....
I want to build a database of sales and purchase info in say 'sheet 1' - let's say 30 different headed columns and then many lines of different information under each column.
In 'sheet 2' I want to create an invoice template of my own design (I can do easily) for printing.
This template will need to extract say 10 bits of information from a particular row and then put them in a certain place on this sheet.
How can I type in one piece of information in a cell on sheet 2 (say the invoice or line number of the database) and that will then trigger all the other needed information from that particular line in sheet one onto this template?
I know it can be done but for life of me can not remember the formulas to use for these other cells in sheet 2 based on the info put in that one 'trigger' cell
View 4 Replies
View Related
Mar 28, 2014
I have a folder with multiples excel sheets
Destination : C: Project CustomerExcel
I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.
New sheet would contain the information of each sheet eg. Column A2= file name
And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.
VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
[Code] .....
View 5 Replies
View Related
Nov 4, 2012
I wish to Automatically copy the TEXT that is written from Spreadsheet 1 cells D5 to F5 to Spreadsheet 2 cells F5 to J5 .... a similar range of cells.
Is there a formula I can use or do I need to venture into the programming side of things.
View 7 Replies
View Related
Feb 16, 2007
I have a range that contains 3 cells on top act as titles ie TYPE NUMBER and AMOUNT.
I need to extract a range from the original range where the TYPE changes.
eg
TYPE NUMBER AMOUNT
OSF 1002 10.23
OSF 1004 15.68
SA 1006 100.25
SA 1005 17.89
CW 1112 15.56
So for the above example I would need the 2 OSF entries and its corresponding NUMBER and AMOUNT to another location on the worksheet. The same for the other TYPEs. The number of entries for each TYPE varies so I will not in advance how many entries of each TYPE I will end up having.
View 4 Replies
View Related
Aug 8, 2012
I've tried several ways and came close to the extract with the 2 formulas below. If there is a way to combine these 2 formulas, how to combine or any other function to complete the extract?
View 1 Replies
View Related
Sep 6, 2005
Assuming that the entries are text values:
For the next to the last entry:
=INDEX(A:A,MATCH(LOOKUP(REPT("z",255),A:A),A:A,0)-2)
For the last entry:
=LOOKUP(REPT("Z",255),A:A) .....
View 14 Replies
View Related
Jun 23, 2009
I have following data from which I want to extract only numbers.
Abc ch#25981Abc ch#25982Abc ch#25983Abc ch#25984Chq#25894Chq#25895Chq#25896Chq#25897Aed chq#25897 Bounced
View 10 Replies
View Related
Jun 19, 2007
I have a problem for extracting dates of leave from the attendance. A sample WB is attached. In the attached WB, I want to extract date(s) on which leaves were taken.
View 9 Replies
View Related
Nov 30, 2007
I would like to know if it is possible to extract just the number of a row from a range. for instance range("c5") would give a row number of 3. Its probably simple but its evading my attmept to work it out.
View 4 Replies
View Related
Aug 4, 2009
Is there a simple way to find all rows in a 25,000 row worksheet, that contain zip codes from another, smaller, worksheet.
View 9 Replies
View Related
Sep 22, 2007
I have a an excel file which has 2 columns of data. 1st column indicates the date. 2nd column indicates the data associated with the date.
I would want to create a code that is able to extract the dates that is able to let user select the date range and its associated data and copy it into another worksheet.
But it seems that my below code fails. Can anybody give me some advice?
For eg in the attachment file, the Raw Data worksheet contains all the infomation and i wish to copy all the data from 8thJuly07 to 30thJuly07 to another sheet named Edited Data.
Sub date_choice()
Dim i As Integer, j As Integer
Dim end_date_last_occurence As Integer, No_of_end_date_occurence As Integer
Dim strt_date_1st_occurence_row(1 To 10000) As Variant
Dim end_date_1st_occurence_row(1 To 10000) As Variant
Raw_data_last_row = Range("A" & Rows.Count).End(xlUp).Row 'Get the last row no
Sheets(1).Name = "Raw Data"
start_Date = InputBox("Enter Start Date", "Start Date", "dd,mm,yy")
end_Date = InputBox("Enter End Date", "End Date", "dd,mm,yy")
View 8 Replies
View Related
Jun 16, 2008
I have data of different accident year. Now I need to identify different Accident Year and use them in Macro to do certain things. For example in the file attached I am using them in Msgbox. Now to acheive that I first extracted unique values using advanced filter on Column C and later use the different value to POP up the Msgbox. (This is achieved). Now I want to skip this part of doing Advance filter to extract unique value. Is there a way I can know unique value of data through the code itself and use them in program. here is what I am doing currently: I have delted the most of the data cause I am concerned with coulumn containing acc_year only.
Sub test()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:= Range("C2" _
), Unique:=True
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C3").Select
Do While ActiveCell.Value <> ""
MsgBox ("The Year is " & ActiveCell.Value)
a = ActiveCell.Row
Range("C" & a + 1).Select
Loop
End Sub
View 3 Replies
View Related
Jan 14, 2010
I am using Spreadsheet control on User form and i just want to set Range as we do for worksheets. but unfortunately it is not working here. and generates the error
Set rng = Spreadsheet1.Range("A2:A" & Spreadsheet1.Range("A" & Spreadsheet1.Rows.Count).End(xlUp).Row)
here spreadsheet1 is spreadsheet control and Error is "Type Mismatch"
View 9 Replies
View Related
Feb 18, 2014
There are multiple duplicates of different part numbers in a column. When I autofilter the column, it shows 1 selection for each part number. I am trying to extract this selection fromm the autofilter dropdown box. I want to use it elsewhere in a dropdown box.
View 8 Replies
View Related
Apr 28, 2014
I have a long string that has a list of digits in the middle that I want to extract. The string is variable length and the number of digits I want to extract is 5-7. I also have slashes in between the numbers I want to extract. I need a UDF that allows me to extract the 5-7 digit number from the string and restrict around the slashes (i.e. if two sets of digits in the string match the condition for extraction, extract the one around the slashes.) For example my original data is like
1. aaa/12345/aaa/123
2. aaaaa/123456/aaaaaa/3423
3. 323/aa/1234567/aa
and I want for results
1. 12345
2. 123456
3. 1234567
Is there a UDF that allows me to do this?
View 14 Replies
View Related
Apr 12, 2008
I have a column of data in the following format:
Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)
etc
I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.
For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.
I can do it in excel by:
(Assuming data is in cell V36)
'in colum x
=FIND("(",V36)
'in column y
=FIND(")",V36)
'in column z
=MID(V36,X36+1,Y36-(X36+2))
in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)
I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.
View 8 Replies
View Related
Jan 16, 2010
Hi all im having a tough time trying to figure out a way to select a range (which will be the entire row )based on a condition that will be data selected from a combobox and display that range in a userform with spreadsheet contol 9
what i have so far is a worksheet with all the data.
i have a combobox on a userform that is prefilled with data from a specific column
every thing is working except when i select the entire row how do i change that to a range that i can use in the spreadsheet control 9 that is added to the userform.
View 11 Replies
View Related
Dec 9, 2008
I want to combine & I may be dense, but I just haven't gotten the hang of building multiple functions yet, especially when it comes to knowing when to add zeros & ones, for the index & match functions, as an example.
indexing a range from another spreadsheet, but when I tried to use it again on a different range & copied & pasted and changed the ranges, I get #REF!, which I know means I screwed something up, I'm just not sure what & I don't know what the 1's & 0's mean.
=INDEX(RiskLevel,MATCH(1,INDEX(FREQUENCY(E5,RiskScore),0),0))
View 7 Replies
View Related
Feb 24, 2009
I have two ranges of arrays "arr1" and "arr2".
I need to show all different elements between two arrays.
Attached the sample.
View 7 Replies
View Related
Jan 18, 2008
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK
AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do:
- is to extract the month and assign it to a new string
- extract the year and assign it to a new string
- extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
View 2 Replies
View Related
Apr 15, 2008
i have a spreadsheet with a list of students in it and next to it i have a column with their current score and the letter equivilant (A*-U)
so say
joe bloggs 615 B
Joe bog 750 A*
etc
is there a way if I list the grading boundaries for it to work out how many marks are required for the student to reach the next grade?
boundaries are
A* 728
A 669
B 610
C 554
D 467
E 383
F 298
G 214
U under 213
View 9 Replies
View Related
Aug 26, 2006
Trying to pass an array to a procedure to use as criteria in advanced filer.
My code below is raising this error:
Run-time error '1004':
The extract range has a missing or illegal field name
rngData. AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCrit, CopyToRange:=rngDest, Unique:=False
Private Sub btnOK_Click()
Dim arrValues()
Dim lngI As Long
Dim lngX As Long
With Me.lstAccounts
If .ListIndex <> -1 Then
For lngI = 0 To .ListCount - 1 ................
View 9 Replies
View Related
Sep 10, 2009
I currently have a navigation menu with 9 options however these options are based on Range names that I have set up.
The concern is that if someone deletes the cell that contains the range name, obviously that option in the navigation won't work, nor would you want it to.
However if someone re-enters a cell that you would want linked up with the navigation there's a lot more action involved.
What i'm looking for is a macro that can be used through a button, Idealy i'd like the macro to search through column "B" and look for key labels (ex: "Ground Floor", "1st Floor" "2nd Floor" etc.) and have each button set up for it's respective search data (ex. Ground button searches only "Ground Floor")
View 10 Replies
View Related
