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.
i think this will be simple for most of you. i need a macro that will move blocks of cells from one column into the succeding columns. say i have one column of 1000 values and i need to break that up into columns of 50.
I am trying to average different rows and columns within a larger block data set in a series. This data is from a 96-well absorbance microplate reader experiment. I only mention this to describe the raw data output I am dealing with.
Each set of data is in a 12 x 8 block with the next block below it with one blank row between. So I have a block of data contained between A1->L1->L8. The next block is contained between A10->L10->L17. This continues for a total of 28 blocks.
I want to take averages from rows or columns from each block and autofill them into a single column. So for example I'll need =Average(A1-C1) with =Average(A10-C10) below it and so on and so forth. My problem is that if I try to autofill from this already started column the third row will take the average of A2-C2 instead of A19-C19.
Is there a formula/script for me to skip the correct number of rows to the next data block?
I have attached my spreadsheet to this thread. I am using Excel 2013
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
I saw a post that needed a solution for moving blocks of cells in order to get fewer rows but more columns. The post is gone but I'd like to think my work wasn't in vain as the algorithm was more difficult than I thought. The problem was wanting make a 5000 X 4 grid into a 1700 X 12 grid, 55 rows and 4 columns at a time. This is sometimes done for visiblity or printing purposes to get more data going across instead of down. An example would be wanting to have more data accross on a print page and the page holds 55 lines of data.
For example, A56:D110 would be moved to E1:H55, A111:D165 to I1:L55, A166:D220 to A56:D110 etc. The code is somewhat generic so different blocks of rows and columns can be specified. To see this code work, enter the number 1 in columns A-D, then fill series -> step by one for a thousand or so rows. Then run the code.
Sub FewerRowsMoreColumns() 'Rearranges blocks of cells so the end result is more columns and fewer rows Dim RowStop As Long Dim ColStop As Integer Dim RowStep As Long Dim ColStep As Integer Dim RowOffset As Long Dim rw As Long Dim col As Integer
ColStop = 12 'Enter the last column number you want the cells moved to RowStep = 55 'Enter the number of rows you want to move at one time ColStep = 4 'Enter the number of data columns you are starting with.....................
I have a worksheet that collects information from another using complex formulas.
In column A I have codes i.e. PRD001A, PRD001B etc. A block of codes starts at A and can go up to Z. i.e. PRD001 could start WITH PRD001A and finish with PRD001N then there is always a PRD001W and PRD001Z to finish with.
PRD002A would follow PRD001Z and might only have A, B, W & Z codes before PRD003A.
In column K is the sum of columns D:J.
What I want is a macro that will look at column A & K and if all OR some of the codes for say PRD001 are >0 don't delete any of the rows for that group of codes.
Then look at column A & K and if all of the codes for say PRD002 =0 delete all of the rows for that group of codes.
Column A | Column B | Column C Los Angeles | Fire Dept | 3 Los Angeles | Health Services | 12 New York | Fire Dept | 8 New York | Health Services | 22 New York | Internal Services | 100 New York | Public Works | 7 Chicago | Health Services | 15 Chicago | Public Works | 56 Chicago | Social Services | 4
And I am trying to make it look like this:
Fire Dept Health Services Internal Services Public Works Social Services
I am working on the attached price proposal and need my user to be able to enter a number into column E indicating that that row should be printed on the quote. The 'quote' can be new sheet or another area of current sheet that is formatted to look like letterhead.
What I'd like to do is to have the block of rows starting from "Agent:" and ending with "Total" copied into a separate worksheet and then have the worksheet named using the agent's name. I have highlighted the data in yellow in the attached reference file. I would like to have this done for all 58 agents. I could copy and paste manually but that wouldn't be the smartest way to go about this. I figure I probably need a looping structure for this but I am not yet proficient enough to figure this out quickly. I am supposed to have this done today.
Edit: The last part of this would be to have blocks of rows from the "sections" worksheet which use identical agent names also pasted below the data from the "questions" worksheet. I have attached the workbook I am working on now cutting and pasting manually.
I am trying to Sum "uneven blocks of rows" using the Sum Function.This works OK for the 1st.Block,but then the selection of cells moves down the same number of rows as the 1st Block.
I am attaching a sample file which best explains what I am trying to achieve.
I have used some code that Kris gave me a couple of weeks ago and changed it to insert a row between each block of information. Have I got it right?
Sub InsertRows() Dim lRow As Long Dim r As Long lRow = Range("A" & Rows.Count).End(xlUp).Row Application. ScreenUpdating = False Columns("M").Insert [m3] = "temp1" Range("M4:M" & lRow) = "=LEFT(A4,5)" For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(r, 13) <> Cells(r - 1, 13) Then Rows(r).Insert Shift:=xlDown Next r Columns("M:N").Delete Application.ScreenUpdating = True
This must be a simple fix. But I can't seem to get it. I need to test the data of each cell in column B against each other cell in column B. If the data is equal then move it under the first cell of the test.
1 2 3 2 2 3 4
Group the 2's and 3's in order. Finally, then create borders around each group of numbers.
I have sheet1 with four columns. Team, Issue, Discussion and Recommendation. Discussion and Recommendation headings were placed in column B along with the data in the file I was sent. I need to get it back into table format to eventually load into Access. What I'm trying to do is to loop reading down column B. When I find the word “Discussion” (exact format) I need to drop down one row below the word, cut whatever is in the row, move one column to the right, Column C, and two rows up and paste. For example, cell B3 has “Discussion”. I need to drop to B4, cut whatever is in the cell and move to C2 and paste. I also need the same to happen for Recommendation. Find the word, drop down one row, cut whatever is in the cell, move two columns to the right and up four rows. I would like to delete the rows I moved data from, but will do that by hand if it’s too hard to do in a macro.
An example of my data: TeamText Design4. Reuse existing code to extent possible. DesignDiscussion DesignNo discussion DesignRecommendation DesignEstablish a baseline and work to the plan. Design5. Delete old code DesignDiscussion DesignNo discussion DesignRecommendation DesignNo recommendation Design6. Plan to work without full details. DesignDiscussion DesignIncremental design DesignRecommendation DesignWork on smaller details first
I need to create a repeatable macro/module to help me prepare a spreadsheet for import into Microsoft Access. In each row of the spreadsheet is various information including Order Number, Model, and Serial Number. The problem lies in the Serial Number column, which often contains several Serial Numbers delimited by commas. In order to serve my needs in Access, each Serial Number instance needs to become a new row ( record) wherein all of the information (except for the Serial Number) is the same. It is crucially important that all of the serial numbers are in the same column. (I tried using text to columns as a starting-off point, but seemed to dead end).
I'm working with transcribed comments from patient satisfaction surveys. The goal is to produce a single Excel file that contains all of the positive comments from source files where physicians are mentioned by name. Our vendor notes this by preceding any person's name with an asterisk (e.g., *Dr. Smith or *Doctor Smith"). I've attached a sample source file (Ambulatory.xls), so you can see what I'm starting with (I've deleted the identifying data in columns B, C, and D).
I'm looking for the appropriate VBA code to cut and paste the row in one file (in this case, Ambulatory.xls) to a separate Excel file (Weekly Physician Comment Report.xls) IF the cell contents in column G of that row contain either of the following text pieces:
*Dr
*Doctor
Each week's file will obviously have an unknown number of total rows - and will contain lots of positive comments that DON'T mention a physician by name. In the file I've attached here, for example, there is only one such comment (highlighted in yellow). I simply want to cut and paste the rows with physician names into the new file. If you can provide me with the code to do this for this example file, I can repeat the process for my other files (there are 6 source files in all).
I am trying to have rows moved from (Schedule) to (Complete) in the attached workbook. The criteria to have the row moved would be the user-selected "Status" list changing to "COMPLETE" in Column G.
I would like to have the row cut and deleted from Sheet4 (Schedule) once "COMPLETE" is selected from the list in Column G with rows below moving up. The row would then be inserted into Sheet2 (Complete) at the top of the list (Row 7) and rows would move down below the new entry. I would also like to have the formatting remain uniform (every other row fill)
I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns
Like "Service Order ID" is 1 column in set of data ,it move to second column of top row
Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,
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:
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....
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.
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I have a sheet with about 1000 rows of data filling 40 columns. the data is the same in column 1 for a random number of rows between 2 and 40. ie as here...
2 2 2 2 33 33 33 4 4 4 4 4 4
etc. Is it possible with a macro to divide the sheet into blocks of 50 rows each with just the data before it changes to a different no.
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.
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".
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......................
I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.
In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.
area (P) phone (Q) primary phone (R) col S col T col U
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
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.
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:
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:
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.
