Rows To Columns Not Using Paste Special Or Transpose Function
Jul 10, 2012
I need a formula to transpose rows to columns of a large table. The transpose array formula is not working for me. I know there is a column and row function formula to do this, but I can't remember it.
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?
I am trying to copy one worksheet using the "move/copy" function that is available when you right click a tab name and want to copy the worksheet in the same workbook
The steps I'm using are:
1. Right click the tab name 2. Select Move or Copy, 3. Select Create a Copy 4. Click OK
Doing all of the above does not work. When the new worksheet opens, all columns are the same width. It seems to be stuck on "autofit column width" setting of 8.5. The original worksheet is several columns wide all with different width settings.
I've also used Copy, Paste Special and selected column widths and that does not work either.
I have two excel tabs on a spreadsheet, one titled "Reviews" and one titled "Details". On my Reviews tab, I have:
A B C D 1 0 0 1 0 0 0 0 1 1 1 1 . . . . . . . .
etc.. On my Details tab, I have
A 1 0 1 . . . B 0 0 1 . . . C 0 0 1 . . . D 1 0 1 . . .
(The same information, but presented in different ways). I'm trying to continue to make my Reviews tab as long as possible to match my Details tab but when I drag the cross to continue the pattern, it doesn't follow what I need it to. How can I continue my Review tab down for 600 or so fields to match my Details tab even though they are both in different layouts?
Is there a simple way to combine Paste Link and the Paste Transpose function? I receive a lot of data in rows that I need to put in columns for display purposes. Using transpose is great for doing that but I would like to link it using paste link so I don't have to keep doing each time the data changes. I have recorded macros to see what is happening when I perform the operations seperately as shown below but have been unable to combine them.
I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
Got a bit of an issue with using the paste special function. I am trying to record a macro that would copy a group of cells that are verticle, and then paste them onto a new row in a different sheet.
I have done this in the past by enabling the record macro function and inserting a new row going back to the sheet with the data selecting the data copying it, and then selecting pasting area using paste special and enabling the "Transpose" box.
Although today i found this didin't work in fact it (this is the best i can describe it) merged each cell with the cell below givng a type of square function as can be seen below.
I have a question regarding COUNTIFS. I have data arrayed vertically and horizontally on a worksheet. Date - Column 1 Column 2 etc. Criteria data (flight information) is listed under these columns. I'm trying to get the COUNTIFS formula to count the number of times a particular flight occurs within a date column.
My formula is
=COUNTIFS(C27:G24 (this is the field that contains the flight info), C6 (this contains the particular flight I'm looking for), C16:G16 (this contains the date range, C5 (this contains the date I'm looking for)
When I use this formula I get the result #VALUE! back. I also wanted to ask if there is any way to use the paste special function but not lose the original formatting/formulas from the cells one is pasting from.
Is it possible to create a User Defined Function that replicates the "Copy/ Paste Special" function? I tried recording a Macro and using that as the basis for the User Defined Function but it didn't work.
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
I have 19 rows of data spanning 184 columns, and I'm attempting to transpose them to be 19 columns and 184 rows, which would normally be easy. The problem is I need to be able to transpose the numbers while still keeping the formulas on a separate worksheet linking/connecting to the correct cells. I almost need to be able to cut/special paste - transpose, but that's not possible... I don't think.
I have some sales history data for products which I need in one row. Each product has 4 rows x 12 columns (months) of data under each other. I need the 48 months of data in one row per product. A sample of the type of data I have is below (product A) along with the expected result. In the real data there would be many products not just one as per my example.
Sheet1 *ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW1 Sample Data
how do i transpose from rows to columns i have 545 names with addresses Sheet1 GH221name23 adress line124 adress line225 phone26 fax272name28 adress line129 adress line230 phone31 fax323name33 adress line134 adress line235 phone36 fax37 city384name39 fax40 city Excel tables to the web >> Excel Jeanie HTML 4
selecting each block of name+address is tedious --how can i do it in one go also the number of rows varies in each block -- at places there are 4 rows at other places 3 rows and so on
I have some summary results stated in row format by GL at the row header. I would like the GL to be the column header and the unit to be the row header. I tried using the index function but keep receiving #VALUE! error. Sample attached.
refer to the attached file. I have series of continuous data which is numbered from 1 to 100. They are arranged in vertical order. Due to some application, i need to convert them into another sheet so that they can be viewed horizontally. I used HLOOKUP to obtain the data from sheet 1. Now i have only 17 groups of data and i still have 83 more data to be input in the future. Is there any VBA code that can help me "extend" the equation in sheet2 so that whenever i key in the data in sheet1, the corresponding data will be generated automatically in sheet2?
transposing columns and rows in an Excel worksheet. This data is a table of part numbers requested vs. received over several different days. Currently, the data is sorted by date across the columns, and each date is split into two columns for requested and received. The rows are labeled with the individual part numbers.
I would like to format the data so that the part numbers appear as the column label, and each part number label is split into two columns for requested and received. Also, the date would then signify the date of each value in the table. I have attempted to use the transpose command, but this only gets me half-way there - still struggling with the splitting of the columns. I can drag a simple "= CELL" command, but the existing data is too cumbersome to build this. I thought a macro might be more efficient.
The file attached is a snap-shot of the data, but will display the formatting challenge I have. The worksheet "Given Format" is the current, while the "Desired Format" is the desired.
I want to transpose the phone number and website over where the name is? I know I can do a copy and paste special and transpose but then I have do it individuallly and I have over thousands rows.I have attached an example.
I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).
The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.