Create A Macro To Transpose Data
Jan 28, 2008
I have a spreadsheet, with over 9000 line items. And to give you guys an idea, these items are products that we carry at our stores (convinience stores). Most of our items are sold to the customer by eaches or unit, but we do carry beverage items which can be sold several ways (single can, 6pk, 12pk, 24pk etc). Anyway, on the spreadsheet, these sell units(single, 6pk, 12pk etc) have their own line, per each sell unit. I want them to be all in one line. Heres what it looks like:
I want columns F, and G's values be transposed to the first line, after the RET column. Like this:
View 9 Replies
ADVERTISEMENT
Jun 7, 2014
I 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)
View 3 Replies
View Related
Feb 9, 2009
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 Related
Oct 14, 2013
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] .......
View 2 Replies
View Related
Jan 29, 2014
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]...
View 3 Replies
View Related
Nov 5, 2008
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).
View 9 Replies
View Related
Jul 15, 2014
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
View 3 Replies
View Related
Feb 18, 2014
I got to transpose the data from sheet "Data" into new sheet "Actual". Now One row has been added on the top (R1) to categorize the whole data in terms of Major and minor.
modify the macro which add one column (K) with header as "Error type" in sheet "Actual" and then display the above two parameters.
I want exact type of data in sheet "Actual" from raw data sheet "Data"
View 3 Replies
View Related
Jun 23, 2007
Need macro that will transpose data from rows to columns
TAN KOON TECK
64 JALAN SEMBILANG DUA KAW 7
OFF JALAN TELOK PULAI
**blank**
**blank**
41100
KLANG
**blank**
next entry
There are blank rows in between the address, and 1 blank row between new entry.
Furthermore, the blank rows between the address are not consistent
e.g.
AZAKI B ISHAK
PT 26 PEKAN JELAWAT
**blank**
**blank**
**blank**
16070
JELAWAT
I am wondering if there is a code that could execute a macro to transpose such data automatically instead of me transposing it one by one
View 9 Replies
View Related
Nov 27, 2008
I have spreadsheet which consists of two columns of data.
Column A contains a gerneric heading/group and Column B details some results for each heading/group; these results cover between 16 to 40 rows.
Basically, I need to transpose each heading/group's rows of results data into the same row as the heading/group row; then delete the rows where the results data was copied from; then I want to go to the next heading/group in Column A and repeat the process - transposing the results into a row.
I have written a simple macro which seems to work intermittingly - one which doesn't repeat or loop though. Sometimes I get the Runtime Error 1004 and sometimes the macro overwrites the transposed row if the results are listed over 16 rows.
My draft macro code is as follows:
Sub Transpose()
'
' Transpose Macro
' Macro recorded 27/11/2008 by Hunter
'
' Keyboard Shortcut: Ctrl+p............
View 9 Replies
View Related
May 2, 2007
I am in need of perhaps a code that will allow me to offset the data which is highlighted in yellow to something that is similar to the data which is highlighted in green. The logic behind that is i do multiple loans that begin at various months of the year and must be presented into the business plan accordingly.
In this example "spreadsheet included" i chose payment 1 as January but some loans dont begin payment for 2 or more months. Hence the expense and hence the numbers located in the "Control Box" will then be able to offset the data down the number of rows that i specify in the control box at the upper left most of the screen into the section that is highligted in green respectively.
Once the data has been shifted i then need to transpose the data to every two columns in the far most payment schedule which includes the principle "prin" and the interest "int" in the respective months broken down. I need the ability to offset this data a maximum of 11 months as 12 would push the loan into a different year.
I have 15 simultaneous loans so i would need a macro that can do this for me. I am a beginner coding but very familar in excel and a formula would not work as it would be over written each time. I am thinking of if statements but that would be a pain in the rear.
I would then need to repeat this macro for the next 14 loans but in this case i would need to do it four more times and then i would need to do the same procedure for the interest side of the loan.
View 11 Replies
View Related
Oct 5, 2011
Im trying to create a Macro which can do the following: Go to the selected cell when Macro is run, keep going down cell by cell until it finds a blank cell, select the values, copy them and paste them by transposing into the cell next to where it started from in a row, carry on doing this procedure and pasting the transposed values next to the first non blank value until it gets to the end of this column.
View 4 Replies
View Related
Jan 25, 2013
I am looking for a macro that works like the ASAP Utility (Transpose data from one column to several columns in steps). To elaborate the work done by the macro it should transpose the values in a column to the number of steps that is user defined (Using InputBox) that is if there are 103 values in the column and the user enters the number of steps as 24 then the macro should transpose the data up to 24 columns and the rest in the next row up to 24 columns and so on unless the complete data is transposed.
For more clarity refer the attached excel sheet or the "Transpose data from one column to several columns in steps" utility of ASAP Utility.
View 3 Replies
View Related
Jul 6, 2014
I need to create a macro in excel that will take data for a particular year in vertical rows and transpose it to horizontal rows.
What the data originally looks like:
380204198401014.72
380204198402016.34
380204198403015.66
380204198404013.71
380204198405013.86
380204198406013.51
[Code] ........
What I want the transposed data to look like:
StationYearJanFebMarAprMayJunJulAugSepOctNovDec
38020419844.726.345.663.713.863.5113.69 3.611.362.812.522.25
38020419853.194.211.021.292.372.416.082.500.143.196.122.04
I have never written a macro before and have tons of records (over 40,000) so using this will save me a lot of time.
View 5 Replies
View Related
Feb 3, 2014
Extract data from one sheet to another sheet then transpose automatically.
I have attached the work book of what I'm trying to accomplish.
View 14 Replies
View Related
May 4, 2012
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.
View 9 Replies
View Related
May 22, 2012
How to create a macro to transpose rainfall data (in a column) into rows.
I have over 7200 years of data so using paste special will be very slow.
I have attached a sample of the spreadsheet below.
YearMonthRainfallYEARJANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC
11105.5841126.1212131.72131412.0541514.15451646.6716179.75571824.4838193.
25291105.931011131.1781111231.943122161.0321322109.9142324.751152416.501162512.
164172613.222182787.3719285.5202931.944212104.019222111.1642321221.049243117.
[Code] ..........
View 6 Replies
View Related
Oct 14, 2008
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 Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Jul 8, 2014
I'm creating a macro that will involve some form of if-else/case-switch, as well as a loop (probably), but I'm not too sure how to go about it.
Basically there are records in columns. Lets say Column A has numbers in it, for example
Cell A1 has "Three"
A2 has "Five"
A3 has "Two" all without quotes
I basically want a macro that will loop through the whole column, so if A1 contains "Three" (not case sensitive), then set B1 to "3" without quotes. If A2 contains "Two", then the macro will set B2 to "2" without quotes.
Of course this is sample data but you get the jist. It needs to loop through one column, so as to fill in the other column. You can assume that column A will always be filled, so the macro is being created to automatically fill in column B based on what is in column A.
View 1 Replies
View Related
Sep 20, 2013
I have 2 sheets in one excel file.
I need a macro to check how many rows have value in sheet b and then insert rows accordingly in sheet A between cell B11:B100.
For E.g. if there is value from cell A1 to A150. than I want the macro to add 50 more rows in Sheet A between Cell B11 & B100 and also copy the formula that is there in cell N11 to Z100.
Data in cell A1 to A150 can be between 0 to 999
View 2 Replies
View Related
Jun 12, 2009
I'm looking to create a new file from data in my table. I don't want to even imagine having to do this manually again...I'm optimistic there is a solution. All the data needed to create the file is in the table, but i need it stacked and organized in a weird way. It's almost to hard to explain...so I color coded an attachement that basically says it all. It's pretty much the same thing repeated over and over except the last 2 lines. It's just a really messed up organization. In the real version I need the new file in a new workbook. I'm extremely grateful to anyone who can automate this thing
View 2 Replies
View Related
Jun 5, 2009
I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
View 9 Replies
View Related
Aug 26, 2009
I’m trying to do is create two tables from data evenly spaced throughout columns A to I. To make the screenshot easier to view I moved the second table under the first. The data is arranged so that each “Subject” has 42 columns and is arranged by “Type” and then by “Subject.”
The tricky part is that the number of subjects will vary so everything has to be done in some sort of loop. From the little I know about VBA I was able to record and edit a macro to add spaces every six rows and average the “MaxResponse.” (that’s what stage the screenshot was taken at) To make the table I think I need to write some sort of Dim statement but that is way beyond my comprehension.
Here is a link to a photo of the table http://yfrog.com/77excelshot3j
Excel 2003
VBA 6.5
Win XP Pro
View 9 Replies
View Related
Jul 6, 2014
I'm trying to compile a spread sheet that will pull the play by play logs from the NHL website.
The logs are listed in order.
Game 1 is [URL]... Game 2 is [URL]... etc etc
I am trying to put them all on one spreadsheet. (or to do it in batches of 100, or 50 or whatever the size of the file allows)
View 1 Replies
View Related
Feb 4, 2012
I need a Macro to create a unique, rolling data list. On Sheet 1, a report is pasted(from outside source) in cell A3(Rows 1 & 2 are headers) on a weekly basis. It is always the same amount of columns wide(138). The number of rows will always vary but will never be more than 500.
Here’s what I need for Sheet 2:
-Column A should be a copy from Column A on Sheet 1 (data is ITEMS)
-Column B should be a copy from Column BH on Sheet 1 (data is QUANTITY of items)
-Column C should be a copy from Column BI on Sheet 1 (data is PRICE of items)
Here’s the challenge (when the new report is pasted on Sheet 1):
-Sheet 1. Find new ITEMS not on Sheet 2 and paste them at the end of the report on Sheet 2 along with their QUANTITY and PRICE
-Sheet 1. Find any ITEMS already on Sheet 2, then update new QUANTITY and PRICE from Sheet 1 onto Sheet 2
-Sheet 2. Leave ITEMS alone that are now missing from the new report on Sheet 1
View 7 Replies
View Related
Oct 24, 2012
Currently I have been tasked with trying to develop a link between an excel spreadsheet and outlook. What they want is for a button to pickup new entries into the sheet and then create outlook events based on several criteria. The first been that is put on several shared calendars, second that the category of the event is call "BID" and gold in color. The last part is where specific information from the spreadsheet fits into the event areas (ie. subject, location, start time, body). I did find code that is a possible solution but manipulating it to what i need. Here is the code.
Code:
Option Explicit
Sub AddToOutlook()
Dim OL As Outlook.Application
[Code]....
As you can see for the most part I have been able to fill in most of the program with what i needed. I have column B on the sheet that has r's on it. If there is an r in that column then the macro creates information from that row, else it skips to the next one. so i need a loop. Also like stated above, how do you make it an event and not a meeting, how do you set the category and lastly for the boy how do i set it so it copies the entire row (column A, Column C to Column L)?
View 1 Replies
View Related
Jul 31, 2013
I have a worksheet with the following columns:
Brand
UserName
FirstName
LastName
Email
[Code]....
This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.
E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed
View 3 Replies
View Related
Dec 21, 2006
I need a macro… when I run the macro.. I want to create work sheet based in the shift time and copy entire row related to that shift time of all the cell to that particular sheet… there may be multiple rows related to same shift time but remaining data may be different..
View 9 Replies
View Related
May 15, 2014
create macro in order to protect Data Validation rules.
e.g. If user copy and paste cells from others source which is not in the same validation criteria or not contain any validation rules, the existing validation will gone.
so, is there any macro which will be able to automatically run to prevent the data validation?
View 7 Replies
View Related