Please refer to attached file.
I have employee Clock IN - Clock Out as shown in Column A thru C. An employee can have more then 1 Clock In Clock Out as shown for Employee Name Karmen and Haley.
As shown, each employee is separated by "----------------------" and it ends with "REPORT END"
I would like a VB Code to transpose the data as shown in Column G thru H ...
I have 2 columns on sheet 1 as below. I need a code to put all the data in column B vertically on sheet 2 as the result shows. Please note all cells data will be off various lengths all seperated by a comma.
Sheet1 AB2BK
1003 CV1173, CV3133BK1004 CV1010, CV1010A, CV13514BK1005 CV1012, CV1257, CV17995BK1006 CV1836, CV506
Result after code has run.
Sheet2 AB1
BK1003CV11732BK1003CV3133BK1004CV10104BK1004CV1010A5BK1004CV13516
BK1005CV10127BK1005CV12578BK1005CV17999BK1006CV183610BK1006CV506
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedI'm trying to transpose 5 cells vertically into 5 cells horizontally, then do the next 5 verticals under the 5 horizontals. See attached spreadsheet for Column A (original data) and horizontal data (result). VBA code would be most useful, and original data could potentially be 600 cells long.
View 5 Replies View RelatedBelow, I interpret "vArray" as a vertical array, but the output is horizontal when I run a VBA procedure with this line
Sheet5.Cells(1,1).Resize(1, 100).Value = vArray ( The horizontal values copied are correct! )
If I change this "Resize(1, 100)" to "Resize(100,1)" the output is now vertical but the cell values copied are all exactly the same to vArray(1)
How can this line to be modified to be horizontal and its values the right ones?
I have a long header I am pasting to a column "A" on a new Sheet. When the header pastes it is repeating itself but with long blank spaces in between each repeat. The first time goes from A1 to A152, which is all I want. But it shows up again starting at A180225, and again at A212993, and again and again. I only want a single instance of the header in Column A. Here is the macro I am using:
Sub Sort_Cells()
Rows("1:1").Select
Selection.Copy
Sheets.Add after:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=True
Selection.Columns.AutoFit
Range("B1").Select
End Sub
I think I have a very straight forward problem, I'm copying about 400 values from one workbook to another (from vertical range to horizontal range) and I currently have about 400 lines of code in order to do this. Below I've pasted the code I'm using now but the macro takes an estimated 30 seconds to run. I figure if I can reduce the number of lines the macro will run a lot faster
RowCount = openWb.Sheets("Library Raw Shear Rates").Range("A3").CurrentRegion.Rows.Count
With openWb.Sheets("Library Raw Shear Rates").Range("A3")
[Code]......
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
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.
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
I have data that gets dumped from a program into a nasty horizontal format that I need to get transposed into a verticle format. As it stands now, the info is reported with hourly data spread accross rows. I need the hourly data in one column. See attached sheet for an example of what I need done with the data. I'm looking for a macro to take my "original" sheet and create my "new" sheet. Note: a macro that can do this would save me days of time.
View 4 Replies View RelatedI need a macro to manipulate some data. The attached example contains more details.
View 2 Replies View RelatedI would like to transpose the data below Columns( A & B).GroupPersonAA1AA2AA3AA4BB1BB2BB3CC1CC2
To this format on Sheet2 Starting in A1.
ABCA1B1C1A2B2C2A3B3A4
I need the following data transposed to the next sheet in this formatClaims
Assessor:
Claim ID:
Quality Checker:
Q/Assessment Date:
Correct Member
Correct Payee
Correct re-imbursement address
Correct amount & currency
[Code]...
where score denoted the marks achieved. with the code for the same.
Claims Assessor:
abc
Quality Checker:
xyz
Claim ID:
165663
Q/Assessment Date:
21-06-2012
[Code]....
I have a set range of 21 rows, and the data in column A.
how can i tell the vba to look at cell A1:A21, copy the data, and past special transpose it onto Sheet2 starting with A1:U1
then for cells A22:A42, copy and paste special transpose onto Sheet2 A2:U2 and follow this procesure down the length of the entire sheet.
I want to transpose my data. Please see excel file. The original data is of 5 persons (in column) and working hours are recorded over the 35 days (day 1 to day 35 in rows). There are 35 entries for each persons.
I want to do a longitudinal analysis. So I want to transpose data in such a way that each person is repeated 35 time one column, with day in next column (1-35), and working hours are give in front;
For detail see attached excel sheet.
Original table
ID
D1
D2
D3
D4
D5
D6
D7
[Code] ...........
Want to make below table
ID
Day
Hour
1
1
2
[Code] .......
Have been stuck on an interesting issue I currently have data as set out in the table below.
name
ID
course 1
course 2
course 3
course 4
[Code]....
I need the course list to be restructured such as this below. I have tried using a vlookup with extra bits and a pivot table but cannot achieve what i am after, was thinking maybe a macro that could transpose each row as it drops down but not sure where to begin on that one.
steve
course 1
100
steve
course 2
94
[Code]...
The following data is in column A:
Adam
Mobile 19171234854
Work 19171234854
Adele
Mobile 9171234854
Home 2121234567
Adrian Simpson
Mobile 19171234854
I would like to move the data to rows with the appropriate headers:
NameMobileWorkHome
Adam1917123485419171234854
Adele9171234854 2121234567
Adrian Simpson19171234854
As you can see, some of the phone numbers start with 1 and some don't. The listings may or may not include mobile, work, and home phones.
When I download pricing from SAP, the output vertically displays material, scale quantity, price (three columns of data). There could be up to nine price breaks per item and there could be as few a one. How could I transpose the data to have only one item per row followed by the scale quantities and prices in the proceeding 18 columns. Keep in mind the the quantities very greatly between items. A value of zero should be displayed when the full 9 quantity and price breaks are not used.
I have excel file that have the following columns: Sample ID, Analyte Name, Concentration, RSD. These columns are filled down.
I would like the analyte names should be the column headings. Then the rows with the sample ID, Concentration, and RSD - these rows are filled in with elemental concentrations, and rsds associated with those concentrations.
It is not quite a simple transpose, the rows should start over every time the sample name changes. I will be very grateful for any help with this and would gladly send a file to anyone who would help me. Sometimes the number of analytes I sample changes, so if anyone has any ideas on how to write a macro that can do this by recognizing when the analyte name repeats itself (to know when to start a new set of rows).
I am trying to work with a table that came from a web page that looks like this. ( It has about 200 records.)
Record 1
Name
Address
City, State Zip
Record 2
Name
Address
City State Zip
.
.
.
I want the resulting file to look like this
Record1, Name, Address, City State Zip
Record 2, Name, Address, City State Zip
I can use the Copy / Paste/transpose, but it would look like this. Record 1, Name Address, City State Zip, Record 2 Name Address. I could transpose one record at a time, but that would take a long time.