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


ADVERTISEMENT

Converting Column Information Into Rows

Jan 5, 2013

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 Replies View Related

Converting Data In One Column To Multiple Rows?

Mar 5, 2012

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)

View 4 Replies View Related

Converting Different Length Rows Into Single Column

Jul 29, 2014

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.

View 4 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

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.

View 11 Replies View Related

Converting A Column Number Into A Column Letter

Mar 9, 2007

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?

View 5 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
BLANK ROW
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 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?

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....

View 5 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
A
B
C
D

[Code].....

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.

3,484
2,179
62.5
2,053
58.9
126
5.8
5.2
6.4

[Code] .......

View 7 Replies View Related

Converting Column Data To Row

Oct 8, 2008

I 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 Related

CONVERTING Data From One Column To Another

Dec 5, 2008

WE 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 Related

Converting Multiple Records (rows) Into One Record (row) Using A Unique Identifier

Sep 22, 2008

I 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:

View 3 Replies View Related

Converting A Matrix Into A Single Column

May 5, 2008

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

Converting Column Numbers To Letters

Jun 24, 2014

how to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.

Code:
Function colNumToChar(colNum) As String
Dim Num1, Num2, Num3 As Integer
Dim Let1, Let2, Let3 As String

[Code]....

View 9 Replies View Related

Converting Input Column Into Multiples

Jan 6, 2009

I know Excel quite well but this problem has me beaten and I have searched high and low to know avail.

I have an input column of departments and names which is ever-changing and currently I have hard-coded them into a spreadsheet to run a couple of drop down columns. However, I really want this to be automated and no longer hard-coded so that names are in the correct departments automatically.

View 9 Replies View Related

Converting Column Of Numbers To Corresponding Word

Mar 2, 2007

I have a file with a lot of numbers, but one column with single digits. These digits have a cooresponding model name. I want to convert them all to their model name. There are only 4 model names. I tried a nested IF statement, but felt this wasn't working properly or was not the proper formula choice.

A Find/Replace would work, but it picks up the single digits elsewhere in the sheet. I realize i could copy/paste my column to a new sheet and from there convert/paste, but I am trying to get better at Excel.

View 4 Replies View Related

Converting A Date Based Upon A Time In Column A..

Jan 15, 2009

I some assistance with converting a date based upon a time in column A. I have a number of dates in column A as follows:

01/13/2009 11:50 AM
01/05/2009 09:14 AM
01/12/2009 05:30 PM
01/12/2009 02:30 PM
01/12/2009 01:45 PM

I need to convert these values to another date base upon the values in column A.
If the time < 2:00 pm then leave date as is, if time > than 2:00 pm then add one day. The expected results in column B would be as follows

01/13/2009
01/05/2009
01/13/2009
01/13/2009
01/12/2009

View 2 Replies View Related

Converting Date Column To Text Formatting

Mar 9, 2009

I have column A set up to display dates in the following format: 03/04/2009. While the "look" is fine for the values in the column, I cannot get it to stay in that format when it is moved to a text file. It simply converts the display back to 39876.

I would like a macro that 1) looks at all values in column A and 2) converts each cell in the column to text format but leaves the contents so they are dates 03/04/2009 (mm,dd,yyyy).

View 2 Replies View Related

Converting Date Format In Formatted Column

Oct 9, 2013

I have a column that contains dates from our system that displays in "mmddyyyy" format (i.e., 10121999 or 8121998). How do I convert this to a 10/12/1999, 8/12/1998 formatted column?

View 1 Replies View Related

Converting A Column Of Text To Number With Macro

Nov 10, 2008

convert a column of text to number with macro.

View 9 Replies View Related

Filtering And Converting A Text Table To A Single Column List?

Dec 16, 2013

From a table like the one here, how can I generate a list (without spaces) of all the names only? Perhaps filtering by "Mr","Miss","Dr" or something? The result I'm after would be a column on a new sheet that ran:

Mr James
Mrs Milly
Dr McAllister
Miss Aujard
Mr Barker
Mrs Stanley

View 3 Replies View Related

Copying Only Rows With Data In First Column - Skipping Rows With Blank First Column

May 12, 2014

I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.

I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.

View 4 Replies View Related

Converting Horizontal Data To Vertical Data With Multiple Rows?

Sep 8, 2009

reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.

The attached example shows before and after.

View 6 Replies View Related

Compare Column B With All Rows In Column A If Match Place In Column C?

Aug 13, 2014

I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.

So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.

This would be repeated until all rows in B are complete.

Example
Col A: red
Col B: red
Col C: red

Col A: red
Col B: blue
Col C: not found

View 6 Replies View Related

Taking Text In A Long Column And Converting It Into Corresponding Text In Another?

Sep 27, 2013

I have two columns that read:

XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....

The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?

View 2 Replies View Related

Pulling Rows From Other Sheet - Skipping Over Rows If Column Is Blank

Jan 17, 2014

I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.

Sheet2 looks like this:

Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat

[Code]....

Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:

Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob

[Code]...

Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.

PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.

EDIT:

So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).

I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.

Here is my failed formula:

=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))

View 14 Replies View Related

1 Row Cell Covering 4 Rows But Next Column Divided In Separate Rows

Jun 5, 2014

I am working with an excel spreadsheet. There is a column for "name", and then the next column is for medical condition.

If the same patient has multiple (say, 4) conditions, the rows for "name" are combined into a single cell(let's say, a cell going from row 2 to row 5, witha a single name in the combined cell) , with the next column having four different cells adjacent to the single cell (cell 2 has "bleeding", cell 3 has "fever", cell 4 has "diarrhea", and cell 5 has "rash"). Some patients have only single conditions, single cells.

Will this mess up COUNTS and COUNTIF?

Is there a way to split the combined cell2-5 for ''name" into four cells all containing the same name?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved