Transpose large data from columns to rows

Hello thanks for the help if possible.

I have 2 columns. Column A contains names of employees column B
contains the trainings the employees attended.

Each employee has different number of trainings (I got this data
sorted by simple pivot table)

A                                        B
Employee X                    training 1
Employee X                    training 2
Employee X                    training 3
Employee Y                    training 3
Employee Y                    training 2
Employee Z                     training 5
Employee Z                     training 7
Employee Z                     training 3
Employee Z                     training 1


I would like to somehow change data that the name of the employee
would be in column A, and all his trainings would be moved (instead of
vertically) horizontally (to columns B, C, D,E=85). So:

A                                   B
C                      D                       E
Employee X            training 1          training 2          training
3
Employee Y            training 3          training 2
Employee Z            training 5           training 7
training 3          training 1

Any ideas how to achieve this? Transpose could be used to simply
change this layout, but the number of total employees is too large so
it would take to long.

Thank you
0
Klemen25
3/10/2010 7:30:28 AM
excel 39879 articles. 2 followers. Follow

3 Replies
2104 Views

Similar Articles

[PageSpeed] 27

Perhaps more understandable result I seek:

ABCDE
Employee X  training 1  training 2  training 3
Employee  Y  training 3  training 2
Employee Z  training 5  training 7  training 3  training 1
0
Klemen25
3/10/2010 7:37:32 AM
I managed to find the problem myself, useing pivot table, if forumals
and deleting blank cells. Took me some time, but I did it :)
Thanks
0
Klemen25
3/10/2010 9:39:48 AM
Without pivot table

Sub lineemup()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) = Cells(i + 1, 1) Then
nc = Cells(i, Columns.Count).End(xlToLeft).Column + 1
Cells(i + 1, 2).Resize(, nc).Copy Cells(i, nc)
Rows(i + 1).Delete
End If
Next i
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Klemen25" <klemen25@gmail.com> wrote in message 
news:86bcaa4c-dcc4-4c3b-a7ba-6c68dc11dd17@d27g2000yqf.googlegroups.com...
Hello thanks for the help if possible.

I have 2 columns. Column A contains names of employees column B
contains the trainings the employees attended.

Each employee has different number of trainings (I got this data
sorted by simple pivot table)

A                                        B
Employee X                    training 1
Employee X                    training 2
Employee X                    training 3
Employee Y                    training 3
Employee Y                    training 2
Employee Z                     training 5
Employee Z                     training 7
Employee Z                     training 3
Employee Z                     training 1


I would like to somehow change data that the name of the employee
would be in column A, and all his trainings would be moved (instead of
vertically) horizontally (to columns B, C, D,E�). So:

A                                   B
C                      D                       E
Employee X            training 1          training 2          training
3
Employee Y            training 3          training 2
Employee Z            training 5           training 7
training 3          training 1

Any ideas how to achieve this? Transpose could be used to simply
change this layout, but the number of total employees is too large so
it would take to long.

Thank you 

0
Don
3/10/2010 1:35:46 PM
Reply:

Similar Artilces:

Edit Access data in Excel
I've linked to Access table using OLE but cannot send edits of the data back to access. How do i set up the liks such that changes made in Excel are returned to Access H ...

moving selected data to to other work books
I have set up my accounts in a excel work book. I need to move selecte information from one sheet to different sheets. let me explain further There are sales figures for example David, Samuel, and John in on sheet for a particular day. Individual sales figures for the thre different people need to be copied to three seperate sheets can this b done? Please please help -- Message posted from http://www.ExcelForum.com Hi as a starting point: http://www.rondebruin.nl/copy5.htm -- Regards Frank Kabel Frankfurt, Germany "Chris mills >" <<Chris.mills.169f2v@excelforum-nospam....

Technical formatting in data labels
Hi, I am using a bar chart to show data collected from scientific experiments. I need to label each bar on the x axis with a seperate value (100uM H2O2, 200uM H2O2 etc). However, the formatting shown in the cell is not carried through to the chart - i.e. in H2O2 both 2's should be subscript. I am sure there must be an obvious way to do this - lots of people must need it for chemistry (e.g. formulas) and general maths (e.g. cm2). Any ideas? Unfortunately, there is no easy way to maintain formatting when information in a chart is from a cell link. You can adapt the ideas at http...

In Excel, how do I only add numbers in a row that are over 0?
In my monthly spreadsheet, I have 31 rows and 8 columns in which I have both negative and positive numbers. Each row must have a total to the right, but I only want the positive numbers to be taken into account. However, the negative numbers are not always in the same column. Any way I can do this? Thank you! use <sumif> see help Lollycat <Lollycat@discussions.microsoft.com> wrote in message news:F3F3ECF4-8DBD-4E8F-A852-54A32DEAD592@microsoft.com... > In my monthly spreadsheet, I have 31 rows and 8 columns in which I have both > negative and positive numbers. Each r...

