I frequently have to work with tables set up like this:
A1 A2 A3
B1 B2 B3
C1 C2 C3
All I would like to do is to transpose them into one single column:
A1
A2
A3
B1
B2
B3
C1
C2
C3
I want to transpose multiple rows to single column and I'm dealing with huge data.
For example, I am working with data like this
A1 A2 A3
B1 B2 B3
C1 C2 C3
and I have to transpose to
A1
A2
A3
B1
B2
B3
C1
C2
C3
I'm using following vb code:
Sub ToOneColumn()
Dim i As Long, k As Long, j As Integer
Application.ScreenUpdating = False
Columns(1).Insert
[Code] ........
The script terminate without completing the job and shows error as "runtime error '1004': application -defined or object defined error".
I am trying to reformat the attached doc into one single column, It's a race listing of overseas marathons and I need it to look like this
Event Name
Event Date
City
Country
Entry fee
Website
in date order, one event after the other in a single column so I can flow it into a word doc ready to be designed. I need to apply in Excel (I am using 2007) to get this doc into the required format?!!
I've got an issue where I'm trying to transpose data from one column into several rows. I've been looking for a macro to help me out but can't seem to find a way to do this. Does anyone have any idea how I can do this?
Ideally, the macro would be written so it would find the data in the column and move it to a new sheet and then it would repeat the process throughout the document. The macro would know that the data is grouped b/c of the blanks found between the data set. So as the macro is running, once it hits a blank, it would then copy and transpose the data and continue. Does this make sense?
I've posted a sample of the info I'm working with.
I have a spreadsheet that calculates data in multiple columns (A:AN) and each column has 27 rows of data called "Sheet 1". As it stands, the spreadsheet goes out very far. On "Sheet 2" I would like for all of the data in "Sheet 1", columns A:AN to be "transposed" to "Sheet 2" in column A only. So, column A on "Sheet 2" would have the data from "Sheet 1" A1:A27, B1:B27, C1:C27, so on and so forth. I would like for the formatting to maintain the same order as well.
An example would be:
Sheet 1
Column A B
Row
1 Green Red
2 Blue Yellow
3 Black Cyan
4 Pink Magenta
[code]....
I have a single column of about 1500 points, and I need them to be separated into many rows of 20 numbers.
I have multiple issues:
1) The middle number of each row (10th and 11th) need to be repeated. So a transpose formula can not work all the way through the 20 numbers. Unless I transpose the first ten, then tranpose the second ten. Is there an easier way around this ?
2) The bigger issue is, I would like to write a formula that can manipulate the date to enter in the correct cell.
To illustrate I will use a set of ten numbers:
1
2
3
4
5
6
7
8
9
10
11
12
INTO
1
2
3
4
5
6
7
8
9
10
11
12
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...
I have 300 rows worth of data that looks similar to this, all organized in one column:
John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555
As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.
John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555
Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.
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.
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.
I have a column with (for example) 5 entries A1, A2, A3, and so on. I would like to pick up this data, tranpose it into a row, put it on the 2nd sheet, but (again for example) be specific about where i want it to go. I may want to have A1 at B2, A2 at E2, A3 at H2, etc
-A B C D E F G H
1x
2y
3z
4
5
becomes
-A B C D E F G H
1
2 -x - - y - - z
3
4
5
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies View RelatedI have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
I have a long spreadsheet, that has a format something like this: ...
View 6 Replies View RelatedIs there a way convert single cell with wrap text into multiple cells with the number of resulting rows based on the number of lines in the original cell. Due to the restrictions of my overall printable "document", I must use multiple cells of the same height. Any change of row height would throw off the desired final result.
As an example, when I start with my original cell in text wrap and set column width I get something like the following in cell F2:
ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME
MATERIAL)
This looks great, but it is all in one cell, and the four lines of wrapped text have obviously increased the row height which is bad.
I have tried using the LEFT and MID functions to force the text into different rows based on character length, but this is sort of messy since I am never sure what the input will be resulting in something like the following (this time in cells F2:F5) with spaces at the beginning of a line or breaks in the middle of words:
ASTM A743 UNS J93254
(SUPER AUSTENITIC)
(LONG LEAD TIME MATE
RIAL)
My ultimate goal would be to somehow format the original input data text so that regardless of the input, the text would be restricted to a certain column width, sending the remaining text to the next consecutive rows down, and maintain the integrity of the original. I can have as many intermediate cells, formatting as I go to get to this point.
Of course, I could manually enter the text in the different rows every time, but it would be nice to have a succession of formatted cells so that the desired end result would be automatic. Hopefully, the answer does not involve too much in the way of macro or any VBA code since I am a pitiful novice, and we are sort of under pressure to get this project under way.
I have a cash receipt worksheet and want to sum up the days cash. My problem is some days there are one receipt and some there are five. I have to keep hitting the sum button to make it include the proper range.
View 7 Replies View RelatedI have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
I have a database of addresses that has each address as several rows which I want to convert into a single row. In the source file there is a blank row between records. I have attached a sample file with two sheets in it. One is the "before" data format and the other sheet is the "after" example of how I would like it to come out. We have thousands of records to convert so I need something that can be automated instead of a massive cut and paste operation.
Here is the sample: sample conversion.xlsx
I have a HUGE spreadsheet (1,000,000 rows) that contains a series of reports. The first row of the report contains all of the report identification numbers. The next 20-50 rows contains the report details, all in column A. Then the report ends with a cell in column A containing "[report_end]" and then on the next row the next report begins.
I would like to compress the report details all into a single cell in column A so that there is only one row for each report, sort of like this:
AA | BB | CC | DD
xyz
123
[end_report]
AA | BB | CC | DD
xyz
123
[end_report]
becomes:
AA xyz 123 [end_report] | BB | CC | DD
AA xyz 123 [end_report] | BB | CC | DD
Macro to sort column data.
I have a spreadsheet in the following format (just a flavour of the format)
SD 1
SD2
SD3
1a
1b
1c
2a
[Code]...
Each row refers to a survey response, the first ten columns being standard demographics.
Following this each group of 5 columns for the next 100 columns represents a single response. What I want to do is convert each single row to multiple rows with the same first 10 columns and each single response on its own row like so:
SD1
SD2
SD3
a
b
c
a
[Code]...
I found an excellent similar example on this board here Convert a single row to multiple rows with the same A column value However I would like my macro to convert to rows even if the cells are blank So each existing row will generate 10 individual reponse rows regardless of data found).
I am interested to know how this would work and how i could modify the variables to acommodate 15 standard demographic fields and 20 columns per individual responses.
I'm looking for a way to sort dates from several columns into a new single column (perhaps multiple columns if the entry columns become too numerous). I've included an example. There are currently only 4 columns, but there may be as many as 20 in the future, each with 20 dates under each heading. Any blank cells would be eliminated. If I filled a blank with a new date, that date would be placed into the chronological column. So basically, this would take the date from several different categories and create a single calendar of events.
View 8 Replies View RelatedI 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].....
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 RelatedI 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.
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.
I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.
I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.
Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.
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.
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] ...
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
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] .......