Transpose Multiple Rows Of Data To Single Row?

Jul 24, 2014

I have a set of data that I need to change the "layout" of. I've had similar situations before, but this one is just killing me. Basically, my data is for item pricing. It is represented as

Item, QTY, Price
A,1,1.25
A,10,1.1
A,25,1
A,100,0.9
B,1,1.25
B,10,1.1
B,25,1
B,100,0.9
C,1,1.25
C,10,1.1
C,25,1
C,100,0.9
Item D,Qty1,P1
Item D,Q2,P2
Item D,Q3,P3
Item D,Q4,P4

However, I need it in the following format:

A,1,10,25,100,1.25,1.1,1,0.9
B,1,10,25,100,1.25,1.1,1,0.9
C,1,10,25,100,1.25,1.1,1,0.9
Item DQ1Q2Q3Q4P1P2P3P4

As a note: there is a maximum of 4 Price/QTY breaks, so the script can be hard coded for that. When I tried this, I had it looking at the Item column, finding out how many breaks there are for a specific item and then doing a loop to extract the qty and price to a single row in the format shown above. It worked for the first 2 items, but then the loop got throw off. I will see if I can reproduce the code for that.

View 4 Replies


ADVERTISEMENT

Transpose Rows To Single Column

Feb 23, 2012

I'm trying to do something which I can't manage with traditional formulas and a macro might be required.

I have the following table:

Code:

Header1Header2Header3Header4Header5
1.00 6.00 11.0016.0021.00
2.00 7.00 12.0017.0022.00
3.00 8.00 13.0018.0023.00
4.00 9.00 14.0019.0024.00
5.00 10.0015.0020.0025.00

What I would need to do is take all column values and transpose is to rows, copying the header for every set, like:

ColumnA ColumnB
Header1 1.00
Header2 6.00
Header3 11.00
Header4 16.00
Header5 21.00

[Code] ...

View 8 Replies View Related

Selective Transpose Multiple Rows Of Data To Columns

Jul 21, 2013

How to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:

study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5

[Code]....

I'd like to extract the data into a new table on another worksheet that looks like this:

Date
provider
study id
visit #
7/21/13
Test Name
10001

[Code]...

This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...

View 14 Replies View Related

Excel 2007 :: Transpose Data From Column A1 To Multiple Rows

Feb 26, 2014

I need to transpose data from Column A to Row 2 and down.

The data in column A is in sections of 19 rows and then a blank cell and another 19 rows of data contimuously, It is a dynamic range and can contain many thousands of Rows.

The data needs to be transposed from Column A to row 2 (row 1 has the head line for each column) so the 19 lines of data is now spread accross 19 columns in row 2 and the next section from column A is spread accross the 19 columns in row 3 and so on.

My data looks similar to the below. (Test Number 0001 starts in A1)

Test Number 0001

21-Feb-2014

Kettel

Office

Demo

[code]....

I use Excel 2007

View 6 Replies View Related

Transpose Multiple Columns For Single Row

Feb 12, 2014

Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7

After
XXXXXXXYYYYYYYZZZZZZZ1234567

[Code] .....

View 10 Replies View Related

Combine Data From Multiple Rows Into One Single Row?

Jun 8, 2013

I have a set of data with 5 columns. The first column is ID. Now I have the same ID recorded several times with different data (in the other 4 columns) against the ID. I want to concatenate the data with the same ID into the same row. The data has already been sorted by ID. If it is the first time this ID appears, record the whole 5 columns. If the ID appears again, then record only the 3rd, 4th and 5th column. When I run my code, I got error 'subscript out of range (Error 9)' . It seems that the array I use has only 5 columns. But I don't know how to modify this.

VB:
Sub Patient_Detail()
Dim n As Integer 'index of rows to record to
Dim i As Integer 'index of column to record from

[Code].....

View 7 Replies View Related

Moving Data From Multiple Rows To A Single One

Jun 29, 2014

