I have an excel file that contains 93 tabs, all with the
same type of information, and I need to take the information from each
tab and combine it so that it has all the information from all 93 tabs
in one.....without having to copy and paste each tab,
I need to combine data from multiple tabs into one tab. I can have up to 5 tabs with data that starts in column B. The number of rows will be different each time.
View 1 Replies View RelatedI have a sheet with over 1000 rows showing me every employee's benefit choices. It has multiple rows for each employee - one that indicates their health option, one that indicates their dental option and so on.
I'd like to combine them so I have one row that has columns for health, dental, etc...and then their options would be listed all in the same row. I've attached the original with the solution I'd like pasted below. I'm sure this isn't difficult but I'm having trouble figuring it out.
Good Afternoon. I am attempting to create a pivot table using 4 columns from 5 different tabs. I have created pivot tables before using one tab and they are pretty easy. However, I have been working on this for over an hour and I am getting nowhere. Can someone tell me how to put all five tabs into one pivot table? The columns are all in the same order on each tab. They are as follows: System, Management Area, Schedule Date, WO Type. I want the pivot table to have System and Management area on the left side, date on the top row and counting the number of WO Types.
View 6 Replies View RelatedI have a workbook with two different tabs. On one tab, I have a spreadsheet that lists business names on first column, address # on second column, address street on third column.
On the second spreadsheet, I have address # on first column, address street on second column.
What I want to do is create a column before the first on the second spreadsheet, titled business names, and then have Excel find and match the address # and address names that are the same on the first and second workbook, and for those that are common, insert the business name into the new column on the second spreadsheet.
EX:
Spreadsheet 1 -
Joe's Pizza | 67 | Smith Street
I want it to find 67 Smith Street, and insert Joe's Pizza in second spreadsheet.
I need help with a formula so that I can create a single chart or (more likely) multiple charts for each of the choices in column C.
Attached is a sample of my spreadsheet.
I want to be able to break out each of the "Br#"(A) associated with the "BUM"(C) and graph each of the choices in column D (Run Time).
I am sure I can create the chart I need once I get the formula created to pull the data I am trying to combine. I hope that I have explained this well enough.
I have data in excel that has some information the same with a unique field. See below:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
[code]...
I want it to consolidate all of the like information but add on the codes in separate columns like this:
Last Name
First Name
ID
Date
Address
Apt #
Acct #
Code 1
Code 2
Code 3
Code 4
[code]...
Also, sometimes the same code is used multiple times but i want any duplicate codes to show as separate codes.
I have a worksheet that contains 26 tabs all of which have the same format but contain different data based on that pay period. i would like to create a summary tab which will allow me to enter the pay period at the top (1,2,3 ect) and have excel reference that tabs information into the summary. Is this possible?
View 6 Replies View RelatedI am looking for a macro to rename tabs based on information in A1 on each tab. The thing that is causing me issues is the cell A1 has a formula in it and it changes on a daily basis with days of the week but the tab only updates when i double click A1 and press enter.
I need it to update automatically.
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.
Is it possible to make multple worksheets from a selection of multiple cells?
This would mean a selection of 10 cells would generate 10 sheets titled with the cell conent.
Is it possible to combine the following three formulas into one formula?
=IF(AND(H5>59,(I5<5),(J5<15)),("Close Out"),(""))
=IF(AND(H5>59,(I5<15),(J5<5)),("Close Out"),(""))
=IF(AND(H5>55,(I5<10),(J5<3)),("Close Out"),(""))
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
I have a list of data elements listed row by row with headers
last 4 of SSNFull Nameschool_codeterm_beginterm_endSchool Rep NameEmail
1111 Jim smith 00104600082012 082016 school reptestemail@hotmail.com
2222 Jane Doe 00104600082012 082016 school reptestemail@hotmail.com
3333 justin justin00104600082012 082014 school reptestemail@hotmail.com
4444 Joe Joe 00104600082012 082016 school reptestemail@hotmail.com
5555 Jake Jake 00104600082012 082016 school reptestemail@hotmail.com
What I need to do is to get all 5 rows into one row and duplicate the headers so row 1 has all fire rows of information one column after the next comlumn. If I can't duplicate the headers easily and can just aggregate the data into one row in cell by cell that fine.
Sample document attached for review
I need to combine multiple sheets in to one sheet. Each sheet has a column with unique identifiers but other columns have different data. The model is attached.
View 5 Replies View RelatedI have a userform that has the same type information on 3 different entities (name, address, phone, etc). The same research is required on each one i.e. you need to look up the zip code for each). Therefore I have a bank of buttons on each page of the multipage form. Each button does the same thing depending on which page you are on. The following is my code to copy some information and open another application depending on which page you are on and which type entity.
My question is how can I combine these into one so my module is not 3' long.
I was wondering if you could help me combine a load of Macros (Theirs about 20, but I've only put two here)The code I have is this...
View 10 Replies View RelatedA member of my team has created numerous templates in Excel 2007 for various customers. Each template has some columns that are identical for each (Cust Name, for example), and some columns are unique to each customer. I want to combine all of these individual templates into one Excel workbook and be able to click (select from a dropdown list) the customer name, and have only those columns that pertain to that customer appear. Select another customer name, different columns appear, and so on. Is this possible to do in Excel 2007? [This is my first post, so please forgive any offenses if my question is too long.]
View 9 Replies View RelatedI have multiple WB's all with the same format in a single folder. I need a button to copy all the text from each WB into a single Master WB that has the same format. There are 3 sheets in each client WB corresponding to three sheets in the Master WB. Each client WB has a number of rows (or none) on each sheet and when they are copied to the Master they need to paste consecutively and into the corresponding Master sheet.
Something like this:
Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.
I hope thats clear enough. The Client WB's are all named "stats [name].xls" with 1 hidden sheet (to populate lists) and 3 sheets named "POC", "ISS" and "ECS" repectively. The Master WB is named Stats.xls with the same sheet names as the Client WB's.
The following code was written for me by a helpful member of this forum but it only copies the first sheet of each Client WB. When i tried to duplicate and modify it to copy the second and third sheets I could not get it to copy from the second/third sheets and it meant 3 buttons/3 steps/3 times the confusion.
Sub Report()
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If
If there is anyone who could help me with this I would very much appreciate it. I am only a basic user of Excel and VBA is still new to me. Adding modules and understanding basic commands is as much as I know at the moment.
Not really sure what I want to accomplish is called but here's a description:
I've got a file with 2 columns
Column A Column B
C53445002558-001--104
C53445540.00
C53455002558-003--105
C53455540.00
C53465540.00
C53475002558-003--105
C53475760.00
C53485002552-002
C53485002558-003--105
C53487294.00
C53495002558-003--105
C53495540.00
What I want is the following:
C53445002558-001--1045540.00 ....
I have two spreadsheets of 27,000+ rows each.
Each one consists of user data for each time they logged onto our systems. The problem is that there is one row for each login. There are anywhere from 1 to 20 (or so) rows per user. I need to turn that into a spreadsheet with one row for each user.
In theory, the problem shouldn't be that difficult. Each user has a unique user ID, and the only math that's required in the merge is a simple adding of numbers from three columns to give a single total for each user.
So far, the only workaround I've been able to come up with is using the subtotal function based on the unique user ID which will at least isolate each user and total up their usage statistics. But it still leaves me with the problem of deleting the now uneccessary extra rows by hand, and then pastin the data back into a new sheet. Using vlookup, I think.
I have a HUGE spreadsheet (1,000,000 rows) that contains a series of reports. The first row of the report contains all of the report identification numbers. The next 20-50 rows contains the report details, all in column A. Then the report ends with a cell in column A containing "[report_end]" and then on the next row the next report begins.
I would like to compress the report details all into a single cell in column A so that there is only one row for each report, sort of like this:
AA | BB | CC | DD
xyz
123
[end_report]
AA | BB | CC | DD
xyz
123
[end_report]
becomes:
AA xyz 123 [end_report] | BB | CC | DD
AA xyz 123 [end_report] | BB | CC | DD
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.
I have multiple sheets within a workbook, where the sheetnames will always be changing.
Inside of these worksheets there is data that will be different, the starting cell of the range is allways the same and the number of columns is constant. The number of rows changes. I need a code that will go to each worksheet, define and copy the range on that sheet and paste it onto a summary sheet, in order.
I would like to collect all the data and put it onto one sheet.
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.
I'm trying to figure out whether or not something is possible in power pivot. I'd like to load two tables into powerpivot and then set up formulas that create calculated values across the two tables.
Here's a simplified description of what the tables look like. [URL] .........
The pivot table should join the two tables on Month and Category and allow me to create calculated values across the cost category, like this. Note it's filtered to a single cost category right now: [URL] .......
In this view it's filtered to all cost categories but i'd be looking to still maintains the right count of members (i.e. it doesn't inflate the numbers from duplicate records as you might get if you were to join the two tables in a sql database): [URL] ....
The actual tables I would use are quite a bit larger (too large for excel without pp), and have quite a few more of each type of category.
Would something like this be possible to do in powerpivot without having to do some data prep work in another app to join the two tables together?
I have a time tracker that provides hours that employees clock in and out. It provides me with an excel document with all the employees time every two weeks as needed, however it does not total the hours from all previous spread sheets so that I can have a YTD (year to date) total of the employees hours worked and I need to be able to have this. Currently I am going through each and every spreadsheet and totalling them up. I have over 40 employees and this has become very time consuming to say the least.
View 1 Replies View RelatedI am trying to create a macro to gather a data range from multiple files placed a folder and combine them into a single worksheet which can be easily totaled. I've used some similar code I did for another project to gather the data. It starts by listing the file name and then the data set (About 40 cells) below the file title. However, the data sets from each spreadsheet are filling themselves into a single column one after the other, whereas I would like to have them populate one worksheet's data in each column.
[Code]......
SummaryWorkbook.xls
[URL]...I was trying to use this thread's solution macro on my sheet that is only consolidating columns A, B, and C with a couple thousand lines and I've had it running for over an hour now. There hasn't been any errors but is there any particular reason that might be causing it to take longer than it should aside from having to go through a few thousand rows?
Basically, I only want 1 Version of data in Column B and any other rows that has a match in column B, i want to consolidate unique data from Columns A and C.
A B C
AB 123 AAC
BA 131 AAC
AB 222 CAA
Res:
A B C
AB 123, 222 AAC, CAA
BA 131 AAC
This isn't absolutely necessary, but being able to do this easily would cut down on a lot of tedious jobs for me. Let's say I have a set of data like this (I tried to space it out as best I could to give the idea):
View 3 Replies View Related