Copy Selected Data From Multiple Rows Into Separate Column In One New Row
Dec 1, 2013
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
View 3 Replies
ADVERTISEMENT
Mar 13, 2014
I want to to copy selected columns of sales data into rows organized by salesperson. I have just started out with VBA and find that I cannot do it myself.
My original data are in the form of the following:
invoice_no
product
sales
qty
total
[Code] .....
I want to display the data in another sheet in the following format:
sales_a
sales_b
sales_c
sales_d
[Code] ...........
View 2 Replies
View Related
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
Oct 30, 2013
I'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
View 8 Replies
View Related
Feb 6, 2008
The following code inserts a row below the selected row, and copies the formula of the row above into it.
Dim Rw As Integer
Rw = ActiveCell.Row
Selection.Insert Shift:=xlDown
Rows("" & Rw - 1 & ":" & Rw - 1 & "").Copy
Rows("" & Rw & ":" & Rw & "").Paste
However, I need to alter this to work for inserting more than one row at a time. ie. the user selects 'x' number of rows and 'x' rows are inserted below (in the same way 'Insert Row' works in Excel) and the row above the selection is copied down.
View 2 Replies
View Related
Oct 27, 2013
I know how to separate data into different columns i.e. Marry Johnson into two separate columns; however, I have data in different rows that I need separated into different column. See screen cast [URL]
Using excel 2003 on windows 7 64 bit
View 3 Replies
View Related
Jul 11, 2012
Software: Excel 2010, Windows 7
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
View 8 Replies
View Related
Oct 18, 2006
I have a macro that loops through several workbooks and copies data to one common sheet. Works great with one flaw that I have not been able to resolve. I am using the End Property example below, this works great as long as there are more than one cell with data. With one cell having data this takes you to the bottom of the sheet.
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Need code to select from a specific cell to the last cell in that column with data where when the selected cell is the only one with data that would be the only cell selected.
View 2 Replies
View Related
Nov 18, 2013
I have a workbook with four tabs or four sheets.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
View 6 Replies
View Related
Aug 25, 2009
I have a large spreadsheet which I need to split into individual workbooks by reference to a particular column.
Rather then doing this manually and splitting the data out one at a time - I would like a macro to do this for me.
I am a novice excel user.
View 9 Replies
View Related
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
Feb 27, 2014
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
View 6 Replies
View Related
Jul 10, 2014
I have a challenge where in have a list of about 4000 rows and each cell have mutiple lines. ( i know there is a thread about this but that did not address my issue).
So my data is as follows - (I have attached teh sample file)
"Pari bom - GP and CFO 02547895452 pari@gmail.com
papa bom - GP and CFO 23365789541 papa@gmail.com
mama bom - CFO 2587412589 pari@gmail.com
nana bom - CFO 2587412589 nana@gmail.com
masi bom - CFO 2587412589 masi@gmail.com
"
I want to be in a position to seperte each line in exclusive rows and then seperate teh name, number and the email ... the later eing easier but cant get my head around seperating the cells.
View 4 Replies
View Related
Apr 3, 2007
I have a table of data 100's rows 10 cols. In col A is a name like USA - these refer to sheet names in the workbook. The other cols are numbers.
I need to write a macro to start at row A and go down the rows 1 at a time and copy and paste that row to the bottom of the sheet named in Col A.
Actually needs to insert at the bottom of a table in the USA col rather than just paste as there is other data further down.
View 9 Replies
View Related
Jul 27, 2006
Is there a way (with a macro?) to select all rows within a certain range (e.g. from row 3 to the end of the sheet) SEPARATELY, just as selecting them manually with ctrl+select?
Like in my example sheet;
I would use the AutoFilter on row 2 to show only the items with prices equal to 30. Now I want to select the entire rows (name, amount & price) of these two items, so that when I turn of the AutoFilter the two rows would still remain selected.
View 3 Replies
View Related
Apr 21, 2014
I'm new to Macros and below is my requirement.I need to split my data into multiple rows based on count and the first row should have the value but the other rows should have a value as zero.
Input
Count Value
1 400
2 101
3 300
4 450
Output
Count Value
1 400
2 101
20
3 300
3 0
3 0
4 450
4 0
4 0
4 0
View 4 Replies
View Related
May 13, 2013
I have one column of data, I would like to separate the ODD and Even row values into two separate column, such that the data will appear staggered.
ie. this is my data in column 1
1
2
3
4
5
6
7
8
would like to separate it into columns 2 and 3 as follows
1
.....2
3
.....4
5
.....6
7
.....8
I believe I need to use the ISODD, ISEVEN function somehow but can not figure it out.
View 4 Replies
View Related
Nov 30, 2006
I need to copy only user selected rows ( multiple) from one sheet to another. I see similar threads here but none that work with user selected rows.
View 4 Replies
View Related
Aug 5, 2009
See the attached xls. There are two worksheets contained therein, one with the original data, and the other showing how I'd like it organized.
I have rows of product data consisting of a product id followed by repeating attributes of quantity and associated price. I need to convert each product row to multiple rows of each quantity/price pair while preserving the id for each pair.
View 10 Replies
View Related
Apr 22, 2005
How do I create a macro that takes the 3 digits values in cells Q82:AJ86 and place them is column L, set code to start placing result in L38?
example
Q82=610
R82=611
S82=612
T82=613
Results
L38=610
L39=611
L40=612
L41=613
View 9 Replies
View Related
Feb 22, 2014
figure it out thus problem with macro,
before/sample data :
name
address
gender
activity
code
[Code]...
after macro :
george
g
t
jas
[Code]...
it's possible with macro??
View 3 Replies
View Related
Dec 23, 2008
I have a multiple rows of data, but one column which is K has data like:
1020
1020
1020
1020
1020
1030
1030
1030
1030
1050
1050
1050
What i want my macro to do, is whenever that number in column K changes to copy all the data in row A thru K to a new sheet.
View 9 Replies
View Related
Sep 6, 2008
I have set up the attached workbook to try to show the results that I need using the matching shaded cells in the worksheets.
The first part of the problem is that all data in columns A, B, C & H (Input1) needs to be copied to columns A, B, C & F in Input2 with the addition of a Zero value in columns D & E (sample data lines 1 - 3).
The second part of the problem is that all data in columns A, B, & F (Input2) where the cell in column C is equal to ZERO needs to be copied to columns A, B & C in Input 3 (sample data Lines 4 - 11).
It would also be useful to be able to copy data in columns A, B, & F (Input2) where the cell in column C is NOT equal to ZERO to columns A, B & C in Input 3 (sample data Lines 12 - 25). However, there will be often be more than value in column C (Input2) dictated by the date in Column A and number in Column B. The value in Column F (Input2) will always be the same for each of those rows, so it does not need to be duplicated in Column C (Input3).
This would have to be manually triggered by linking it to a button or menu item, not sure what would be the best option. As data is entered at various times of the week, the routine would either have to overwrite existing matching data or carry on from where it last finished!
View 9 Replies
View Related
May 1, 2009
enables the user to select a row to delete. However, I need to do more than that. I need to be able to copy all the rows until row 37 and move it one row up after the selected row is deleted. Below is the code
View 3 Replies
View Related
Mar 26, 2013
I have some selected cells in column A, they are not consecutive. I would like to copy these values and then copy the same area but from a different column...
The code I have here is only working when I have a consecutive selection.
Code:
Set rng = Selection
fr = rng.Cells(1, 1).Row
lr = rng(rng.Count).Row
rng.Copy
View 5 Replies
View Related
Dec 6, 2008
I know how to merge data in a CSV (see attachment) from 2 columns into a single cell using =A1&" | "&B2 so that A1 (Safety Products) plus B1 (Fire Protection) becomes Safety Products | Fire Protection in cell C1, but how can I do this across multiple rows so that each pair of names is combined in each row? The job I'm working with is a product CSV file that has 6370 lines so I don't want to do them one line at a time!
View 2 Replies
View Related
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
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
Sep 7, 2007
I found this treat but it didn't help me as i hoped. Move Data From One Column To Multiple Columns. I try to convert multiple datablocks to a new worksheet and make the adress blocks ready for further sorting and removing actions. The Excel I use is Dutch and i hope that i make my question clear. What I want:
copy each adressblock to another worksheet, put the initals as well as the beginning in a seperate column and delete the unnecessary comma's. Each adressblock is identified by a number in colom A. When a new number is shown, excel has to start a new line to fill. finally,if possible i like to extract the adress line into seperate colums of "street+numer", zip and place. Some adresblock have more than onder adres, if possible i want excel to ad these information in the 'extra' colums.
View 2 Replies
View Related
Jul 12, 2008
The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.
View 13 Replies
View Related