Any macro capable of moving data from multiple row to a single one i have attach a sample file before and after ...

View 4 Replies View Related

Consolidate Multiple Rows Of Data Into Single Row

Jul 28, 2006

I imagine my goal could be achieved via some scripting code, but, alas, I don't do vbs. Here's the situation:
I have a spreadsheet generated by another office which lists individuals' names and information about participation in various programs. Each line lists name, address, program title and amount. The next line may be the same name, address with a different program and amount. Each person may have 4-5 entries. I want to make single entries for each individual, with colums showing the programs and amounts.
Current:
ID Name Address Program Amount
1 Bob home A 25
1 Bob home B 37
2 Dave home A 22
2 Dave home B 10
2 Dave home C 21

Need:
ID Name Address Program A Program B Program C
1 Bob home 25 37 0
2 Dave home 22 10 21

Hopefully this makes it a bit more clear. The only way I know how to accomplish this is the "old fashioned" long hand approach of cut & paste. There are several thousand individuals, with (currently) tens of thousands of rows.

View 9 Replies View Related

Organize Data From Multiple Rows Into Single Row

Jun 12, 2007

I would like to automate, due to how long it would take to manually organize the data myself. I have all of the raw data needed organized into 3 columns. The problem is that some of the related data is divided up into multiple rows, based on ID number. What I would like to do is take the related CPT codes and respective descriptions, from the same ID number, and place them on the same row. I am going to include some pics and even a small chunk of the source data. Here is a pic of the raw data:

And here is the first 3 already done:

The number of CPT codes per ID number range from 2 - 5 I believe, maybe 6. Not sure if this has any bearing on how it is done.

View 4 Replies View Related

Data From Multiple Rows Into Single Column

Oct 19, 2007

I am trying to take data from multiple rows and columns (matrix) and reformat it all to fit in a single column.

It would be great if I could select the cells I want to reformat into the single column as the number of rows and columns containing the original data is not always the same (ie 20x20, 21x35, 56x200) etc...

View 9 Replies View Related

Retrieving Multiple Rows Of Data From One Single Item?

Jan 3, 2014

retrieve the multiple rows of data from one specific item to another worksheet inside same workbook. below attachment is my sample workbook.

inside the workbook, eg.. please have a look on Sheet 3 (the record of the item and its description) will store on Sheet 2. From sheet 1, when user choose the item from drop down list, and click the button will direct to the retrieved result in sheet 2. my question is i cant retrieve the multiple rows of data exactly in the sheet 2. it only remains one item for one row of description.

and below attached picture is what i wish to achieve =)Capture.PNG

View 4 Replies View Related

Copy Multiple Rows Data To Single Column

Oct 29, 2008

transposing and sorting data into multiple columns.

Column 1 for example will have the Parent's Name. Column 2 will have the Children.

If Mark has 3 children, X Y and Z

Jim has two children, A and B

then I want Column 1 to display Mark and Jims Name and column two should display all the children

Here is how I want the data:

Column1 Column 2

Mark X
Mark Y
Mark Z
Jim A
Jim B

Please see attachment.

View 3 Replies View Related

Single Column With Multiple Rows Of Data - Compare Names

Apr 12, 2012

I have a single column with multiple rows of data like this: Afirst last, Bfirst last, Cfirst last (all in one cell) etc.

I want to type some names in one cell in the same format (Afirst last, Cfirst last, Bfirst last) but not order and then check if any of those names exist in the first column and count the results of matches.

So in this example I am looking to count Bfirst last and/or Cfirst last in every cell of column A. I need to use a formula.

View 2 Replies View Related

Transpose Data In Single Cell From Horizontal To Vertical

Oct 28, 2011

Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.

But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.

Is there any VBA function or public function to perform the this action?

Example:

From

A 1Dog; Lion; Parrot; Bee; Snail

To

A 7Dog8Lion9Parrot10Bee

11Snail

Like wise i will have to do the same action for the following

A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7

View 5 Replies View Related

