I am looking to remove all blanks from a table I have and move all the data left. Right now the data is by date, and some dates are blank for some players (it is a basketball sheet) and filled for others, but I want to see it by game. So I have what is in the first table below, and I want it to look like what is in the second.
2-22-2014
2-21-2014
2-20-2014
2-19-2014
2-18-2014
[code].....
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
I have to download a report through SAP and have written some pretty basic stuff to automate the formatting process, however, i still have to manually remove blank rows. I would like to be able to add the removal of theses rows as part of the code, one of the main problems that i see is that i run this report every week and the empty rows are not necessarilly in the order every time.
View 4 Replies View RelatedI have a lists of e-mail address (up to 15 rows long) in adjacent columns.
At times one or more addresses need to be removed. After removal I need the list to 'bunch up' so that the space caused by the now blank cell is removed.
At the moment I am doing this with vba as follows:
Sub Clear_Blanks()
With Range("e13:e27")
. AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
.Copy Range("e35")
.AutoFilter
.ClearContents
End With
Range("e35:e49").Cut Range("e13")
End Sub...
how to set up a formula that would count the number of numbers in a column, only if the cells before those cells are blank. In plain English: total the number of occurences in each column, provided the value in cellrow is the first occurence in that row starting at column A. E.G
A
B
C
D
John
x
x
Dave
x
x
Kyle
x
x
Bob
x
x
Count would return under Column A, 1; under Column B, 1; (because row John has a value in AJohn it is ignored), under Column C, 2; under Column D, 0.
How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop.
View 5 Replies View RelatedI am using Data Validation and I don't want the user to go past a cell without putting in an entry. When I leave the box (ignore blank) unchecked it doesn't do anything after I have protected the document.
I have a macro that creates mutiple, though varing numbers of, columns. Once the columns are created, a blank column is set up to the right of them. When the data is ideal, only one cell in each row created by the populated columns will have data in it. So lets say that in this example, the macro created 23 columns of data. A1 is blank, B1 is Widget, and C1 through the end are also blank. I need a macro that will look at the rows and find the populated column and copy that string of data to the blank 24th column (to the right) created after the poulated columns. So X1 (the 24th column) will have Widget placed in it.
Now I need it to do one other thing. In another example, let say there are 5 columns created this time. In the third row, there is data in A3 and D3. If they are the equal to each other (A3 value being 143256 and D3 value being 143256), then I need the 6th column created to have 143256 placed in it. However, if the data differs ( A3 value being 143255 and D3 value being 143288) then I need the word "Scrap" to appear in the sixth column.
I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.
I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.
If patient “John R. Smith”, for example, is entered in the database as”
“Smith” “John” “R “
and also as
“Smith” “<space>John” “R”
then the database won’t recognize them as the same name when I search for John Smith’s data. Right?
If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.
This is my first post on this forum and I am an Excel novice. I have a worksheet with lots of blank cells and I need to shift all the cells with data all the way to the left (to column A). I cannot get the sort or filter functions to do this for me, and I've never used macros but it appears they might be useful for my problem. I'd like to find an automatic process to move the data left rather than drag and drop hundreds of times. I'm attaching the complete xls file for your viewing.
View 5 Replies View RelatedI have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.
Example data
A..................B
15
50
25
20................55
Blank............55
30................31.25
35................27.5
blank............27.5
blank............27.5
15................25
10................22.5
15................18.75
40................20
blank.............20
I have a long list of values in column F of my worksheet. These values appear in random rows for example: cell F3 is 27, cell F9 is 7, cell F13 is 27, cell F20 is 9 ... The data is not evenly spaced any specific number of rows apart, but there are spaces (rows which are blank in column F).
I would like to put all of these values in column G but without any blank cells, and keep the order the same as well. So using the above example, Id like a way to make cell G1 read 27, G2 read 7, G3 read 27, G4 read 9.
Column F could be a very long column im not sure exactly how long as the data is being put in periodically. Some sort of formula that I could just drag down that would read the values from column F and put them in column G with no places would be ideal.
Lets say I have a column, and i have some dates in that column. between each date entry are blank cells, an undetermined ammount.
ie:
------------
column1
------------
(blank)
1/1/2007
(blank)
(blank)
(blank)
2/5/2007
(blank)
(blank)
3/7/2007
(blank)
... etc ...
I would like some type of array formula, that could
agegrate that whole column into a new column, removing the blank cells.
so the new column would be:
--------
Column2
--------
1/1/2007
2/5/2007
3/7/2007
I was thinking something like... but this doesnt get rid of the blanks...
(this is using google spreadsheet, but ARRAYFORMULA, is the same as hitting "CTRL-ENTER" - in excel.
=ARRAYFORMULA(if(isnumber(A9:A17),A9:A17,0))
I have a spreadsheet which is used by users unfamiliar with Excel. They are using the filter to select records, however when this is used some records appear which have no entry in the cells of that column. Can I overcome this? There is no data in the blank cells, other than a data validation drop down.
View 1 Replies View RelatedI'm needing some code that will remove an open quotation mark from the numerical value found in column A. This will be for every row with a numercal value.
example problem: "360015
example answer: 360015
All numbers will be 6 digits
And I've got another issue with numerals in column C. To the left side of the Cell there maybe a Number or Letter. To The Right side of the cell
there will be a single or double digit number. Both will be separted by some space (6-8 spaces). As in the examples below:
(again what you see on each line is in one cell in col. C.
R 59
E 6
0 4
I need the value on the left side of the cell to be moved concenate/merge with the values in column B.
There's will be approx 2000 rows that will need to be checked for this occurrance. Since there's so many rows,
I'd really would like to use VBA and not formulas to accomplish this.
I have a spreadsheet which contains 39 columns with a header row. The number of rows varies per spreadsheet. I'm running into a problem with highlighting(painting yellow) cells. Really the problem is selecting the cells to be highlighted. The macro is skipping some of my code completely and in other cases leaving some cells unhighlighted.
1. All blank cells in row AG should be painted yellow.
2. If column AF contains a cell with number 900 or greater, then the cell in AG (same row) should NOT be painted yellow.
3. All blank cells in rows AC and G should be painted yellow. Some spreadsheets may not contain blank cells.
Problem with the VB is not performing the code after the first command. It will highlight MOST of the cells in AG but for some reason it is leaving a few blank ones unhighlighted. The blank ones are usually at the bottom of the rows. I have no idea why it would miss these.
I believe the "ucase" tag is incorrect since its looking for a number but I am not sure what to put in its place and if I use something else should I dim it as ""?
I've attached an excel spreadsheet which contains a sample to this problem. I've also attached what it should look like if my code worked.
I have data in some of the cells within range A26:A39
These cells are populated via an IF function on another worksheet. Even though the cells appear blank (as in the value returned is ""), there is a formula in these cells. I think it's called formula blank?
I am looking for a way to copy the data from the cells within the range which are not blank (ie: not = "") and paste this data elsewhere on the sheet in a list with no blank spaces in between.
I anticipate that there will be 4 non blank cells within this range.
Ideally I would have data from the nonblank cells copied and pasted to cells
A40
A41
A42
A43
Am i able to remove blank spaces from cells retrospectively?
i.e. i have 1000 cells with names appearing in a cell thus " john smith" i want it to be "john smith".
so just removing the leading blank space only.
I'm looking for a formula that pulls the text from a cell unti it hits a space. I'm using the formula below but keep getting #VALUE results. B1: =LEFT(A1,FIND(",",A1,1)). I know it's not that hard but can't figure it out.
View 4 Replies View RelatedEach month I get a report that is formatted by the customers and this sheet never changes. The problem is that the master sheet my job uses is not formatted this way. The master workbook has several sheets that breaks down the data from the monthly sheet. Instead of having to enter the data manually I'm looking for a way to export the data from the monthly sheets into the master workbook. As I stated the sheets are all the same but come from different providers and all contain the same amount/style of data.
View 1 Replies View RelatedAlthough Copy + Paste and Copy to New Row are fairly common questions I find, I am having trouble getting it to fit to what I want.
In the attached example, I have three sheets: 'TBC', 'Bland Anna' and 'Coates Beth'.
I would like to search in columns E and F (no dates will be present until row 5, but as rows 1 through 4 will not contain dates, it may be easier/involve less code to just search the columns) for a specific month, e.g. for this example search for any dates that are in January.
I would then like to copy the data in columns D through G that correlate to said date, and then paste onto sheet TBC on the next available row, not to overwrite.
The macro in question would need to go through every sheet and perform the above action, although there are only 2 sheets in this example, the live workbook will likely contain 100+ sheets containing employee sick leave.
Furthermore, and this is something I'm not sure would be feasible, everytime it copies data, I need it to pull across the name of the individual. This part is slightly more complicated as the names on the sick leave sheets are based at E2 and G2, whereas it needs to copy the names to B and C. Moving the name information from E2 and G2 would make sense to make copying easier, there are other sheets that I have excluded as they are not relevant here, but which are used elsewhere.
If this is not possible, I will have to look into reworking the layout of the information to make it feasible.
month copy test.xlsm
I need to protect my worksheet and only allow users to edit certain ranges. I am allowing the use of autofilter which I can select when I apply protection. However, I am now aware I cannot sort protected cells..which is essential. I basically need to be able to sort a column titled 'surname'. The worksheet is a record of pupils attainment in my class.
Now, I have a macro which seems to do the job: ....
I have Main worksheet (sheet 9) and some codes in Sheet 10 which I use as a source for data validation in Sheet 9. So far I have a macro which looks for changes in cells and, when someone chooses a code from the list the macro adds a comment to the cell and populates it with an explanation of the code. The explanations are also in the code worksheet (Sheet 10). It works really nicely but I cannot work out how to remove the comment if the user clears the value in the cell.
My Macro thus far is:
[Code] .....
I have a texfile that populates a textbox on a userform. I would like to remove all blank lines in the string including those at the end if they exist, before populating the textbox. I'm reading the entire file at once into the string, not line by line.
Is there any way to edit the string called Text to remove the blank lines before populating the textbox? I'm looking for 2 carriage return characters in a row, and if so then remove one of them, but I don't know how to code that. This is in the userform activate section. If I read the textfile line by line, I don't know how to populate the textbox that way and remove the blank lines.
Is there a quick way to remove blank rows quickly. I have a spreadsheet with over 8500 rows but some are blank.
View 8 Replies View RelatedI have a workbook containing several worksheets. I use one worksheet to collate information from the others. I do this by referencing the relevant cells I need from the other worksheets with the '=' command.
When this displays it shows as a '0' if the original cell is blank. Is it possible for this to show as a blank unless there is any data. I have tried the ISERROR function but it still leaves the entry as a '0'.
I have a large spreedsheet like below and want to move "the "anytown, US" to the right of "1 Main Street" and delete blank rows. [I know a few ways to do the latter]
John Smith1 Main StreetAnytown, USJohn Smith1 Main StreetAnytown, US
my question is how to return blank cell if i have no data inputted in left cell.. or if AH9 is blank or no data inputted AI10 will be blank using this formula.. =IF(AH9<75,"A",IF(AH9<82,"B",IF(AH9<87,"C",IF(AH9<92,"D","E"))))
View 2 Replies View RelatedI am running into a seemingly random event upon executing a rather long program in Excel VBA: a square area turns gray in the left of the screen in Excel, making invisible everything under it, and affecting every sheet and workbook open. The only way to get rid of it, so far, has been to restart Excel. I thought it pertained to a faulty Office installation (XP/2002), but then it also happened on another machine (with Excel 2003) where I installed the application.
View 4 Replies View RelatedI have created a dynamic chart but I want the axis to only reflect data greater than 0. Can the axis' of a chart also be dynamic with the dynamic chart?
Upon request...Will email spreadsheet if need to review.