Combining Text And Cell Values From Multiple Worksheets
Dec 1, 2013
I have the below macro which is failing to insert text into A1 of the Header Sheet, followed by the values in the designated cells that are from Sheet1. I would also like to have the values in cells J2 and K2 enclosed in single quotes.
Sub Header()
Worksheets("Header").Activate
ActiveSheet.Cells(1, 1).Select
ActiveCell.Value = "create or replace" & " '" & Sheet1.Range("J2").Cell.Value & "' " & " '" Sheet1.Range("K2").Cell.Value & "' "
End Sub
how do I get it into the nicely formatted version most of you are using? The Mr Excel HTML add-in?
View 2 Replies
ADVERTISEMENT
Jan 6, 2009
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
View 9 Replies
View Related
Jul 26, 2007
would anyone be able to write the code that will combine the data from all rows from all worksheets within a workbook. i've struggled with this one....
here is the deal:
all worksheets have the same columns and column headings but differnet amount of row counts. the width of the sheets is to Column "M" or "13" and there are no blank columns.
There is no need to have the columns headings repeat within the compiled worksheet.
the amount of worksheets will vary depending on when i run it so it will not be able to use specific naming conventions.
View 9 Replies
View Related
Sep 24, 2010
I have a workbook that contains 18 sheets of data. These worksheets are named according to their respective client names (AAA Shine, Fern Barn, Oracle, etc. for example). On each of these sheets, is a column (Column B), that has a salesperson's name in it (Fred, Joe, Ted, Anne... there are about 10 names total). On each sheet, there are corresponding values for the revenue from that account for each month of the year. Those values appear in Column L (Jan), M(Feb), N(Mar),... W(Dec).
What I would like to have is formula that will look at each sheet, find all the instances of Fred for January, sum them, and report them in a single cell on a different sheet I have called "Summary". The end result will be a total revenue from all customers, for each sales person, by month, in one summary sheet.
View 9 Replies
View Related
May 12, 2006
I am trying to do is have a worksheet that gets its information from other worksheets within the same workbook. Some of the worksheets that it gets its data from are web queries, and as such change on occasion (columns stay the same but rows get inserted or deleted as data on the web page that it is importing changes). Also due to this being an import I can not change directly the format that comes onto the sheets (ig split into different columns etc). Much easier described within the example as I am not sure of the words to best describe the end result I am looking for. A copy of a smaller version of what I am doing is attached
View 3 Replies
View Related
Jan 21, 2014
This is a sample of what I am trying to accomplish (file attached). I have information in one worksheet (called MasterList) and a second worksheet called (RecordList). I want to take information from MasterList and RecordList and combine them to produce a report (Results). Assume the user does not have access to MasterList or Results.
The user would enter the UID in RecordList, which then populates information from MasterList. There can be multiple entries for each UID and there is no set number of entries (could be 1, could be 500)
The user enters the UID into RecordList, which populates information from MasterList. The user then adds in the additional information into the fields.
What I want is all the information from the Master List must be reported whether anything exists in RecordList or not. If there is information in the RecordList, display it and on a seperate line for each entry. Each UID then needs to be totalled (which can be done through pivot table later).
View 1 Replies
View Related
Jul 17, 2013
I have five excel separate excel files containing values covering more than 500,000 rows each. I want to put then in a single excel workbook without tedious work of copy/paste to sheets of this workbook.
View 2 Replies
View Related
Dec 2, 2008
what i want to do is 1 have a workbook with 3 worksheets. Sheet1 contains the columns:
Reference No. Line Item No. Amount Date
1 3 500 12/01
2 4 900 01/01
Sheet2:
Reference No. Line Item No. Quantity PersonInCharge
1 3 10 Kim
5 6 60 Noel
For sheet 3, what i want to happen is that when i run the VBA macro, it searches through both worksheets (Sheet1 and Sheet2), finding rows that have matching Reference No. AND Line Item No. and combine all of the columns (and values) for that particular row into another row in Sheet 3. So for the above data set, the resulting data in Sheet 3 would be:
Reference No. Line Item No. Amount Date Quanity PersonInCharge
1 3 500 12/01 10 Kim
As this is the only entry in both sheet1 and sheet2 where there is a matching reference and line item no.
View 9 Replies
View Related
Sep 13, 2009
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are:
Position Number (Sheet 1)
Position Title (Sheets 1 and 2)
Position Requirement (Sheet 2)
Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, I’d be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I can’t figure out how to say to Excel “a plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbers”.
View 4 Replies
View Related
Apr 15, 2008
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
View 9 Replies
View Related
Jun 25, 2008
I am trying to write a Macro that would go into same specific cells in multiple worksheets and paste the data into one summary worksheet if ofcourse a criteria is met.
The multiple worksheets have the same format. The worksheets are numbered 001, 002, 003, 004, etc (increase on a daily base) ..
View 3 Replies
View Related
Apr 18, 2014
I have a table based on transport numbers who sometimes have doubles. Example:
Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1
So it works as following:
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)
View 4 Replies
View Related
Mar 2, 2012
it should compare and see if product, model for same id is the same but brand is different and the quantity of one or more of those brands=0 then the result (In column F)should combine the brands. check the attached image for more details.
View 5 Replies
View Related
Nov 26, 2008
I'm having trouble combining some values in cells. For example in cell A1 i have the value 372-25. In cell B1 i want to make A1 part of another value (it is an APN if you must know). So that cell would be 011-372-25-11. I would be adding 011 as a prefix to each cell and 11 as an ending. I'm sure there must be an easy way of doing this.
I have tried
=011-A1-11
="011-"A1"-11"
="011-A1-11"
View 4 Replies
View Related
Feb 8, 2012
I have the follwoing example. Basically, there is an "X" for each posession of Andrew (cells: B1, C2, D3, E4). What I'm trying to do, in another sheet, is centralize the below information in only one row for "Andrew", with an "X" under each of his posessions (all the 4 "X"-es are now in cells B1, C1, D1, E1).
CarPhoneHouseBoatAndrewXAndrewXAndrewXAndrewX
View 2 Replies
View Related
Jun 5, 2012
I'm working on a project for use by our sales teams to for them to easily find out which campaigns are currently being offered in their regions. The data looks like this:
Participating Regions
Campaign
IN, OH, MI, IL, NE, AZ
Campaign A
IN, OH, MI, IL, NE, AZ
Campaign B
IL, IN
Campaign C
IL, IN
Campaign D
All
Campaign E
IL, WI, MN
Campaign F
This is going to be embedded internally and needs to be as simple to use as possible. I would like for column A to be filterable by state, either with a drop down menu or a selector. My problem is that many states could be in one cell, or individually in a different cell, and the default autofilters recognize these as two different values. I would like the sales rep to be able to filter "IN" for example and get 5 results. One solution that I am aware of is for me to duplicate each campaign on a different row and list one state per row for that campaign. The issue with that is that this is going to be updated frequently and that is a tedious manual process. I am also aware that you can use a text filter for "contains" specific words, but even that is more intricate than what I am aiming for.
View 6 Replies
View Related
Dec 4, 2013
I have a data set that I'm trying to sort in order to be efficient for some of my team members.
Below is an example of the raw data and how I've currently got it sorted. I think my problem may be that I am trying to join a number with a string but I'm not positive.
Col A
123-45678-A-1
123-45678-A-10
123-45678-B-2
I need to eliminate the letter from the data, add a leading zero to all single digit numbers and sort ascendingly.
My first step is to split the data into columns using the "-" as a delimiter. I end up with 2 columns as shown below.
Col A
Col B
123-45678-
1
123-45678-
10
123-45678-
2
Next I add a leading zero to Col B. The assumption is there will never be more than 99 numbers, so I use the following code:
Columns("B:B").Select
Selection.NumberFormat = "00"
To produce:
Col A
Col B
123-45678-
01
123-45678-
10
123-45678-
02
Now all I need to do is rejoin these 2 columns before sorting. My current code is:
Sub Rejoin_Container_Number()
x = 1
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Do While Cells(x, 2).Value ""
Cells(x, 1).Value = Cells(x, 2).Value & Cells(x, 3).Value
[Code]...
As many have probably already guessed, this produces the following result:
Col A
123-45678-1
123-45678-10
123-45678-2
I'm looking for:
Col A
123-45678-01
123-45678-10
123-45678-02
**zeroes in BOLD for reference only, result does not need to be bold**
View 4 Replies
View Related
Aug 26, 2013
I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies
View Related
Jul 30, 2009
I am trying to create a formula to display a string of text that refers to multiple cells. for example, =If(A1=0,"insert text here" &B1 "insert more text here" &B2 "insert even more text here" &B3, "insert text here" &C1 "Insert text here").
It works fine for one cell value, like ="Total: " &A1
View 8 Replies
View Related
Mar 2, 2014
File I have attached or made a link for here Untitled-2.jpg
I am trying to make that amazon cell, all text to appear as one line like the second line foreign conversion fee i want it to appear like that. Rather then having to delete and modify 100's of rows like this.
View 6 Replies
View Related
Aug 20, 2009
I have about 20 choices I'd like a user to select from and have the text output to a single cell.
Just create a Data Validation List? Yeah, but that only allows you to select ONE of the items in the list -- I want to select as few as zero and as many as all 20, combining the resulting text into a single string - space or comma delimited.
My first thought was checkboxes, but all I can seem to figure out is how to get ONE of the results into the cell, meaning I'm better off with the Data Validation List because it's easier.
Especially since I need to repeat this for as many as 200 or 300 lines,
View 11 Replies
View Related
Oct 2, 2008
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
View 2 Replies
View Related
Jul 20, 2009
For making quotes at work, I'm looking to have the quotes named automatically. The format of naming our quotes is the date (in this format) then quote name: yymmddNAME (Example for today: 090720EXCEL)
I'm using the =NOW() function for the date (Cell A1), then doing a custom format to turn the format into yymmdd then in a different cell I'm putting in the "NAME" (Cell A2) and what I want to do is combine the 2 cells (Cell A3) by doing: =A1 & A2
but when i do this, the format of the date gets messed up.
View 2 Replies
View Related
Mar 10, 2014
Cell A is the first name, B is a period, C is the last name, D is an "@" and E is "yahoo.com".
I want to squeeze them all together, in order, without spaces, into cell F to make an email address.
What is the formula ?
View 5 Replies
View Related
Jun 5, 2013
Collapsing function: I want to select cells that I want merged, and have them COLLAPSE into the first cell selected calls
Combining function: OR Select the cells I want merged, and allow me to pick a destination cell for the result COMBINE
I would like to add this functionality to all my spreadsheets in the future.
Excel-Forum-Data Collapsing sample.xls
View 1 Replies
View Related
Dec 27, 2009
I am using excel 2007 and have the following information:
Cell A1: L6212- (formatted as text)
Cell B1: 05.50 (formatted as custom number 00,00)
When I merge the 2 cells using the concatenate formula I end up with
Cell C1: L6212-5.5
What I want is L6212-05.50
View 10 Replies
View Related
Jul 15, 2009
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:
A1 contains B
B1 contains 2
B2 contains "tribbles"
An imaginary function might go like this........
View 3 Replies
View Related
Jul 31, 2007
I have a workbook with about 20 different worksheets in it. Each one has a header with three text boxes in it. I would like to fill these text boxes for all of the sheets, but I would like them not to be linked together or to any one cell, so that the individual fields can be modified after they have been set.
My code is below. When I run it, I get an error - "Compile Error: Method or data member not found."
hwrev = InputBox("What revision is your hardware?", "HW Revision")
eswrev = InputBox("What revision is your embedded software?", "ESW Revision")
sheetcount = 0
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
View 3 Replies
View Related
Aug 21, 2007
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.
What VBA code would merge the information from the four worksheets to Worksheet E?
View 9 Replies
View Related
Dec 30, 2006
how to use SUMPRODUCT to sum values between 2 dates in a table in one worksheet containing 2 coulmns (dates and values) .
but .......
If I need to do that to a whole workbook of 100 worksheets
i.e ( in on step I enter 2 dates , and the result should be the sum of all the values in all the worksheets that are between those 2 dates
View 9 Replies
View Related