Summarize Data From Columns In Multiple Sheets To A Single Datasheet Of Rows

Jun 19, 2014

I need a macro that will take hours by day (columns) by service (rows) per client (sheet) and summarize the data into one database of rows containing client, service, date, and hours. The "Summary of Charges" should only include service hours > zero. I am attaching a sample file. I have little to no experience with vba so I don't even know where to begin. I can copy code.

View 4 Replies View Related

VBA Excel To Transpose Multiple Rows And Multiple Column?

Jul 3, 2013

I was planning to transpose this sample data into the output designated below.

Original sample: (There is an empty row after every 4 entries)

Name: xxx
Email: xxx
Phone Number: xxx
Address: xxx

[Code].....

View 9 Replies View Related

Transpose Multiple Columns Into Rows?

Nov 20, 2013

I have following information. I need to transpose the columns into rows

C
10001

V
1000

V
1001

V
1002

C
10002

V
1001

V
1003

C
10003

V
1001

V
1003

V
1004

The expected result should be like
10001 1000 1001 1002
10002 1001 1003
10003 1001 1003 1004

View 4 Replies View Related

Transpose Multiple Columns To Rows Based On Criteria

Sep 26, 2008

I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:

Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00

I want it to look like the following:

Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00

I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.

View 11 Replies View Related

Convert/Transpose Multiple Groups Of Rows Across Columns

Mar 9, 2009

How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):

GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I

I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):

1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM

As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls

View 5 Replies View Related

Transpose Multiple Columns Into Rows Based On Repeats

Jul 9, 2009

I need to transpose a three column worksheet with thousands of rows containing repeats based on the value in Column A (between 2 and 11 consecutive repeats), into rows with no repeats, and the values from the repeated rows into new columns. Column A has a unique numeric value corresponding to the repeated rows. Column B has 1 of 11 values and Column C has 1 of 4 values.

The worksheet looks like this:

1 abc x
1 def y
2 ghi x
2 abc n
2 lmn x
2 def z
2 jkl y

I need to make it look like this:

1 abc x def y
2 ghi x abc n lmn x def z jkl y

I tried using the following code, but it dropped all the values from column C:

Sub kTest()
Dim a, i As Long, w(), k(), n As Long
Dim dic As Object, ws As Worksheet, s As String

Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Sheets("sheet1")
a = . Range("a2:b" & .Range("a" & Rows.Count).End(xlUp).Row)
End With

I am attaching a workbook " Book 1" that has the results from the above macro in the first worksheet "Final Report", the origninal data "orig data", and the format I need to get the data into "needed data".

View 5 Replies View Related

Transpose Multiple Columns To Rows Based On Match Via Macro Or Formula

Jul 10, 2008

I have two columns of data as follows:


10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......

View 9 Replies View Related

Data In Columns To Rows (not Transpose)

Nov 19, 2008

I am trying to clean up some data, which is organized horizantally AND in rows, as per the attached screenshot.

The fact that there is data horizontally across seven week days and in rows for the weeks of the year makes it impossible for me to use the simple TRANSPOSE feature, of which I am aware...

I have quite a couple of worksheets I need to apply this operation on, so manual work is the worst solution...

View 13 Replies View Related

Getting Data From Columns To Rows Without Using Transpose

Mar 26, 2009

I have an excel spreadsheet which contains data for customers and the last date they were seen at an appointment (along with various other bits of info).

The way the data is exported from my database package means that each customer has one row per appointment, i.e. row 1 contains john smith, 01/01/2009, row 2 contains john smith, 03/03/2009, row 3 contains john smith 01/04/2009, row 4 contains joe bloggs 12/02/2009, row 5 contains joe bloggs 27/03/2009. Some customers may have 4 or 5 appointments listed whereas another customer may only have one. I need to get the appointments all onto one row per customer so that I can calculate the number of days between appointments. I have tried to use transpose, but with 8000 rows it takes forever.

View 4 Replies View Related

Transpose Data From Rows To Cells?

Jun 26, 2014

Macro that transpose data from row to column.

Original Data:
Original Data.jpg

Requested output:
Output.JPG

The number of cells that include data in each row as well as number of rows is variable.

View 1 Replies View Related

Extract Data In Rows And Transpose To Columns?

Feb 13, 2014

I am struggling with an Excel Database, to make it "cleaner". Here is my problem. My database looks something like that:

Column A Column B Column C ... Column H
ID Age Date of Birth ... Language
00001 14 01/01/2000 ... English
00001 14 01/01/2000 ... French
00002 14 01/01/2000 ... English
00003 14 01/01/2000 ... French
00003 14 01/01/2000 ... German
00001 14 01/01/2000 ... Spanish

Basically, Columns B & C will never change for the same ID, but columns G, H and others contain data that is different from one row to the other. What i wwould like to do is having unique values in Rows, with Languages displayed in Columns. The database would look like this:

Column A Column B Column C ... Column H Column I Column J
ID Age Date of Birth ... Language 1 Language 2 Language 3
00001 14 01/01/2000 ... English French Spanish
00002 14 01/01/2000 ... English
00003 14 01/01/2000 ... French German

The challenge is that I would need this to be done with formulas only, not using any kind of code. Deleting duplicates manually after "cleaning" the database should'nt be a problem. I tried a formula found on this forum, but i couldn't manage to make it work. The formula looked like this:

{=IFERROR(INDEX($C$4:$C$8;SMALL(IF(FREQUENCY(MATCH($C$4:$C$8;$C$4:$C$8;0);MATCH($C$4:$C$8;$C$4:$C$8;0));ROW($C$4:$C$8)-ROW($C$4)+1);ROWS(C$16:C17)));"")}

View 2 Replies View Related

How To Transpose / Move Data From Columns To Rows

May 18, 2013

So, I have a column with data on rows as follows below. I need to arrange the data below in such a way that I have on first column the company name, second column the contact person and so on depending on what data is found (tel, e-mail, website).

The data is on rows and I have separated each company/group of data with a row between them. There are in total aprox 200 companies/groups of data that I need to arrange as explained above.

I have tried with transpose but I have to manually do it 200 times. I have tried with an indirect formula but the companies/groups of data do no have same amount of info/rows e.g. some lack the phone number or other data.

if there is a method to save the time and not arrange them manually.

Actinote
Contact: Toine Kets
Managing Director
Str. Tache Ionescu 3, Et. 5, Apt. 10

[Code]....

View 7 Replies View Related

Layout/Transpose Data Rows Into Columns

Jun 17, 2008

I have a following table:

A B C
1 City Name List
2 NY Peter 11; 23; 12; 11; 14
3 Toronto John 24; 25; 87

How can I, in a separate worksheet, create a following table?

A B C
1 City Name List
2 NY Peter 11
3 NY Peter 23
4 NY Peter 12
5 NY Peter 11
6 NY Peter 14
7 Toronto John 24
8 Toronto John 25
9 Toronto John 87

View 4 Replies View Related

Transpose Or Convert Rows Of Data Into Columns Of Data:

Feb 12, 2009

When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.

Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?

View 3 Replies View Related

Macro To Transpose Multiple Data In Cell Separated By Commas To Each Data In Column

Jul 15, 2014

I have a table in the format below with about 3500 rows

Column A
Column B

0001
All vehicles, Retirements

0002
All vehicles, Retirements, Addition

0003
All vehicles, Retirements, Addition, Deletion from Y

I would like to change it to the following format:

Column A
Column B

0001
All vehicles

0001
Retirements

0002
All vehicles

0002
Retirements

0002
Addition

0003
All vehicles

0003
Retirements

0003
Addition

0003
Deletion from Y

View 3 Replies View Related

How To Transpose Data From Many Coloum To Single Coloum

Dec 11, 2008

i have work sheet with 2 coloums with random data...

View 9 Replies View Related







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