Data Entry Errors Exist In Batch
When trying to recover a payables batch through Batch Recovery, we keep getting this error message: "Data entry errors exist in batch "XXXX". Use the Batch Recovery window to process this batch." We are using Batch Recovery and it still isn't working. Any suggestions? -- Deanna Rowen Highfields, Inc. Once recovered, print the batch edit listing to see what the errors are. Correct the errors and re post. -- Ian "delyro65" wrote: > When trying to recover a payables batch through Batch Recovery, we keep > getting this error message: > >...

Hide Column if Grand Total is 0
I have a macro that subtotals based on sales order. If a column (e. price adjustment) has a Grand Total value of 0 I hide the column befor printing the report. Is there a macro that could search for the word Grand Total then go to each column starting from H through T on the Grand Total Row, se if the value is 0 and hide the column if the value is 0. Any help will be greatly appreciated. S -- Message posted from http://www.ExcelForum.com Hi, Try something like: Sub HideGT() For Each c In Range(Cells(1, "A"), Cells(ActiveCell.End (xlDown).Row, "A")) If UCase(c.Value...

Rolling 12 month data charts
I was wondering if someone could give me an example to help me get my head around the best way to do this. I have posted before on how my charts us data tables and that I would like to change that. I need to add annotations to specific months that stay with the data point as the calendar year rolls to the next month. What I am needing is someone to help me out by giving me an example of what my chart should look like with the chart and then the data below in a similar format as the data table. I have close to 35 charts that I have to do this for and I want to make sure I start this new pro...

Pop Up
I have read and understand why Outlook2002 up gets the pop up. But.. I have two Exchg servers. 1 in Houston, 1 in Seattle connected by a T1. Only the OUtlook2002 users get the popup in Seattle for the Exchg server in Houston. These users do not have mailboxes in Houston. We do not have any public folder replication. Users in Houston do not get the popup. The Seattle server is config correctly for Routing Calculation Server. Why would the Seattle machines even want to retrieve security or whatever from the Houston server???? Tks, Jim Jimawatson wrote: > I have read and understand why O...

query to delete a row with foreign key contraints...
Hello, I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreign key with table 2) Date_To_Work (date type) I want to delete a row from Table1. I can engineer a simple delete query. DELETE * FROM Table1 as D WHERE D.Dept_Name = 'sales' However, I do not know how to deal with the foreign key constraints. Any help would be appriciated. Thanks in advance, RABMissouri2010 You...

Copy data into next avaiable cell #2
Thanks Katie I will give it a go and see what happens. Chi -- Chise ----------------------------------------------------------------------- Chisel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=935 View this thread: http://www.excelforum.com/showthread.php?threadid=26840 ...

Data seen in Reports from Multiple countries
Hi, We happen have various business units which represents different countries. There are users who have access to only business unit level but when they access the reports like "Account Overview","Account Distribution", they are able to see data for other business units. Can anybody help me in pointing out where the problem lies and how to resolve it? 1) Can you check the Account privilege of the user see if the privilege is in Business level? 2) Are the records on other businesses shared to the user? -- This posting is provided "AS IS" with no warrantie...

Merge Rows of like data
I get an excel sheet from our accounting department that I import into a database for reporting. The data is straight forward in most cases, but today I noticed that there are a lot of rows that are duplicated except for two columns. Is there a macro or a way to run a script that would look at these rows and compare them and if all the columns in the row match except for these two, combine the the columns (these are number columns so I would like to add the numbers) and create a single row? If so this would really help me get the reports they need. Any help is appreciated. Here is ...

