Formula For Transposing Rows Vs Columns Dynamically?
May 10, 2012
I have an Excel Sheet1
The sheet has horizontal info a list of "headers" going horizontally across row 2. So row 2 may have A2= Date, B2=Name, C3=Style, D3= ........
The header rows will vary in some cases. I want to use a formula to copy and transpose the information to Sheet2. I would like the list of headers to go vertical instead of horizontal. So A1=Date, A2-Name, A3=Style, A4= ..... all the way down.
I know about Copy -> Special -> transpose but I need something that is dynamic and simple. I know I can use the indirect function and do something like this =INDIRECT( "Sheet1!" & ADDRESS(1,2) ) . However I was trying to figure out two things. How best can I make the columns dynamic and increment up especially if I am not starting at A1? Is there a better formula to use that is not volatile like INDIRECT?
I ve got a similar issue with converting the rows to columns... At the file I attached below, I need to convert to columns some sort of data which is organized as rows. They are all set up as 12 cells in a row and I need to convert them to a single column which keeps the time references of the data. I mean, both of the data and their years will be needed for my aims, but it is not important to keep the years columns with dataset, as long as rows are literally converted to columns under their order.
I actually found a vba macro on the net but it doesn't useful at all when it comes to keeping all data in their original order. It has some specific design to solve the issue without the aim of keeping the data in its original order I guess. Is there any possibility to modify its code or any other way to do this?
Code:
Sub Data_to_Column() Dim rData As Range Dim r As Range, c As Range Dim rStart As Range Dim counter As Integer
I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.
The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.
I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.
I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...
I have a spreadsheet that calculates data in multiple columns (A:AN) and each column has 27 rows of data called "Sheet 1". As it stands, the spreadsheet goes out very far. On "Sheet 2" I would like for all of the data in "Sheet 1", columns A:AN to be "transposed" to "Sheet 2" in column A only. So, column A on "Sheet 2" would have the data from "Sheet 1" A1:A27, B1:B27, C1:C27, so on and so forth. I would like for the formatting to maintain the same order as well.
An example would be:
Sheet 1 Column A B Row 1 Green Red 2 Blue Yellow 3 Black Cyan 4 Pink Magenta
I am trying to reformat the attached doc into one single column, It's a race listing of overseas marathons and I need it to look like this
Event Name Event Date City Country Entry fee Website
in date order, one event after the other in a single column so I can flow it into a word doc ready to be designed. I need to apply in Excel (I am using 2007) to get this doc into the required format?!!
We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)
Right now here's how the data is received:
a a a b b c c c c d d d e e e e
Using two vba scripts, we first separate the data with row spaces between the unique data as follows:
a a a
b b
c c c c
Then with another script, we transpose the data as follows:
a a a b b c c c c d d d e e e e
When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...
a a a b b c c c c d d d e e e e e
where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.
The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.
Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?
My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.
I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)
The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.
One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.
We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.
For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.
One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.
I am trying to set up a new assurance work book and worksheet (worksheet2 lets say) which will dynamically link to another workbook and worksheet (worksheet 1) owned by another part of my business. The purpose using the data set in worksheet 1 is that this is the source data and is the most accurate for the project information. So I am looking up columns A, B and C in that workbook in my new worksheet2 using ='[Spreadheet 1.xlsx]Sheet1'!$A$1:$A$1174 the same for column B and C, with the aim that any new data entries within columns A, B and C will be populated in worksheet2.
In columns D to Z of worksheet 2 (my worksheet) I am applying some assurance metrics to the project information that is specific each row of column A, B and C which are dynamically linked to woorksheet1. My problem and it is completely eluding me is this....
The owners of worksheet1 regularly sort the data into chronological order based on Column A. However the data is not normally presented in this way i.e. all new entries regardless of date are added to the list at the bottom of worksheet1. The problem I have is, is keeping my row data in Columns D-Z linked to the row data in columns A-C of worksheet2 no matter what kind of sorting occurs to Columns A-C in worksheet1.
The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.
The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)
Is there a way to do this?
I have attached a spreadsheet with the original table and how I need the end result to look.
I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this?
Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense?
I've posted a sample of the info I'm working with.
I have a spread sheet with values in the area of A1:H834
In column H, I have number values from 1-7.
Essentially that number value means that the values in the row are duplicate.
So, for example, if H2 has a value of 4, that means that $A$2:$G$2, really should have an additional 3 rows underneath with the EXACT same data in each cell, however, the way the sheet was created, was to remove the duplicate values and just indicate in column H, the number value of how many duplicates $A$2:$G$2 really is.
I need to unpackage this and create what it was originally. What type of formula can I use, to look at the value in H2, and then insert underneath that number of rowes with the exact same data as A2:G2 and do the same for the remainder of the table all the way down to A834:G834
I have a column with (for example) 5 entries A1, A2, A3, and so on. I would like to pick up this data, tranpose it into a row, put it on the 2nd sheet, but (again for example) be specific about where i want it to go. I may want to have A1 at B2, A2 at E2, A3 at H2, etc
I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
I am trying to delete many rows in a spreadsheet with over 45000 records. There are 10 columns and column B is the determinant of whether a row should be deleted. The criteria is that if column B contains format like 'P00000xxxx, then it should be retained.
Otherwise, the whole row will be deleted. I started to record some codes but then was stuck with the deletion portion.
Below are the codes that I tried to use:
Dim rng As Range, PCrng As Range, PCstring As String
Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Set rng = Range(Selection, Selection.End(xlDown)).Select For Each PCrng In rng If PCrng.Value " 'P00000xxxx ' I don't know how to search this format, the last 4 digits will vary from case to case.
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
1. Data (Let's say 5 fruit names are entered) will be entered in sheet1 (row wise) 2. Data (Let's say 5 flowers names are entered) will be entered in sheet2 (row wise) 2. Entered data in sheet1 and sheet2 should get automatically populated in sheet3 under respective headers (header 1 - fruits and header 2 - flowers)
Rules : 1. We will have Headers with one default empty row 2. With addition of every row in sheet1 or sheet2, a row should automatically inserted in sheet3 under respective headers and populate data.
I have a formula in the first worksheet called 'Summary' of my workbook. This formula calculates average of the data in other worksheet. How do I change the worksheet name in the formula dynamically?
e.g. The second worksheet is January. The formula in cell A1 of worksheet 'Summary' calculates the average of the data in the range A1...A12 of worksheet 'January'. When I copy this formula to cell A2 in worksheet 'Summary', the worksheet name in formula remains 'January' and it does not change to 'February'.
One more important thing is that the worksheets are being added automatically i.e I can not define all the worksheet at first go.
What I need to do is basically use one sheet from my workbook as a 'template' for all the other sheets, but in a way that when I make a change to a formula on my 'Template' sheet all the other sheets based off of it follow suit. I need to do this because for 100000's of rows, spread across different sheets, I have product information populating Columns A-G, and their unique cost in Column H. I need to apply markups to each product (row), but all of these markups are standard across every product and thus every sheet. So in the perfect world I would be able to edit the formula on Sheet #1, and then have that change be reflected in every other sheet.
So let's say I have something like this on Sheet #1...
[URL] .....
Where Column C is a product of A & B...ie. "=A2*B2"
What I need to happen is on Sheet #2 is for it to somehow pull the same formula that is on Sheet #1 in Column C, but apply that formula to 'A2' & 'B2' on sheet #2. And I also want it to update whenever I change the formula in Sheet #1.
If it requires that I set up a template on a completely different workbook, that is perfectly fine as Sheet #1, my 'Template' Sheet, does not hold in actual product information.
I am trying to get the following routine to work. I need this formula to run dynamically when a user changes cell F27.
Sub PotentialUnitSales() 'Dim x As Range Set Range("J27:J200") = "R[1]C[5]-(RC[5]-RC[3])" 'Range("J27").FormulaR1C1 = "R[1]C[5]-(RC[5]-RC[3])" rng = Range([J27], Cells(Rows.Count, "J").End(xlUp)) End Sub
Below is the code. It seems to be creating 50 thousand rows below the data already entered in the worksheet "DELPHI DATA". What I need to do is change it to only add those formulae or pasted values to as many rows as already have data entered in them (which may eventually approach 50000, but may remain at only a few thousand.)
I want to write a formula that inserts a column and then does a vlookup in that new column using the cell just to the left of it as the first variable. The problem is, this could be in any random column so I can't do a static reference to $A2 as an example. So, my question is...
Instead of using =vlookup($A2,Sheet1A1:B50,2,false)
How do I replace the $A2 part with some code that says, "the cell to the left of here..."
