Automatically Copying 9 Rows And Converting To Columns

Dec 17, 2012

I need to take a list of about 1,500 rows of data (single column) and convert it into columns of 9 entries. I know that I can select nine rows and then paste using transpose... But any way of "automating" the process?



becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....

View 5 Replies


Converting Formulas To Values While Copying / Pasting Columns Between Worksheets (VBA)

Feb 6, 2014

I have code that "mostly" works great. I'm copying columns from one sheet to another sheet, but in different columns. I have unique code for each copy/paste. The source is mostly raw data. However, there are a few columns that are formula-based, and I'm having a problem. I've attached my code, and it works, but it takes 20 minutes to complete.

I'm very new to macros, but I think(?) I know enough that a data source with 2,000 rows and 30 columns shouldn't take 20 minutes to complete the macro calculations. All of the columns (copy and paste) in the code are pure data. The only exception is the column labeled "AI". Is there are shortcut, other than creating new columns in the source data sheet and pasting these results as values? I put the specific pieces of code that I'm referencing in bold.

View 3 Replies View Related

Converting Columns Into Rows

Mar 23, 2013

I have a spreadsheet that has many columns I need them to be stacked into rows to import them into a publishing program but I don't know how to accomplish this automatically. right now all I know how to do is insert rows and drag and drop the info which is very time consuming.

I have attached a sample of the sheet. A2 through A12 is how I would like all of the data to appear. However A14 through G14 is how it looks now.

View 5 Replies View Related

Converting Columns Into Rows?

Jul 14, 2013

excel to convert the data from columns into rows as mentioned below

current excel data is


Expected data

There are close to 10000 records like this is there any formula/function to change this data.

View 11 Replies View Related

Converting Data Into Rows And Columns

Jan 12, 2010

I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?

Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe

View 9 Replies View Related

Automatically Copying Rows With A Particular Attribute To A Different Worksheet

Oct 23, 2008

my problem involves displaying rows that meet certain ‘criteria’, on a separate worksheet, in real time. The criteria will be determined by a selection made from a dropdown list in the row in question.

ILLUSTRATION: Let us say (for example) that I am dealing with a worksheet for MEN’S DATA. Each man’s data will be on a separate row. On each row, one of the cells has a dropdown list having 2 options, SHORT and TALL. I have a separate report worksheet for TALL men. I mean, I want every row for which I select the TALL option, to be copied to my TALL report worksheet. I want the selection of the TALL option to be a kind of trigger that promptly copies the row to the TALL worksheet, once I make the selection from the dropdown list.

View 9 Replies View Related

Converting Conditional Data In Rows To Columns

Aug 23, 2009

How can I convert a table from rows to columns as shown below??? Thanks for the feedback...

Current file with:

Unit_no work_order_date

1234 1/1/2005
1234 6/10/2006
1234 10/1/2007
4321 2/3/2004
4321 5/12/2005

Change to:

Unit_no work_order_date work_order_date2

1234 1/1/2005 6/10/2006
1234 6/10/2006 10/1/2007
4321 2/3/2004 5/12/2005

Will be working with data to sum data between the two work order dates...

View 9 Replies View Related

Excel 2010 :: Converting Multiple Columns Into Rows?

Aug 24, 2012

I am trying to convert

Excel 2010


View 9 Replies View Related

Converting Multiple Rows / Columns To One Single Long Row

Nov 22, 2012

I am working on putting together a very large spreadsheet covering multiple data sets over multiple states/years. I am trying to convert the data that I have in one spreadsheet (that is arranged like the example below) and make it so that I can paste the data into another spreadsheet as one single row: i.e, 1,651 would follow in the column to the right in the same row as 6.4 and so on. Right now I am having to copy and paste row by row and it is going to take me years.


[Code] .......

View 7 Replies View Related

Automatically Copying Rows From One Sheet To Another Depending On A Cells Input

Jun 2, 2009

i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...

View 9 Replies View Related

Copying Formula With Fixed Rows But Different Columns?

Nov 27, 2013

Im using this formula in a spreedsheet =(B$3*0,5)+(B$3+(B$4*0,5))+(B$3+B$4+(B$5*0,5))+(B$3+B$4+B$5+(B$6*0,5))+(SUM(B$7:B$28)*4)

What i want is to be able to drag this down for multiple rows but i want the formula above to only change columns i.e.

Next row gets C$3 and so on.

View 3 Replies View Related

Copying And Pasting Formatted Columns And Rows

Jul 10, 2014

So, I have a document 25 pages long. It has 26 sections (1-26) and each section has up to 10 (.1 - .10) sub sections which contain 16 (a-p) sub-sub-sections. There are 6 columns of varying widths. Some cells, in a row, merge into adjacent cells.

