Move Data From One Cell In One Row Into Several Rows And Copy Rest Of Row
Mar 2, 2013
I publish a green building mag, and we run an enquiry system for readers on our website. This is the link: [URL] .......
The idea is that readers tell us some info about themselves, including what types of products/services they'd like to receive information about, and we pass their details on to advertisers offering those products/services.
When someone fills out a form, we get data in Excel on their enquiry. It's all in one row - name, contact details, and then the products/services they've enquired about are included in one cell, separated by commas.
So for instance, suppose a listing currently came in as follows (column headings in brackets:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation, solar panels, windows
Essentially I want it to run like this:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) solar panels
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) windows
View 14 Replies
ADVERTISEMENT
Aug 15, 2009
col1 col2 col3
row1 A 1 a1
row2 data data data
row3 data data data
row4 data data data
row5 A 2 a1
row6 A 3 a1
row7 B 1 a1
I'm trying to do is set up a VBA code that will take lines lines 2-4, copy the rows and then paste them in new rows every other row for the rest of the document, so that it appears as...
col1 col2 col3
row1 A 1 a1
row2 data data data
row3 data data data
row4 data data data
row5 A 2 a1
row6 data data data
row7 data data data..............................
View 4 Replies
View Related
Mar 13, 2009
i want to copy a cell value to rest of the cells in the same column upto the last value in that column has the value of "FIN"
View 2 Replies
View Related
Mar 28, 2014
I am trying to extract some data from a cell leaving the rest of the cell untouched.
I would like to extract C:UsersDanMusicIan MusicKaraokeKaraokeBob Marley & The Wailers - Jammin'.mp3 from the cell leaving Bob Marley & The Wailers - Jammin'.mp3 in the. I have to do this to 3000+ rows.
View 9 Replies
View Related
Mar 29, 2009
I have a cell with seven conditional formatting formula rules that I now want to copy to the rest of the column. I can copy/paste special/formats one cell at a time but if I try to to this with a group of cells, (or try using the format painter), it treats the formula references as absolute, even though they aren't shown as absolute in the rules manager. Am I missing something? Using 2007.
View 4 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
Aug 8, 2012
I am using the following formula, and dragging it down,
'=IF(SUMPRODUCT($G$2:$G6=G6)*($Q$2:$Q6=Q6)>1,"duplicate","")
the first row gives Value Error, and the rest rows always blank.
Formula Error - #VALUE!
View 6 Replies
View Related
Nov 1, 2007
I need a macro that can search text cells in column L for a space delimiter, then if the space is present, to split that cell into a row inserted below it, then copy the information in the rest of the row down. I'm not really up on my VBA.
View 13 Replies
View Related
Jul 11, 2006
I have part of this routine working correctly. It will go and find the value I need and move to the next column using the Offset method, but now what am I doing wrong to try and get the code to select to the end of the current column?
Sub Find_First()
Dim FindString As String
Dim rng As Range
'FindString = InputBox("Enter a Search value")
FindString = Worksheets("Template").Range("A26").value
If Trim(FindString) <> "" Then
With Sheets("Service Level Score Paste Sheet").Range("A:A")
Set rng = .find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
rng.Select...........................
View 8 Replies
View Related
Jun 18, 2014
I'm trying to adapt a macro written elsewhere on this forum for my purposes. I need to move 127 rows of data into one row. The macro written does exactly what I would like, except that it only works for 3 rows, rather than 127. How do I get this to work?Here's what I have so far:
[Code].....
View 3 Replies
View Related
Sep 6, 2006
I created a macro that is supposed to copy the contents of about 500 rows, and move them all up one row. One of the things that is contained in those rows are a bunch of links to Microsoft Word documents. When I try to move the 500 rows up manually (just copying, and pasting one row higher), it works fine. The link remains entact.
However, when I use the macro I recorded, the link no longer works. It looks like it is there (the text used to represent the link is there), but when you click on it, nothing is there. Here's my code....
View 9 Replies
View Related
Nov 17, 2006
I have attached a sample file and would like to have the expected format as shown in the attached. Would greatly appreciate if someone could show me the faster way of reformating the data instead of doing it manually.
View 3 Replies
View Related
Jul 17, 2014
formula to make data on same line.On converting data2 is above data 1. I insert a column on left . Need to use mouse right click shortcut delete manually shift cells left and then delete shift cells upto have both data on same line but should be in different cells and same sequence per attached ALSO TO REMOVE "/ -DASH HYPENS.
What I need is formula to move the data2 on same alignment and to delete the two empty rows between both data.
See annexed file for expected results.I have preference to have a formula much more.
View 7 Replies
View Related
Jun 28, 2007
I need to run the same database dump every day and find the differences. Both spreadsheets will have the same column headings, with Column A being the lookup value. Each spreadsheet will have many columns of data. I need to find the differences from Row 1 on spreadsheet A with Row 1 on spreadsheet B.
Example:
Spreadsheet 1, Column A value is 900026. Need to find 900026 in Column A on Spreadsheet 2. Compare all the data in each column to see what has changed. If anything has changed on Spreadsheet, copy the row from Spreadsheet 2 onto a new sheet and 'tag' which data has changed.
Also, again using Column A as the identifier, if there are rows on data on Spreadsheet 1 that do not appear on Spreadsheet 2, copy the entire row onto a new sheet and tag it as "Not on new datadump". And vice versa, is there are rows of data on Spreadsheet 2 that do not appear on Spreadsheet 1, copy the rows of data onta a new sheet and tag it as "Not on old datadump".
View 9 Replies
View Related
Jan 3, 2008
I ran across this macro in the posting " Move Adjacent Cell Into Rows, Same Column" and it is exactly what I need but when I try to use it I get a compile error on the Range("i1").Resize(n, UBound(b, 2)).Value = b. and several other problems. I also change "b" to "c" because this is where my data that I need to work with is located.
I have a similiar case much like the posting statement above where I have a spreadsheet on each row in cell "C" a EP CODE that have various information in it. My problem is that in the EP CODE column, it contains several EP CODE separated by semicolon (";") delimited . In order to serve my needs, each EP CODE instance needs to become a new row (record) whereas all of the information (except for the EP CODE) is the same and need to be repeated for each new row. Example: Before
Name Location EP code
WillisFremont 1;2;3;4;5;6
RaysSan Fran 7
SuNew York 8;9;10;11
After
namelocation EP code
WillisFremont 1
WillisFremont 2
WillisFremont 3......................
View 2 Replies
View Related
Apr 20, 2009
I have a range that i need to run a report on regularly. This range has sporadic amounts of data in it. one time there may be 60 rows of data, next time there may be 250 rows of data. i want the macro to select and copy only the rows that have data in them.
i currently have a macro that i just set a range A25:A400 so it goes down and copies all these rows so it covers me .... but it is causing me problems by copying all those empty rows.
The macro will need to start at A25 and go down from there A26,A27 etc select and copy only rows that have data and stop. i need the data copied to a seperate sheet named "Combined Scoreboard" inputting at A25.
View 9 Replies
View Related
Mar 23, 2014
Here's a sample sheet.
I receive spreadsheets with data sorted by column. There might be 500-600 records in approximately 1-8 categories, 14 columns wide. One column is used for the category. I would like to copy or move the data in each category to a new sheet within the file, so I can work with each category separately. I do this manually now, and have to do it multiple times for each file, as the data changes daily.
The category names are rarely the same from one batch to the next, so the routine will have to look at the categories and understand their names in order to put the data on a new sheet. Category names are alpha-numeric, like RA114, RC109.
This seems like a VB app, but I don't know VB! Is there a way to do this without VB? If not, is there a well-documented VB template or utility I might use to modify?
View 4 Replies
View Related
May 8, 2009
I would like to copy or move data from one worksheet to another automatically. I have one sheet (Data) with multiple pieces of data for specific individuals. I have several other sheets titled their last name. Can data for example from John Doe be copied to the sheet Doe and data for John Smith be copied to sheet Smith. In essence I would like the sheet to paste everything below John Doe to Doe's sheet and recognize the next name and past that into the next sheet and so on. It may be several different codes, Im ok with that.
One other item all cells do not have data I do need the blank cells copied as well at to not lose the formatting. There is also a blank 2 rows between each person.
If you need a copy of what the data looks like I can send it.
View 9 Replies
View Related
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
Feb 24, 2014
If the row contains a text like ABC in multiple columns in a sheet it has to copy the whole column from that sheet and paste special into another sheet.
View 2 Replies
View Related
Jul 23, 2007
We have a client that provided us with a text file that we imported into
Excel. Rather than have the entire record in one row, they have the record
in 4 rows.
What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows.
Below is an idea how this looks.
1JohnSmithstreetcitystateZIPaward 1
2JohnSmithstreetcitystateZIPaward 2
3JohnSmithstreetcitystateZIPaward 3
4JohnSmithstreetcitystateZIPaward 4
5BillJonesstreet2city2state2ZIP2award 1
6BillJonesstreet2city2state2ZIP2award 2
7BillJonesstreet2city2state2ZIP2award 3
8BillJonesstreet2city2state2ZIP2award 4
What we need for our data to work is:
1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4
2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4
View 9 Replies
View Related
Sep 19, 2013
I have a workbook with part data. Each worksheet is an individual part with its data on that sheet. I'm trying to move certain rows between two key words to a separate wb/ws for the top key word. Data looks something like this:
Supplier 1
row
row
row
Supplier 4
row
row
Supplier 15
row
row
row
As I said, each sheet could have only one supplier or a dozen. The number of rows between each supplier also vary. I was trying something like this to do what I need to do:
Code:
DistiStr = "Supplier 1, Supplier 2, Supplier 3, etc etc etc"
DistiArr = Split(DistiStr, ",")
intDistiMax = UBound(DistiArr)
Set VendorWB = Workbooks.Add(xlWBATWorksheet)
VendorWB.SaveAs Filename:="Vendors"
[Code] .......
I see where my logic problem is: It's looping through a3 - lastrow, but I'm going one row extra each time, so when it goes to the top of the loop again, I'm one row to far starting off. I originally did this because if there's only 1 supplier on the sheet, there won't be another supplier name below it to give me my range.
View 2 Replies
View Related
Nov 15, 2006
Moving Data And Deleting Blank Rows. I am a complete newbie to excel VBA and require assistance (if at all possible). I have data in the following format in an excel sheet (I have a lot more rows but give an example for two rows)... in 1 column but I wish to separate it (i.e. move data along onto the first row in other columns) and delete blank rows.
For Example:
Col1
AAAA
BBBB
CCCC
DDDD
EEEE
AAAA
BBBB
CCCC
DDDD...............................
View 9 Replies
View Related
Aug 11, 2014
I have a large spreadsheet which I have extracted the data I need, but I am now having a problem tiding it up.
To simplify. I have 2 columns, both containing data formatted as text, each column has some data, but on different rows.
I need to merge the 2 columns without overwriting the data on the second column, with blank data from the first column, and without the column shrinking or growing.
Here is a section of my spreadsheet that needs merging : Excel-1.PNG
I have tried copy > Paste Special > Skip Blanks, but it just copies the column to the other - no merging.
View 1 Replies
View Related
Dec 13, 2011
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
View 3 Replies
View Related
May 2, 2012
I'm working in Excel 2007 and need to move data from multiple rows to a single row if the ID matches.Below is sample data I would be working with. I want to move data from columns F-U to the right of the original data in the row above it. I would also like to delete the rows that had data moved.
GIDSurnameNameEmployee Number OriginalDate of birthGranting ARE
Employing ARECountry Employing ARECHCM Supplier IDVehicle
Investmt. shares / Awards at termination dateMatching Shares
at termination dateTermination
[Code]....
View 3 Replies
View Related
Feb 5, 2014
I have used a formula to achieve this before which seemed to work although now it seems to be replacing other data already in the worksheet so I am unsure of how to fix, thinking I should just start again.
The file in question is a tracker of activities, Column A (sheet1) is the Status and once the status is "Closed" I need to run a macro that copies all closed activities to Sheet5 at the bottom of previous closed activities. Once this is done I already have a macro to delete all "closed" activities from Sheet1 meaning that it is vital the new closed activities stack onto the bottom of previous closed activities.
The Column Headers start on Row 11 with activities running from Column 12.
View 1 Replies
View Related
Jul 7, 2009
One of the worksheets contains all of the data and the rest are empty. I need to do is move entire rows of from the main worksheet in to worksheets named the same as the value in column C of the row.
for example one row may have TEST in column C so i want to move it to the worksheet called TEST. The next row might have TESTING in column C so that would go to the worksheet called TESTING. and so on.
View 3 Replies
View Related
Jul 16, 2005
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?
here are some additional information:
The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)
The "status" column is at column 11.
View 8 Replies
View Related
May 23, 2006
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View 2 Replies
View Related
