I have huge data
I Want data to be transpose in a below format:-
Emp Code Emp Name Intime Outtime Total worked minutes Extra worked minutes Attendance Shift
And also add the row as per the number of days worked.
VBA code to transpose large number of data.
Transaction #Transaction DescriptionAccountDate (XX/XX/XX)DesciptionABCDEFG10/24/2015Cash Held(3404.00)3404.00 0.00 0.00 50.00 0.00 (50.00)10/25/20152Cash not Held0.00 (9707.00)9707.00 0.00 (60.00)60.00 0.00 10/26/2015Cash Held0.00 (7402.00)0.00 7402.00 0.00 0.00 0.00 10/27/20154Transfer to Bank 0.04 0.00 (50.04)0.00 50.00 0.00 0.00 10/28/20155Transfer of Cash0.00 4208.00 0.00 (4208.00)0.00 0.00 0.00
I am trying to make a macro that will turn transpose the above data into the below data.
DateDescriptionAccountAmount10/24/2015Cash HeldA-340410/24/2015Cash HeldB340410/24/2015Cash HeldE5010/24/2015Cash HeldG-5010/25/2015#2 Cash not HeldB-970710/25/2015#2 Cash not HeldC970710/25/2015#2 Cash not HeldE-6010/25/2015#2 Cash not HeldF6010/26/2015Cash HeldE-740210/26/2015Cash HeldG7402
Basically each non-zero number in the accounts (A through G) need to have their own row with the account name from their appropriate column and the date and description in their appropriate row. So as you can see each account A,B,E,and G get the amount -3404, 3404, 50, and -50 in their own row and get the appropriate descipion of 'Cash Held' and Date '10/24/2015'. Then the macro would move onto the next row and do the same thing.
Also if there is a way to tack in front of the description the transaction # (if there is one) ie. #2 Cash not Held. I really don't even know where to start.
I have a sheet with products down the side, and dates across the top. Numbers will be entered at an intersection between dates and products to represent quantity. Whenever there is a non-blank value for the quantity, I need to copy the products, dates, and quantity into a traditional database format. I have attached the original data format I get from suppliers on when they will deliver, and then show a separate tab for "Desired Result"
View 2 Replies View RelatedI have a problem and I can't figure out how to do this, I have tried using both macros and functions (INDEX for example). The problem is as follows, I have a dataset of 27 worksheets, each worksheet has between 30k and 60k of rows and 25 columns. They are set up as follows:
------------------1990 1991 1992 etc.
Firm_A-Variable_1
Firm_A-Variable_2
|
|
|
Firm_B-Variable_1
Firm_B-Variable_2
|
|
|
etc.
Now what I am looking for is an easy way to transpose the data, I would like to have it looked as follows:
-------------Variable_1 Variable_2 Variable_3
Firm_A 1990
Firm_A 1991
Firm_A 1992
|
|
|- ---- 2012
Firm_B 1990
Firm_B 1991
Firm_B 1992
|
|
| ------2012
etc.
It is basically impossible to do this by hand, each of the 27 worksheet has between the 3000 and 6000 firms and each firm has 57 variables (these are identical for all firms). Also the the firm names and the variable names are in the same column, these should be seperated as well (they are connected with a hyphen).
I have a column (A) where there's a date in each cell. Currently it is formatted as category "text". All entries where initially entered as 'yymmdd', but using category "number". Beacause of that all zeros in the year disappeared, so now they are shown as "ymmdd".
Can someone show me/tell me how to convert all these dates from category "text", to category "date", using format "yymmdd"? I have tried again and again and again, but can't make it work. I have attached a sample of the sheet.
I have a column of dates formatted as:
20130201
The cell format in the column is General.
I need to change it so that the format looks like this:
02/01/2013
Is there a quick/easy way to do this in Excel 2010?
At Line 40, the program stops running. It asks for an object, but Im not sure why.
Also, I realized that there is an issue with sub twodeux copying the range selection from sheet1 of oldbook to the newly created sheet1(transfer template) of newbook.
How can I cause the form to hold that selection of values until its ready to put them into the newbook? ....
I need a list with a row of column headers (optional but recommended) and then rows of data. An example of what I'm working with is as follows:
ALAMEDA FAMILY SERVICES
ALAMEDA FAMILY SERVICES
2325 CLEMENT AVENUE
ALAMEDA, CA 94501
ALLIED RE-ENTRY PROGRAM
ALLIED FELLOWSHIP SERVICE
1524 29TH AVENUE
OAKLAND, CA 9460L
ALLIED FELLOWSHIP SERVICE
ALLIED FELLOWSHIP SERVICE
1851 10TH STREET
OAKLAND, CA 94606...........
I have data in F6:AG6 raw filled with numbers as well as dates, i am looking for formula which can calculate a larger value of number format (excluding date format).
View 1 Replies View RelatedDoneyOte on this but i have added to the range and im now getting an error that i can't seem to fix
View 12 Replies View RelatedIm importing figures into column G of my worksheet, and I need a code so it automatically adds a "0" to the start of each row in column G plus format it.
For example, im pasting in 970702090341 but its showing as 9.70702E+11.
Once I format the cell to 'number' and 0 decimal places it looks fine, but want to save the hassle of doing it each time. Plus I need a zero at the start.
Is there a code possible for this?
I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
I have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
I have a column with dates and times, and an associated column with data for all the dates and times. I want to add every hour of every day to the date/time column, but want the data associated with the times to stay in the row next to the correct time. How would I do this? In the attached file, i want to make coulmns A and B look like Coulnds E and F. I have hundreds (if not thousands) of rows, so i need a way to do this easily. Also, the times are not all evenly spaced or exactly on the hour. I dont need the times to be exactly on the hour or spaced out evenly, but i would like to have at least one time from every hour.
DBdate time alter.xlsx
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
I have a one column spreadsheet. The column contains this data:
1 Name
2 Address
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
12 Address
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
Is it possible to associate a range of cells containing different information based on like values in other cells?
Example:
How it currently appears in spreadsheet
Name Acct#
John Q. Public 12345
John Q. Public 23456
John Q. Public 34567
John Q. Public 45678
Jane Example 11111
Jane Example 22222
Wanted Result
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
John Q. Public 12345 23456 34567 45678
Jane Example 11111 22222
Jane Example 11111 22222
I know how to remove the duplicates afterwords to get one unique record.
I've got a 80 Mb CSV file and would like to open and work with it. Too many lines (90000 or so).
Is there a way to split ( ) this file so I can open two files instead?
in transposing all data, I have data in the format below:
Material ID | Attribute Name | Attribute Value |
MaterialNo.123 | Color | Red |
MaterialNo.123 | Color | Cherry Red |
MaterialNo.123 | Color | Sunset Red |
I want to transpose it to show:
Color Color Color
MaterialNo.123 | Red | Cherry Red | Sunset Red |
I need to transpose column data (Sheet called "Recpt") into rows (sheet called "Formula")
Please refer to attached excel file,sheet "Formula". I have manually entered formula for 12/1/2013. Need to add formula for the rest of the sheet. Since the data is on every 4th column, I am sure it is feasible to copy the formula by adding 4th columns.
AUTOMATE TRANSPOSE 2-13-14.xlsx In the attached file, I am looking to automate the transposing of the date and numbers under each bold number. Data is truck # in bold, the engine oil change date and mileage below. I copied the data from a pivot and need the date and mileage in columns, date on top with mileage below. I can do it with paste special one truck at a time, the big chunk of data is about 2000 rows deep and was hoping the transpose paste special could be automated, I've made a few attempts on how to do it but can't get it.
View 6 Replies View RelatedI am working on a spreadsheet that has (7) sheets or (7) tabs. The First tab is the "Summary Tab" and the 3rd tab is the "Plate" tab. I am working in a range of cells that go up and down on the "Summary Tab" and I need to fill them with the values from the "Plate Tab" that goes side to side or across. I can enter into the top cell on the "Summary Tab": =Plate!B47 and I will get the value that I need. However, I want to copy the formula or fill down and have it pull the values from the cells on the "Plate Tab" Horizontally like this (as I fill down) =Plate!_$47 Where the "_" changes to "C", "D", "E", etc.... but it stays on row 47!!!! How can I do this????
View 5 Replies View RelatedI have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.
There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.
I'm working on this project that I inherited from another colleague and am at a sticking point. The workbook is used to determine which employees are working on which projects what pct of the time. The workbook was set up using a start and end date (columns e and f) for the projects instead of a column with the month and the correct percentage. I've set up monthly columns, which are now in columns K through AF. I believe that I need to transpose those columns into a row so that I can set up a pivot table.
View 5 Replies View RelatedI need macro to transpose the raw data I have extracted from a Biometrics file into the format I need to compute for work hours.
Sheet 1(raw data)
Sheet 2 ( format i want the data to look like)
I've got a workbook called MyBook1 which has 12 groups of 5 columns representing each calendar month. I am looking to create a macro (one for each month) which will be executed from another workbook. This macro will pull a column of data from a workbook called Data1 and paste into a row in the MyBook1 workbook.
Using the macro recorder, I've got the following code that works great but was trying to shorten the code and get it to work from another workbook but haven't been successful. I believe all that needs to be done is create a variable for the source/destination workbooks then set the selections and do the copy/paste but I'm having a hard time finding example macros which I can learn from.
Working code below for January, for Febuary the Data1 range is D2:D6, and the destination is 5 columns over making it M3. Then just follow the code below as a template.
[Code].....
I have a performance extract from a system which has performance for multiple accounts but the data is off different lengths and shown vertically. I wish to move the data onto a new sheet but rotated(transposed) horizontally
Attached is a snapshot of the report that is usually about 20,000 lines. See input and output tabs.
Is there any way that a macro could be created to do this.
Performance.xlsx