Automatically combining data from several worksheets
Hi, I have several worksheets in a workbook - individual quotations (one sheet for each product type). Each sheet has standard information in each row - part no., vendor, quantity, cost price, sales price etc I am trying to find a way to combine the data from the several worksheets/quotations into a master worksheet for ordering i.e. combine the rows from the various sheets (if quantity >0) Any ideas or help appreciated Robert One way is to list all the worksheet names in a column on your master worksheet (if you have a lot of worksheets there are ways of producing this list qu...

faster way of data entry
I would like to learn how to enter data on excel faster. I have a large datasheet, my samples are in "rows" while the tests are in "columns". The samples are arranged in numerical order but when I'm testing them, I group them into fixed groups of around 90-100 members. The members of these groups are randomly arranged. I get my result one group at a time. And I conduct different tests on each fixed group. I just score the test as normal or abnormal. For example, I'm testing test A on goup 1 which is a fixed group of 90 members and the result is normal for al...

Auto Fill Columns
hi all, i have an excel sheet with student's grades: column A has an icreasing number, column B has the name , column C has the class, column D has the grade. I would like if it is posssible when i filter the students by class at the end of the sheet to be added two columns automatically [with macro, code?? i dont know how :-( ] the first one write if the student is 1st, 2nd, 3rd, etc in the class depending on the grade (column D) and the second column write if the grade is lower than 5 the student pass or fail! Is possible the column A when i use the filter to change in a number that is...

How do I get to print a spreadsheet WITH rows and columns?
I'm making a spreadsheet consisting of all of the vendors I deal with, my account number, their phone numbers and addresses, and some other information. I'm not a regular user of EXCEL. When I save and print out the spreadsheet it has no vertical and horizontal lines for the rows and columns. How do I get to print the spreadsheet with those lines? Thanks Adjust your "page setup" under File | Page Set up | Sheet | Gridlines ... Check the Gridlines box. Dejola wrote: > I'm making a spreadsheet consisting of all of the vendors I deal with, > my account number, th...

Special Text to Column question
I have a special problem that I hope the gurus here can solve. Once a month or so, I receive a text file that includes about 400 lines of 323 characters each. Groups of characters have different meaning depending on their position in the string. Some are grouped in as little as one character, other groups are as long as 25 characters. There are no delimiting characters like commas, spaces or the like I usually open the file in Excel and click Text to Columns and select Fixed Width. From there I scroll left to right and click on the necessary widths for the groups involved "9, 5, 2, ...

Restore was two weeks old data
Any reason anyone can tell me why when we did a restore dated may 5 it did not have quotes past april 14th? ...

Delete duplicate rows of data when two columns are the same
I have a worksheet and need to delete duplicate rows when two of my columns are the same. So as below: For every "EMPLOYEE", look at the "JOB ADP" columns - if its the rows are the same, look at the "MASTER COST CENTER" column and see if thats the cooresponding rows are the same. If yes, delete that record (row). If no, leave it. T U W X 1 Job ADP EMPLOYEE Master Cost Center Strd Hours 2 123500 111111111 98140 ...

Crosstab query columns not adding correctly
I have a crosstab query based off of a table "tblClaims". TRANSFORM (Nz(Sum(tblClaims.Count),0)) AS [The Value] SELECT tblClaims.LOC, tblClaims.RvwRsn, Sum(tblClaims.Count) AS Total FROM tblClaims GROUP BY tblClaims.LOC, tblClaims.RvwRsn PIVOT tblClaims.Report In ("C170","RC 85"); My end result should have one row of data for, example: LOC RvwRsn C170 RC 85 72 BTMJ 25 10 But instead I am getting: LOC RvwRsn C170 RC 85 72 BTMJ ...

Look up Data from Worksheet within same Workbook
Hi All, Sorry, this may seem like an elementary question but.... First of all I have three seperate worksheets containing data within my workbook. I want to return all rows from each of those worksheets that matches a certain criteria to another worksheet, without having to manipulate the data. e.g. I have charge codes beginning in 1-90XX, 1-91xx, etc... These appear multiple times in the worksheets that contain data I want to search sheets 1 - 3 to see if any rows contain them & if they do, I want all these complete rows to appear in another worksheet within that same workbook. VLO...

Retrieving deleted data
I attached a 12 page Word file to an Outlook Express message, but before sending the email opened the file and deleted all but the first page (as this was all I wished to attach to the email). Now, after sending the email, I find when I open the word file there is only the first page. How can I retrieve the deleted pages? You would have had to save it to attach it, hence the problem. You saved the shortened version. If you have the backup option set, you will find a backup file in the same folder that will be the previously saved version. Hopefully you haven't saved twice! ...

Data Refresh Speed
I have a spreadsheet that extracts data from Sharepoint and then performs multiple calculations on that data. When I refresh the data, it then also recalculates every formula and this is starting to take a long time due to the sheer number of calculations that are being performed. I'm wondering if there is anything I can do to speed up the processing, like multi-threading or something? Any settings or options I can change to make this faster? See my website http://www.decisionmodels.com/ and white paper http://msdn.microsoft.com/en-us/library/aa730921.aspx Charles _________...

Excel VBA: How to select only rows containing data?
Hi all, I'm writing a macro to automate formatting of a commonly used report. The number of rows containing data will vary on any given day. I'd like to select only the rows that contain data, set the heights for those rows to 63.75, and leave the other rows untouched. Does anyone have a suggestion on how to select only rows containing data? I'm running Excel 2000 on Windows 2000 Best regards, Russell Somers try Sub setrowht() For Each c In Selection If c <> "" Then c.RowHeight = 63.75 Next End Sub -- Don Guillett SalesAid Software donaldb@281.com "Russel...

Altering Combo Box Choices and the bearing on historical data....
I am wracking my brain trying to not screw up years of data in a database I created at work. I need to updated some drop down menu choices and some that have been used need to be eliminated. I am kind of brainfarting here and I want to make sure that by changing these items or removing them that it will not affect the historical data in the tables where the full records are kept. I need some reassurance if at all possible. I made a dummy database and have played around with it and it seems to maintain the data but this is a little nervewracking. My only other option if this w...