I would like to break this document into 26 documents - one for each section - so that I may recombine and regroup them in the future incorporating a few modifications. Whenever I copy and paste to a new Workbook, I loose the formatting of column widths. Resetting these alone takes lots of time.

I have come up with is to duplicate the entire document and delete everything above and below the section I would like to save. Then repeat for next section. There's got to be a better way?

View 10 Replies View Related

Macro For Copying Cells To Columns And Rows

Sep 22, 2012

Why does my macro mess up on second line C23 and not places Play Equipment or the amount in the right column it stays on row C22



Private Sub CommandButton1_Click()
Dim SheetName As String
SheetName = "Estimate1"
SheetName = InputBox("enter the name of a sheet to use", "sheet name", SheetName)


View 2 Replies View Related

Copying Formulas Into Adjacent Columns But Same Rows

Nov 13, 2006

If I have formulas in A1, A5, A19, and A36, highlight them with goto, how do I move them into B1, B5, B19, and B36? Copying and pasting puts them in B1, B2, B3, and B4.

View 9 Replies View Related

Fill Formula Reference By Rows When Copying Across Columns

Dec 20, 2007

I am trying to fill in reference cells horizontally but fix the column and increasing the row number as it fills. For examples I want to start my refence as "=A1" in cell B1. I want to copy this reference horizontally to column Z1 but want the refences to be A1, A2,...,A26. Excel only seems to increase columns when filling horizontally and rows when filling vertically. Transpose works but it's an array which would slow the spreadsheet down.

View 4 Replies View Related

Excel 2010 :: Copying Data Form Columns To Rows?

Mar 27, 2012

I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.

Below I attached examples of the Spreadsheet

Financials SpreadSheet
Need to have the data in column B to F put their respective cells in row in the
Master Spreadsheet
So we would have 5 rows.

Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril


View 6 Replies View Related

Automatically Converting Non GBP Payments Into GBP Amounts

Feb 12, 2014

I have some sales records that contain GBP payments, along with AUR, USD and EUR.

The GBP payments are not a problem, but a formula which could automatically convert AUR, USD and EUR into GBP payments would save a lot of time and reduce any chance of errors.

I have attached an example of the current layout.

Ultimately, I would like to put the sale in it's GBP amount where the boxes are blank & then a formula that detects either

A) If Original currency (the F column) is not GBP, then multiply the amount in the C column by whatever the exchange rate is in the G column.

B) If the exchange rate column G, has anything other than N/A, then multiply this by the amount in column C.

So, if the original currency in column F is in GBP, then I can enter data into the C column, and it will not change, however if the original currency in column F is not GBP, I would like the data in column C to change.

View 3 Replies View Related

Automatically Converting Numbers Stored As Text To Numers

Oct 28, 2008

I am using an ODBC database connection to input data into an excel file. When I input the data excel stores the numeric data as text and puts a ' in front of the number. There is an exclamation mark beside the cell which allows me to convert the data back to numeric format. I need excel to do this conversion for me automatically when new data is added to the file.

View 9 Replies View Related

Copying Invisible Columns, But Not Invisible Rows

Nov 4, 2008

I'm using the following code, which I figured out quite by accident out of happy coincidence that somebody else on here asked about selecting only visible rows:

View 4 Replies View Related

Copying Data From One Worksheet To Another Automatically

Jan 9, 2009

I have 2 worksheets that share some of the same data. However I do not want to have to populate the same information twice. Instead I want to be able to type the information required in the one worksheet and create an instruction for the data that overlaps with the second worksheet to automatically load. The catch is I need it to find the next blank row every time to add the data. EG

Worksheet 1 has columns: Name, ID Number, Date, Gender
Worksheet 2 has columns Name ID Number.

I want to be able to input data on worksheet 1 only and the the Name and ID number will be added to the relevant columns in worksheet 2.

View 2 Replies View Related

Automatically Copying Cell To Other Sheet On A Condition

Aug 14, 2007

I have 41 Sheet in my Workbook.

Sheet1 Contains all the Data

Now For every Staff I have Four Individual Sheet of "Seg"

What I want is to Copy Data From Sheet1 to each sheet of the Staff according to there Respective Details.

1st record to be copied automatically to Sheet7 (CAS B) in Bought Side Column,
2nd record to be copied automatically to Sheet6 (CAS A) in Sold Side Column
with name of toys in both the sheet.

View 9 Replies View Related

Converting Text To Columns For A String

Jul 31, 2012

I have a problem in converting text to columns for a string that has 1000+ entry's. Excel bumps me out after 851 using the Split function.

The data I can only download in a text format has >1000 fields that are seperated by ",". Not a problem so far but when I attempt to convert to columns there is a maximum limitation of 851.

Data1,Data2,Data3,Data4...................Data1000,Data1001,Data1002 etc...

I have tried:

