Excel 2010 :: Moving Data Organized By Columns To Rows?
Nov 26, 2013
import it into to Stata to do statistical analysis. I always receive spreadsheets like this:
country
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
[code].....
and I have to manually reorganize it like this to import into Stata:
country
year
value
Benin
1991
20
Benin
1992
254
[code].....
Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.
View 7 Replies
ADVERTISEMENT
Feb 10, 2014
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.
View 5 Replies
View Related
Dec 13, 2011
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
View 3 Replies
View Related
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
[Code]....
View 6 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Sep 24, 2009
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
View 6 Replies
View Related
Dec 12, 2008
I have address data all in column A (each paret of the address is on a separate row) which I need to move to separate columns on one row for each address:
ie:
Mr Bloggs (row 1)
123 The Avenue (row2)
The town (row 3)
The postcode (row 4)
Mrs Smith (row 6)
456 The street (row7)
The postcode (row 8)
needs to be: (I have used || to indicate different columns)
Mr Bloggs || 123 The avenue || The town || The postcode
Mrs Smith || 456..... || .......
The problem you see from the above is that they are not always consistent ie. the postcode (zip) isn't always 3 rows below the name for example. Also although generally there is a blank row between each record (this may sometimes be 2) and equally there may also be times when there is a blank line in the address.
Any ideas on how to do this.... I have 1000's of records and really can't afford to go through each one making it consistent before moving it to columns?
View 7 Replies
View Related
Jun 24, 2014
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
View 8 Replies
View Related
Oct 3, 2012
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
View 1 Replies
View Related
Feb 26, 2014
I am trying to separate two rows to two columns. How can I do this for +200 data. For example,
1
Apple
2
Ball
3
Candy
....
I need this to be:
1 Apple
2 Ball
3 Candy
and so on.
show me how I can do this for Excel 2010.
View 3 Replies
View Related
Sep 25, 2012
How do I freeze the top 6 rows and the first 3 columns in excel 2010 work-sheet so when scrolling they remain static.
View 2 Replies
View Related
Dec 8, 2012
I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?
Excel 2010
View 9 Replies
View Related
Nov 20, 2013
For some reason I don't seem to be able to insert any columns/rows/cells in to my excel spreadsheet. This is a problem in both basis sheets and more advanced ones.
View 1 Replies
View Related
Aug 24, 2012
I am trying to convert
Excel 2010
A
B
C
D
[Code].....
View 9 Replies
View Related
Jun 14, 2013
I have 10 columns and five rows worth of data in each of those cells
What I really want out of that spreadsheet is 50 rows and only one column
I want to transpose or invert the data so I can apply some formulas in an easier manner
Is there a command to do that.....not too familar with that transpose feature
View 4 Replies
View Related
Jun 21, 2014
I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.
Is there any way I can retain all of the rows and columns?
View 1 Replies
View Related
May 27, 2014
I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:
A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2
I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:
Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)
Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0
Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0
and so on....
View 9 Replies
View Related
Jul 6, 2014
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
View 6 Replies
View Related
Jun 12, 2014
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies
View Related
Aug 8, 2012
I'm using Excel 2010 on Windows 7.
I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.
Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.
It would look something like this
Sheet1
Lastname
Firstname
Device
Johnson
[Code]...
So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:
1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.
View 6 Replies
View Related
Apr 21, 2014
Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.
View 3 Replies
View Related
Feb 8, 2013
I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text
RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12
MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38
RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1
RED008 - Wickaman and Hoodlum bandwidth
MFR005EP - Various Artists bandwidth
RAZORS010 Future Cut bandwidth
Cell 2
(0.06Gb @ 2.00)
(0.19Gb @ 2.00)
(0.01Gb @ 2.00)
Cell 3
0.12
0.38
0.01
View 4 Replies
View Related
May 1, 2014
Have developed a form for my associates and want them to be able to move to the cellls I want them to fill data in to by merely hitting the TAB or ENTER key vs. having to move the cursor to each cell requiring them to input data.
Want cursor to move in a specific cell order. You would think you could merely say 1M, 2C, etc.
View 9 Replies
View Related
Oct 24, 2013
I have this table with some information that I need to work with. The data is simple, but it has many issues, at least for me, to be able to work with it properly.
Basically, I just need to transfer part of the data from this table to another table (in another order). I failed to use vlookup because I need more than one parameter to search the data (Date, Shift and Sector).
The problem I am having to get the codes from the source table is that the same code sometimes shows up more than once per date, per shift and per sector. It's also not following a numeric pattern. I couldn't elaborate any combination of formulas to get the info I need....
I also can't change the format of the source table, because I'm not the one who made it, it has years already, and is constantly updated day by day by a good old man who doesn't know much about computers.
The file is attached : Example.xlsx
View 4 Replies
View Related
Jan 31, 2013
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
View 2 Replies
View Related
Jul 13, 2009
I am trying to move row data into column data. Here is visually what I need.
Before:
After (one row of data only):
We are trying to import data into our accounting software but we can't until we get it into the right format.
View 9 Replies
View Related
Mar 7, 2013
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------|
1.John-----|Doe-----|123 Main St|
2.New York-|NY------|
3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------|
1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
View 2 Replies
View Related
Jan 16, 2013
How to match 2 columns in excel and retreive data from the 3rd column ?I have an example here as to what I want to do..
ColA ColB ColC ColD
niki delhi neha
vinay mumbaihardik
kapil bangalorevinay
neha patna
pooja goa
hardik kerala
Here is what I want to do for the above.
ColA and ColB contains a standard information which is supossed to be my reference. ColC contains my queries for which I need information about their place in ColD.
So I need to match ColC with ColA, so as to retrieve the matched data (between ColC and ColA) from ColB to ColD. Following is the way I expect my result to be..
ColA ColB ColC ColD
niki delhi neha patna
vinay mumbaihardik kerala
kapil bangalorevinay mumbai
neha patna
pooja goa
hardik kerala
I received an answer in that link
"=INDEX($B$2:$B$6,MATCH($C2,$A$2:$A$6,0))",
Which when tried, surely worked a few months back. I am now using excel 2010. I tried the same again, but this time it does not work for me. Is there something else to do which has been changed in the new excel 2010 ?
View 3 Replies
View Related
Mar 8, 2013
I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.
Data Looks like:
54654
31233
42343
51234
66968
43252
54657
63253
[code]....
Would like to look like:
54654
66968
79282
91596
68185
31233
[code]....
View 5 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
