List Extracted Rows Of Specified Data From Another Workbook?
Oct 4, 2013
I have two workbooks. One is called the Extraction Sample, which is attached, and the second is the Sorted Results workbook.
The Extraction Sample workbook contains a master sheet with data in a chart, which gets updated periodically. The Sorted Results workbook contains a list of specific extracted results from the Extraction Sample workbook.
The formula I am trying to create for the Sorted Results workbook needs to do two things:
1. Extract only the rows from the master sheet in the Extraction Sample workbook which contain the word 'Thing' in column D.
2. List the extracted rows on the Sorted Results workbook in numerical order using the data from Column B.
View 14 Replies
ADVERTISEMENT
Dec 24, 2007
I have 2 Workbooks. One is invoice data extracted from SAP and the other is a workbook I have to send to the client (has about 10 worksheets containing 5-10 different branches in each worksheet). The layout of the one I send to the client can not change so I am trying to import the values form the 1st workbook (SAP) into the 2nd workbook. What I do now is copy the first 4 columns of data for each branch and paste it into the 2nd workbook.
I was playing with a VLookup formula but I don’t think that is the correct formula. I need the second workbook to be populated with all the invoice info (Number, Type, Date, Balance) that appears in the raw data workbook.
=VLOOKUP(H14,'[abcbank raw data sample.xls]Sheet1'!$A:$A+'[abcbank raw data sample.xls]Sheet1'!$A:$E,4)
H14 is the branch number on the client sheet (I cut the column containing the branch # on the raw data and inserted it before column A).
Raw Data From SAP
View 13 Replies
View Related
Oct 7, 2012
I have a financial dataset which I need to "clean" before manipulating/analysing.
Each row of the data represents a completed transaction and the first step is to delete rows that are done with particular (internal) clients; the client data (the client's name) is in Column D.
Currently, I delete the unwanted data by autofilter and delete (code below), however this means the clients to be deleted are only defined within the code. I would prefer to have a worksheet within the workbook where the user defines the clients by adding or subtracting their name from a list.
I have created a dynamic range for the client list by inserting a Named variable along the lines of "=OFFSET(!$B$3,1,0,COUNTA($B$3:$B$200)-1," which works fine.
However how do I work this list into my code so that it works when new clients are added or deleted?
Current code for deleting unwanted client data:
Sub filterdelete()
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
With Range(Range("D1"), Range("D" & LastRow))
[Code] .....
View 2 Replies
View Related
Oct 5, 2008
NOTES BC37Size 3/8 x 3/4 x 3/8 40ft4038Size 3/8 x 7/8 x 3/8 20ft2039 40total041 42Size 3/8 x 1 3/8 x 3/8 20ft 43 Spreadsheet FormulasCellFormulaC37=MID(B37,22,2)C38=MID(B38,22,2)C40=SUM(C37:C38) Excel tables to the web >> Excel Jeanie HTML 4
Problem 1.
With the above I have managed to extract what I need into column C. The formula in C40 does not produce the desired result of 60. C37:C38 is formated as Number.
Column B is populated from a drop down box where the list was populated from a text file converted to Excel.
With that said, Problem 2
If the string that is currently sitting in B42 should end up in in B37 as a choice from the drop down, the formula I have in col C would not produce what I need.
I'm sure what I have in C is not the right approach. And I don't understand why the SUM formula will not work.
View 9 Replies
View Related
May 15, 2008
I have the detail as following in as column A
Employee Name
First line of address (Contain alpha numeric no)
Second line of address (Contain state and Pin code)
Phone no
Fax no
Email ID
Wed site address
First detail field (Contain alpha numeric no)
Second detail field
There is some 4000 Line item in this
Can this line item be arranged in the in Different Column form Column A-I
View 9 Replies
View Related
Mar 27, 2007
I have a excel spreadsheet that has employee names, email address and DOB. The DOB format is 23/03. I need a program that will lookup the DOB row and when it matches the value I enter it extracts the employee name and email address. Once it has this information I need it to copy into an already created email template and send its off. On some days there are more than 1 person with a birthday so it will need to send multiple emails.
View 7 Replies
View Related
Apr 3, 2014
I have VBA code the extracts data from a source and puts it into excel. I need to change the format of some of the extracted data so i can use it for a VLookup formula. Reference the attached example. Columns A AND B have the extraced data which are dates. In Columns C and D or E and F I have typed what I would like the data to look like so I can use in a Vloolup formula. Is there VBA or a formula that word change the format of the dates in Columns A and B to look like C and D or E and F. The changed format could be moved to any new cells or rows.
View 1 Replies
View Related
Aug 24, 2012
I need to split in Excel some thousands of data rows containing text and numbers information of different character length Data to be extracted ( parsed) is stored in a single column( the first one) and needed results look like this:
Data to be extracted
Needed results
Address
Name,Surname
Phone no.
Green Street no 16 Smart town Schmit Anders +3900098787867
[Code] .......
Data is composed by addresses, name of persons and phone numbers. Person's name is has two or three parts, address and phone numbers length is different ( 9 to 12 characters) I need to complete this difficult task (for me, obviously, ) in Excel using a combination of functions and/or multiple operations.
View 9 Replies
View Related
Jun 16, 2014
simplifying a formula which gathers data from about 50 worksheets from within the same work book.
The data to be gathered is in the same cell on each worksheet and is simply a number but i want the SUM of theses numbers carried forward to another worksheet. Each worksheet is named by date i.e. sheet 1 is named "16 June 2014" and sheet 2 is named "23 June 2014" and so on until "30 March 2015" (Each sheet represents one full week Monday - Sunday).
View 5 Replies
View Related
May 7, 2012
I am trying to develop a code which extracts the data from text files inside a folder (Folder test in my desktop) into one sheet. The Macro is in the workbook “Text Extract” which is an excel 2007 file. The data of each text should be copied to Sheet1 of this workbook one below the other. For testing purpose I have kept only one text file in the folder and was trying to copy the data from the text data extracted sheet to Cell A1 of Sheet1 of workbook “Text Extract”. The code works fine till copying the data, but shows below error in the line “Selection.Paste”:
Run time error 438: Object doesn’t support this property or method.
Below is the code:
Sub LoopThroughFiles()
Dim strFile As String
Dim strPath As String
[Code]....
View 6 Replies
View Related
May 23, 2006
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
View 3 Replies
View Related
Mar 6, 2014
So I basically have a template workbook that the code is stored in. I need it to pull an entire row if Column C in workbook "rawdata" contains specific text, in this case "PRCH - Purchase".I have never had to do anything like this before, it has always been working in the same workbook. It's failing at the 'mp = ...' line every time. There might be other errors in the code too, I just cannot resolve the first one!
[Code] .....
View 5 Replies
View Related
Nov 19, 2009
I need grouping and then summing in either msquery or back in excel after the data has been extracted from msquery.
I have external data that i have interogated in msquery, and I would like to to sum the amount column if possible.
I have two fields, first field = account number
2nd field = amount due
the first field could contain 5 sets of the same account number, but with different amounts in the Amount Due column. I would like to sum the amounts due for each of the account numbers in one column.
If i cannot do this in msquery, how do i then go about doing it back in excel. I know i can use subtotals, but then when the data updates from the external data source.
View 2 Replies
View Related
Jun 15, 2014
I am trying to create a macro to copy a set of datas in a row , paste into another workbook (specific sheet),then save it as one of the datas that was copied over.
Workbook1
Ticket#
Description
Name
12345
Test 1
David
32145
Test 2
Steve
Workbook2 - sheet (ABC)
Copy row 2 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)12345
Test 1
David
Saveas Workbook2 - ABC - "12345" - Ticket #
Copy row 3 from Workbook1, paste to row 1 in Workbook2 sheet(ABC)
And so on. I will have a set of data to trasfer to Workbook2, each row different workbook.
View 4 Replies
View Related
May 24, 2012
I have two workbooks.
Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.
I want the dropdown list to be active when i close the second workbook.
View 12 Replies
View Related
May 28, 2014
I want to load the actual data exist in the rows by selection value from the data validation list.
E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.
On the second sheet, i made a data validation list of all the person names
Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.
View 13 Replies
View Related
Jun 22, 2009
Is it possible to have a workbook (1) with a list on it and another workbook (2) which has a dropdown list on it that shows the list on the 1st workbook. But when a new entry is made on workbook (2) it is added to the list on workbook(1)
View 8 Replies
View Related
Dec 21, 2012
I have a workbook which contains a list of data on the first worksheet. I need for a macro to copy the name in each row,along with its data in that row and paste it into the corresponding locations on a template located on another worksheet. It would then save the file using the date (col. a) , then the words " Scorecard for" and then the name (col. b), then loop for each row until it reaches the end of the list.
I located this code here here.However I dont know how to modify it to fit my needs. I dont have 2 separate categories for two score cards in column b. It would all run off one scorecard and each row of data.
[Code] .....
View 1 Replies
View Related
May 11, 2009
I have a spreadsheet called contacts which is as it says, I have another sheet called project management, I want to have a drop down list which refers back to the contacts spreadsheet, it this possible as they are two different files?
View 4 Replies
View Related
Nov 23, 2007
I'd like to create a list via Data validation-list. Anyone knows how to reference a range on another sheet of the workbook?
The range is =$Q$5:$Q$13
Is it possible to reference another sheet? If yes, how is is done?
View 9 Replies
View Related
Jun 30, 2014
I have a simple macro which extracts EUR rates from a website but the number format as appears in Excel is a disaster.
I am attaching the file : Querry EUR Rates.xlsm
How to format the rates accordingly.
Also posted here: [URL] .....
View 1 Replies
View Related
Oct 8, 2013
What function should I use to find the total length of the 3 extracted names (last + first & middle title?) I need one single expression for the entire column.
A
B
C
D
E
Full Name (Last, First & Middle plusTitle - the latter may be separated by either an ! or $ symbol).
Title (extracted)
First and the middle name if the latter exists. (extracted)
Last Name (extracted)
The total length of the 3 extracted names (last + first & middle + title) e.g. 25
6
Brouillet,Amy Anne!Miss
Miss
Amy Anne
Brouillet
[Code] .......
View 6 Replies
View Related
Aug 1, 2014
If I have 2 columns (A with data to be extracted, B for output). And 11,000 rows of data. How do I take information from column A and out to column B. For example, I want to identify characters in Column A, for example I want to identify "A" "#" and "SQ" to output. For example If column A, has "A" then Invitation, If column A has "#" then Business, if column A has "SQ" then square
Column (a)
A10
extract to
Column (b)
Invitations
Column (a)
#
extract to
Column (b)
Business
Column (a)
SQ
extract to
Column (b)
Square
View 5 Replies
View Related
Jul 28, 2014
I have three individual lists and I am using a formula like this for each of them =OFFSET(Table1,MATCH(F15,Table1,0)-1,1,COUNTIF(Table1,F15),1)
for my final cell I need to create another data validation list which is depenant on the values selected in the previous three lists.. how I would alter the formula to allow me to do that? I tried using and after the match to match all three tables but it never worked
View 12 Replies
View Related
Dec 30, 2011
I have a data validation cell that has 5 items to be selected, lets say A-E.
I would like a macro that checks and does the following:
A or B selected, hide row 25-34, rows 45- 53
C or D selected, hide row 24-44
E selected, hide rows 34-53
In each case, the macro should check and unhide all rows between 25-53 before doing the above
View 9 Replies
View Related
Jun 15, 2012
I have this piece of Code which I am trying to tweak;
Code:
If Target.Offset(0, 1) = "" Then
Target.Offset(0, 1).Activate
Add: Rspn = InputBox("Please enter the Colour Code for " & Target.Value, "Validating Data Integrity")
[Code] .......
What happens is that the user Enters a Color, say Copper, then an Input Box appears with a message:
Please enter the Color Code for Copper. What I would like to achieve is to have the word Copper displayed in Quotes. i.e. "Copper".
View 4 Replies
View Related
Apr 16, 2014
I have created timesheets for employees that work in our shop. Our company manufactures products for different industries, such as mining, wind power generation, general industrial applications, and so forth. I modified some time sheet templates I found for excel to accommodate our company's actions. Each employee has their own workbook, in which the months are separated into different worksheets. Each sheet is divided further into weeks and in each weekly section the areas of information are divided as follows:
A / B / C / D / E / F / G / H / I / J / K / L / M
Work Sector / Workshop or Fieldservice / Scope of Work / Job # / Reg or OT / Mon / Tue / Wed / Thu / Fri / Sat / Sun / Total
There are 7 workbooks (one for each employee), each with 12 sheets (one for each month). I want to create a master sheet that will pull information from everyone's timesheet if they worked on a particular job. In other words, I would like to type a job number into a cell, then have excel look through everyone's timesheets and pull over only the rows of information that contain that job number
View 12 Replies
View Related
Jun 6, 2014
I have an export spreadsheet which details information from column a to column p which i need ...(supplier code...net value..vat...comments..etc)
However there are also subheaders, sub totals titles which take up entire rows and contains data i do not need.
Is there a way of telling excel to choose certain rows (eg ...A19 to P19, A20, to P20, A45 to P45 )
depending on if the first cell of the rows i need eg Cell A19,A20, A45 etc contains a supplier code such as from the following list :
AC01NET
AD05
210
003
BL01
KF01
KF01
CA06
I would need to paste the selected rows into another sheet
View 11 Replies
View Related
Oct 16, 2006
I'm trying to automate a very laborious process where text data is taken from a series of cells in excel 97 and is turned into HTML tables - I've got it all working except for one thing, when I extract the contents of the text the paragraphs are lost and are merged into one long piece of text - obviously I need to insert <br /> characters where the paragraphs are to get this working. how I can extract text from with some kind of marker for the paragraph sections? Or is there a way to insert characters where the excel cell has a paragraph mark (ie a return key has been pressed).
View 2 Replies
View Related
May 19, 2014
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies
View Related
