I have data I extract out of a datasource. I run a few excel scripts upon it and export the content to another spreadsheet. The column of data (I have columns a - h) i'm concerned with is H. The totals in that column regardless of the length of the column (could be 10 rows of data or 10,000) I need to delete the top 10% and bottom 10% rows completely of those numbers in H and leave the remaining 80% in tact.
View 2 RepliesI have a 23 sheets in a workbook and in each sheet i have around 1200 to 2500 data.
What i want is : from active sheet i want to open another sheet which is located at my desktop and should select column A, H & I all data (whether it is blank at last not to worry) and paste it into my active sheet in column A,B & C then i also have formula to calculate saving per shoe in column D,E & F.
For column D,E & F in active sheet the formula should fill down until the column A gets blank (For example : in column A,B & C under active sheet takes 1230 products so formula of the column D,E & F will end at 1230 row)
I have tried to record macro for the same but this is only for the restricted rows i.e. no. of row so I post it now.
I have many rows of data 6 columns wide. I want to be able to enter data into a specific section, then run a macro to "cut-and-paste" that data onto the bottom of my existing data (with one empty spacer row between the new and existing data)
Here's what I have so far:
[Code] ....
Basically the part I need working on is changing [ Range("A101:F130").Value ] to be dynamic. For the code to determine the last row of data, move 2 rows down, and paste the block there.
Give me the code to attached one column of data to the bottom of another?
ie the next available cell
so column a has 250 lines of data the data from another column will then go diectly into 251
thats not the number i want but the next available
I use the code below to copy formulas to the bottom of the data in a spreadsheet so I know how to do this with a formula.
I have a need to do the same but with a text string that I define.
No conditions, just normally need to copy the text from the cell 2 of a column down to the last row where data is in any column (Usually being col A).
Code:
Dim Last_Row As Long
Application.ScreenUpdating = False
Last_Row = Range("A" & Rows.Count).End(xlUp).Row
Range("S2:S" & Last_Row).Formula = "=b9/a9"
Application.ScreenUpdating = True
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter.
100%
75%
23%
39%
50%
N/A
100%
5%
8 items - the lowest 2 (the bottom quarter) should "stand out" - 5% and 23%
I figured two options:
1.) Conditional Formatting - don't know what the formula would be to use
2.) If statement - preferred method.
I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes"
I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction.
And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
The following code is doing what I want it to do in part, its copying the data in range k3 to q3 to the next empty row in columns a to f
It works when I change any cell in column K (11), I want to change to only on a change in K7 or K8? As an afterthought if I'm dealing with the same sheet do I need to make reference to sheet2?
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextRow As Long
If Target.Column = 11 And Target.Cells.Count = 1 Then
[Code].....
I have a multi tabbed page and the page i want to sort (2) is looking up the 1st page and creating one column list from cell a1 to a280 and it includes a number of zero's from the look up pages because that is the correct result. I already have it sorting but I want it to sort and leave the zeros at the bottom (they are currently at the top) of the sheet so I can just print the info I need.
Can I create this in sort or do I need a macro and how can i write it as I am very green on VB.
I have a s/s encompassing over 350,000 rows.
Data consists of a series of ranges from columns A:N and rows varying in number between 3 and 30.
There are two blank lines between each range of data.
Each range is (with exception) numerically ordered down column H (e.g, 1, 2, 3, etc).
Problem: there are around 1100 occasions when a range contains a row of data which is to be disqualified from consideration. On these occasions the data always appears in the top row of the range and is identifiable in column H by the nine codes 111, 222, 333, 444, 555, 666, 777, 888, and 999).
I wish to find a macro which will :
1. where the top row of a range in column H is one of the codes 111 to 999, transfer that row of data to the bottom of the range.
2. delete the now empty top row from which the transfer was made from.
3. insert a new blank row at the bottom of the range below the row which the data was transferred to (in order to maintain the two blank rows above and below each range).
I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch
S&C Group
77770000583-DOWNERS GROVE BRANCH
MWBF2
77770000585-CHICAGO RIVER BRANCH
MWBF1
77770000587-WEST TOWN BRANCH
MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
I'm looking for a simple piece of code I can use to manipulate the data on the attached workbook. Firstly, I need to be able to sort the data by column O ("Balance"). I then need the code to extract all the information for the top 10 and bottom 10 rows and paste the info into a new worksheet. The code will need to account for the fact the number of rows might increase/decrease with this report.
View 2 Replies View RelatedI 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.
I was wondering if it is possible to hide a column(s) but still have data in that column be visible at the bottom of the spreadsheet. For example, if I hide column G can it only hide the column from rows 1 - 50 and then the column will appear after row 50?
View 3 Replies View RelatedIs there way I can copy the BOTTOM cell amount in a specific column, to another specific cell in another workbook?
I update a worksheet with a new row of data each day. I want to copy only the last (bottom) entry from one column over to a worksheet in another workbook. I'm hoping I can just put something in the cell I want to copy to, to reference the bottom cell with data I'm copying from.
We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.
View 4 Replies View RelatedI have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
If I run this vba it removes the external data connection to the pivot table:
ActiveWorkbook.Connections("EXAMPLE").Delete
When I attempt to drill down into the pivot table it states the data connection was removed.
Is there a way to remove the data connection but preserve the data in the pivot cache?
It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.
I Want to Delete Data.
Filter I Column And Delted "N" ....
I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.
When I click data --> connections its empty.
I have data like this....
6
4
5
2
.......in the first column A1:A4
I would like to add and remove data to the column so I have data that would look like this...
6
4
5
2
1
9
.......in the first column A1:A6
I would like to find the average on differing numbers of data without changing the formula. So how can I tell the software that my average has now changed from 4 data to 6?
Trying to select all cells below the last row of data based on column B and remove borders.
So far I have this.
[Code] .....
Suppose I have data in a range that was imported from "somewhere". Some of the data is preceded by team rankings in parenthesis...like so:
Alabama
(21) Boston College
Notre Dame
(10) Oklahoma
Texas
Florida State
(2) Florida State
I want to strip the data of the team rankings, (as well as any brackets and spaces before the team name). Is there a macro I could write to do this?
I have a list of Surnames in Column D, e.g. ANDERSON, BROWN, COOPER, but for some reason, who ever input the data, decided to include the salutation after that Surname, in brackets, where it was available, e.g. ADAMS (Miss), BUTTON (Mr & Mrs), COX (Dr).
So I'm trtying to remove everything within the brackets (if there is anything) and place it in Column C. I will then Replace the brackets and Trim the contents in Column D. Here is what I want the code to do
I need to remove any attribute that doesn't have a spec in it and keep the attribute that does have a spec in it.
Width=5/8 in|Thickness=0.0150 in|Length=100 ft|Tension Cap.=750 lb [Max]|Inside Diam=|Material=Austenitic Stainless
Steel|Coating=|Color=|Locking Type=Valuclips?|Used With=|Type=|Quantity=|Wt.=3.60 lb
I need to remove for example Inside Diam=| since it doesn't have a spec about the product
Width=5/8 in| will remain since it does have a spec about the product
so after this is done it should look like this
Width=5/8 in|Thickness=0.0150 in|Length=100 ft|Tension Cap.=750 lb [Max]|Material=Austenitic Stainless Steel|Locking Type=Valuclips
|Wt.=3.60 lb
by selecting any cell in sheet 2 column G it will then copy 4 cells to the left paste them to sheet 3.
Then delete the selected row in sheet 2
i have data exported as CSV from in house system, the problem is that data is separted by commas therefore some of cells have split, i need VBA to remove the commas and bring my data back into correct format. Below is a sample of what the data looks like, real data is 5000 rows of data
PFOLIO A/C CODE ACCT NAME CUR CODE DESCRIPTN NARRATIVE DATE ENTRY DATE CASH VALUE De --------------------------------------------------------------------------------------------------------------------------------------------ACA001AUDCUST JP MorganAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N
Every week, I receive a flat file with three columns in this format:
2008-11-03 15:26:53, 'jon@abc.com', 'MYSPACE'
2008-11-03 15:14:02, 'jenroy@abc.com', 'FACEBOOK'
I use "text to columns" in excel to separate this into three columns.
But I can't find a fast way to eliminate those single quotes that are around the email address and the last column.
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
I am trying to create a summary sheet that updates when input data is added.
I'm still learning many parts of excel and I'm unsure why the SUMIF function on occasion leaves duplicate data in.
I can live with the fact that I will need to correct the input data and am willing to just paste in corrected data when it has been validated via the data validation tab.
Once this has been done, all I am seeking is for a subsequent process to start which updates a summary sheet with the sums correctly adding via the sumif function.
I have left some instructions on the actual spreadsheet to clarify my intended results.
I have a data import from our client something like this:
a1:12/11/2009 shoe bags blank-** $700.00 $0.00 $3,949.00
How can i delete all the numeric data in the cell?
i need only the text: shoe bags blank-** in a2