I have a set of training information in the rows, with dates under it which maps back to names of employee in the left hand side. How do I convert that to Name of Employee Training attended dates expiry date.
View 8 RepliesI 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
I have a spreadsheet in one column contains multiple sets of data separated by commas see example 1.
I need convert the data to rows, each set into a new row see example 2.
Example 1: PS-8800-C,PS-8800-D,PS-8800-076,PS-8800-077,PS-8800-077A,PS-8800-078,2"-SC-4057-A-40H-GT
Example 2:
PS-8800-C (Row 1)
PS-8800-D (Row 2)
PS-8800-076 (Row 3)
I have a table in excel in which every line has as many values as the days of the month (e.g. 31 values first row, 28 the second..) and it goes from January 1948 to July 2014. So it ends up having around 800 rows.
I need to put all of those into a column, but it gets hard because the rows have different lengths. I have seen in this forum some solutions that apply well when the table is regular, but I'm stuck in trying to get a solution for when it's not. I have tried to record a macro using TRANSPOSE, but then I cannot change the cells into this formula in the editor in order to create a loop.
I also have to consider leap years.
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
ABCD 12345
ABD 12345
ABCD 12349
ABDF 12349
ABCD 12358
ABF 12358
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
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.
excel to convert the data from columns into rows as mentioned below
current excel data is
deptemp1salemp2sal2emp3sal3
HRAbc123bcv231sjn987
ITsddds9876ds999dsb123
Expected data
Deptempsal
HRAbc123
bcv231
sjn987
ITsdds9876
ds999
dsb123
There are close to 10000 records like this is there any formula/function to change this data.
I have a variable, ColumnX. I want to use VBA to generate some formula referencing ColumnX. For example: I want to sum the values in A1 to A10. ColumnX has been set to "1" which is the number of column A.
Range("A11")= "=sum("A1:" & ColumnX & "10)"
Obviously the above code doesn't work because I'm putting a number where VBA expects a letter. How can I rewrite my code so that A11 will entered with formula that sums A1 to A10?
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
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe
I am trying to find a macro that look for last non empty cell in column A and them paste a formula/comment in all cells of column B.
View 3 Replies View RelatedI 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?
So
1
2
3
4
5
6
7
8
9
ETC
ETC
becomes 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 etc....
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...
I am trying to convert
Excel 2010
A
B
C
D
[Code].....
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.
3,484
2,179
62.5
2,053
58.9
126
5.8
5.2
6.4
[Code] .......
this may seem like a relatively simple thing, but I want to take two rows of information and just switch them with each other. I want all of the formatting to stay the same though relative to each other. The problem is a color name. I have four colors of a pant that I'm tracking for production and the color order is just slightly flipped so I need to switch two of them. I don't want to have to redo the whole thing, too much info!
View 2 Replies View RelatedAll info goes to the master inventory and then if some enters an amount for respray then it is transferred to the respray sheet. when it is transferred to the respray sheet it put the date in Column A and then put 3 days later in Column B.
Every day it will change and the date in A1 changes to tomorrow but if another item is enter as having resprays then it transfers to the respray sheet and will bump down in column C but Column A stays the in the same spot.
I need Column A and B to move up and down if something in Column C moves up or down and it would be nice to always have the oldest date at the top.
I am attempting to Organize some data; however, I have run into a problem. I will paste a sample of the data that I have and explain the situation....
View 9 Replies View RelatedAm trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
View 9 Replies View RelatedI have a column of data (A1:A38) that I need to convert to a row (A1:AL1) and I can't figure out how to do it. Forgive me if the answer is in the forum but I don't know excel well enough to use the proper search terms. I tried searching on pivot which only yielded pivot tables which I do not *think* apply to this.
View 2 Replies View RelatedWE have many spreadsheets with 1000's of part numbers that need to be converted--- currently the spreadsheets have data in B1 and B2 (part number and description), B3 and B4, and so on--- we need to be able to take B1 and send it to A2, so the part number and the description then end up on the same row--can anybody help this Excel novice convert this data??
View 14 Replies View RelatedI would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
I have 500 rows of data with 6 columns. Columns A,B,C,D,E are text, and column F is values.
I'm looking for a formula where I can say for any of these rows where the data in columns A through E match each other in those rows, combine them into one row of said text, with column E values totaled. I thought I could use SUMIFS for this, but I only know how to define SUMIFS with the criteria being a specific cell/text/value for which it should search on. In my example, the data in the cells can be anything, I just need rows to be "collapsed" to one row for like data.
I've manually inserted of what I'd like the program to be able to figure out, and a description of what I've tried, and failed at, so far.
The product codes and prices would of course all be unique, I've just mashed this together so there's some generic info, and I'm not divulging confidential.
I'm busy cleaning information from an excel spreadsheet. Not all the columns are populated. e.g. Row 17 will only have an email address in column E whilst row 18 will have a name, surname, company and email address (same as in row 17).
Using the remove duplicate function, removes row 18 and thus leaves me with the row that only contains the email address.
What would it take for me to be able to remove the rows that do not have the additional columns populated?
I have a workbook where on one sheet various peoples information is listed and based on their organization the information is then linked to one of a few different worksheets for costs. On the cost worksheets, when a new person is added to the original worksheet, their information populates the next row. At the end of the cost columns I have a the total cost of each person added together. What I need to do is have these totals continue to move down the worksheet as new people are added. Is there a way to do this in excel using VBA or another function?
View 1 Replies View RelatedI have a spreadsheet that contains overtime information for employees. Basically, it has a list of names in column A, and a list of projects in the first row, similar to this:
Name
100323
100887
100987
100987
Sum
[Code]....
At the top of the page I've inserted a section similar to this:
Name
Project Number
# Of Hours
SMITH, John
100883
5
Where the name and project number are drop down data validation lists containing all of the project numbers and employees at our facility, and the # of hours section is just a manual entry.
I require a macro that, upon clicking a button, will verify that the name is in the list and insert a row if there isn't as well as verify the project is in the row and insert a column it isn't.. then it will add the number of hours i specified in the box. It would also be nice if the list of names would sort alphabetically and the project numbers would sort numerically from left to right after any additions. As well as clear the selections and # of hours I entered in the second table shown above once everything has been entered in to the main (first) table.
I'm pretty new to creating my own macros (even though im an engineer.. )
I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?
Sub InsertRows()
Dim lRow As Long
Dim r As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Application. ScreenUpdating = False
Columns("M").Insert
[m3] = "temp1"
Range("M4:M" & lRow) = "=LEFT(A4,5)"
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown
Next r
Columns("M:N").Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:= _
"H:HOME imcEXCELCOUPON_COUNT5_MAYREFORECAST.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.SelectedSheets.PrintOut copies:=1
End Sub
I am trying to develop a procedure that allows the user select a customer from a drop down list (using Data validation), thereby providing the user with a summary of customer information. That I can do. What I would like to do is allow the user to add new information in the summary section which is then appended to the customer record further down the sheet (see attached example). New data could only be added to the last two columns. The user would be required to press a form button to append the data.
The catch would be if the customer was selected again, the new data would also display in the summary section.
I need a macro that allows me to select a matrix of cells and converts it into a single column elsewhere, pasting the second column below the first and so on. Is it possible to select the matrix, hit a macro key and then paste it in my selected location?
View 11 Replies View Related