Sub Split_Delimeter()
Dim Words() As String
Words = Split(Cells(2, 4), ",")
MsgBox "String contains " & (UBound(Words) + 1) & " words"
End Sub

View 2 Replies View Related

Converting A String Of Letters In A Row Into Columns

Oct 5, 2009

I have a set of letters in a single row DISATAGFOORCBORDQFINFCOBBKIEHDSMCNRSKARDFCJSLCQCECFVSBCBOYKPVYKC. I want to distibute them across 5 columns such that each letter occupies one cell. I might want to specify a different column width later on so the solution should be flexible.

View 5 Replies View Related

Copying Cell Contents From Another Spreadsheet That Automatically Update

Apr 26, 2013

I am working on a manpower spreadsheet and need for one spreadsheet to extract data from another and automatically update if it is changed and I am struggling below is an example of what is on spreadsheet 1:


and the information from spreadsheet 1 is copied to spreadsheet 2, however if amendments are made to spreadsheet 1 they are automatically updated on spreadsheet 2:

Spreadsheet 2:


View 1 Replies View Related

Automatically Copying Selected Data Based On Criteria

Feb 16, 2007

I am have a spread sheet (sheet 1) with a number of columns and what i am looking to do is see all the people that are participating in a certain stage in the process that is not common, So what i am wanting to do is copy the names and the corresponding number of these people (on sheet1) over to a new worksheet(sheet2) based on a yes or no criteria further on in the spread sheet(sheet1). sheet 2 has additional columns that the workers here would need to fill in.

The criteria is in sheet 1 cells Y2:Y2000
The number is in sheet 1 cells D2:d2000
The names are in sheet 1 cells E2:e2000

If its at all possible i would also like to then make the names of those people on sheet 1 a hyperlink to the additional information

View 4 Replies View Related

Creating Panel Data By Converting Several Columns Into One

Jun 20, 2011

My problem: I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.

I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;

(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)

Country 2001 2002 2003 2004
x - - - -
y - - - -
z - - - -

The identifier here is country, and the data is as such structured as cross sectional data, with each yearly account of gdp being a variable. Now, I need this data as panel data, organized as below:

Country year gdp
x 2001 -
x 2002 -
x 2003 -
x 2004 -

I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.

View 12 Replies View Related

Converting Data In Two Columns Into A Row On Separate Sheet

Oct 20, 2011

I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.

Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6

Ideally the row will look like.

Example - Sheet "Status WS"

Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date

There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.

View 4 Replies View Related

Paste Rows Without Overwriting Other Rows (automatically Shift Other Rows Down)

Jan 3, 2010

The number of rows you are pasting and then you can select that same number of existing rows and use "insert". This is what I'm trying to achieve:

(before paste)

(after pasting unknown number of rows)

Is there really no built-in way to do this?

View 6 Replies View Related

Excel 2007 :: Automatically Transfer Columns From One Worksheet To Another In Multiple Columns?

May 4, 2013

I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..

Excel 2007


View 9 Replies View Related

Converting Column To Rows

Feb 22, 2010

I have a excel spreadsheet that is setup as follow:Johnson, Ashley0916BF00001U9/16/2009Test1POSITIVE0916BF00002R9/16/2009Test1POSITIVE0916BF00001U9/16/2009Test2NP0916BF00002R9/16/2009Test2NP0916BF00001U9/16/2009Test3NP0916BF00002R9/16/2009Test3NPBlack, Tom0517BF00012R5/17/2009Test1POSITIVE0527BF00003R5/27/2009Test1NEGATIVE0601BF00009R6/1/2009Test1NEGATIVE0517BF00012R5/17/2009Test2NP0527BF00003R5/27/2009Test2NEGATIVE0601BF00009R6/1/2009Test2NP0517BF00012R5/17/2009Test3NP0527BF00003R5/27/2009Test3NP0601BF00009R6/1/2009Test3NP

First column is in goups: First row of each group contains customer's name then followed by test codes. Test codes are usually repeated three times per customer but they have different names (listed in third column as Test1, Test2 and Test3) and different results (listed in 4th column as POSITIVE, NEGATIVE or NP).

What I want to do is to convert this data into a way that each row has One customer name and the columns in front of each customer lists Tests results and dates. If a customer has multiple test codes, then one row to be created for each test code.

Customer NameTest 1 DateTest 1 ResultTest2 DateTest2 ResultTest3 DateTest3 ResultJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPJohnson, Ashley9/16/2009POSITIVE9/16/2009NP9/16/2009NPBlack, Tom5/17/2009POSITIVE5/17/2009NP5/17/2009NPBlack, Tom5/27/2009NEGATIVE5/27/2009NEGATIVE5/27/2009NPBlack, Tom6/1/2009NEGATIVE6/1/2009NP6/1/2009NP

View 9 Replies View Related

Copyrights 2005-15, All rights reserved