I have some VBA code that connects to a mySQL DB and imports a few 1000 rows of data. One of the columns is an "Amount" field. How do you automaticly set a sum at the bottom of the column when the Macro has been executed withou me going there and do the SUM calc after the import?
View 3 RepliesI'm trying to create a new report and looking to get the top and bottom ten from a list. My original idea was to use the LARGE and SMALL funcions. However, when I use those because some values may be the same, the corresponding lookup values would be incorrect.
If I have Vendor A,B,C
VendorA 100%
VendorB 100%
VendorC 98%
My report would read this:
VendorA 100%
VendorA 100%
VendorC 98%
I'm using INDEX with MATCH to bring back the vendor. I then figured, I could just link the cell to the top 10 cells from where I am pulling from, which wrks for top ten, but won't work for the bottom ten.
I'm pulling the the data from a sheet with a query so I won't know where the ending data is unlike where the starting data begins. I then thought that maybe I could use an option button to control the query and just change the sort order. But, I really don't want to use VBA on this report, which I think I would have to do for that idea to work.
I need a formula that will give me the bottom most value that is filled in a cell in a certain column.
View 9 Replies View RelatedI am needing to do a formula based on a range of data that will return the top third middle third and bottom third.
Example Data
W X Y
Gross Gross
Margin Margin% Rank
51,241 36.46%2
25,584 19.56%20
19,914 16.26%40
17,188 14.27%52
19,167 15.55%43
6,828 5.23%84
48,076 29.17%3
18,282 13.78%47
16,896 12.75%58
16,124 12.14%64
16,549 12.27%59....................
I first need it to look in the rank column for highest number and divide by 3
Then I need it to give me the average Gross Margin if rank is between 1-32, 33-64, 65-95
I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate)
SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32
I have tried averageif....
Using rank to calculate this may not be the most efficent way
number and number 2 is what i have.. I would like it similar to the right side of this sheet where the missing ones are at the bottom of E
sheet: ...
I have a huge spreadsheet that pulls 5 different types of data based on user statistics and I am trying to come up with a sheet that will rank the top five and bottom 5. I've learned a lot from seach this site and others but I am still running it to some formula problems, so I thought I would tackle a couple here.
The first is the top 5 and bottom 5 of a conversion rate. I use this formula to get the top 5.
=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),LARGE(PersonList!$AA$7:$AA$61,1),0)
Column PersonList!$Z$7 is the number of opportunities
Column PersonList!$aa$aa is the conversion percent being ranked
It works, but not like I am intending it too. I'm trying to elimanate those with less than 10 opportunities, and the first 2 people on the list have less than 10.
And for the bottom 5, I am using
=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),SMALL(PersonList!$AA$7:$AA$61,ROWS(K29:K$33)))
Column rows k29 to k33 is where the bottom five conversion percent sits, k33 being the lowest, most likely zero but could be negative
Again trying to eliminate those will less than 10 opportunities. Plus when this populates, if more than 1 person has the same score like 0.0, only the first person will be listed for the same multiple value of 0.0
you know that part at the bottom toolbar which tells you when you select two or more cells what the sum/average/max/min is? Mine's disappeared.
View 2 Replies View RelatedHave a five column worksheet with the column headings run column A1 - E1 and there are currently 112 players filling out the data with many more to come
The 5 Column Headers are as follows:
Player# ... PlayerName ... M/F ... SessionAve ... GamesPlayed
What I need to do I pull out the top 5 male and top 5 female by SessionAve (including ties if possible). Would like the info to look like:
Male Shooter ...Session Ave ...GamesPlayed
Player1 .843% 30
Player2 .837% 35
Player3 .835% 25
etc..
Then Same for Female Players
I tend to use a lot of For Each loops when I want to go through a range looking for something but this time I'm not so sure it will work.
I have 9 databases each containing over 400 rows with different pieces of client information, these databases are shared between around 40 people (I've also used the 'Allow users to edit ranges' utility to restrict access). What this means however is that the users cannot delete an entire row, instead they can only 'clear contents' on individual cells. This is fine until I try to generate statistics from the databases using autofilters which stop at the first blank row.
So I need to write a backwards for each statement that starts at the bottom and deletes all the blank rows on the way up to the top. I've tried similar things to this before using the For Each but when you use it to delete a row it sometimes skips the next row as it continues at the next one.
I've not managed to get it to work before, I'm just looking for a little guidance really as to how to do this correctly.
VB:
For TopRow = 1 To BottomRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row Step -1
'If is blank delete row.....
Next
I am trying to do the following:
901 > 0 pts
851 - 900 gets 1 pts
LOWEST 3 scores gets 2 pts
Example of what i have for the top three
=IF(D8="-",0,IF(RANK(D8,D$8:D$34)<=3,2,IF(D8<79%,0,1)))
I have tried to change it but cant get it to work for the bottom 3 scores.
I would like to have the column titles stay so I have frozen row 1. But I would also like to freeze row 200 so I can keep up with the running totals at the bottom of the form as I fill in information. Is it possible to freeze a pain at the top and the bottom?
View 4 Replies View RelatedAs we repeat rows at top and coloumn at left while printing using page setup option. can we repeat the rows at bottom and coloumn at right.
View 4 Replies View RelatedI want to be able to call the bottom number in a set of data that is ever expanding. I don't really know how else to describe it , so I will give an example.
Say I have:
3
6
4
I would want to be able to call 4, but then if I had:
3
6
4
5
I would want to be able to call 5, and so on. Is there any simple way of doing this? I basically want the maximum cell name, and the corresponding data inside of it.
I am looking for a shortcut key or some fast way of getting to the last line of information on a excel spreadsheet.
I currently have order spreadsheets with over 10000 lines used and everyday I need to add more lines and if I have used the Shift+F to locate an old order then I can be say sat on line 40 but my last inputted text could be on line 7000 and I would like to know how I can get to that line without knowing the line number.
The shortcut must take me to the last text entered cell not the last cell on the worksheet.
I am trying to print this document but when I print it, there appears a gap between the bottom of the page and the object at the bottom of the page. I have attached the word file (soft copy) as well as the clarification page.
Attached Files: letter head.pdf
Doc2.docx
I have approx 150 rows on my spreadsheet, but I am having difficulty viewing the bottom rows. The slider bar on the right hand side actually disappears on the bottom of the page. I am using windows 8. changed the tool bars and that worked, but I want to see the tool bars.
View 2 Replies View RelatedWhat would be the code to look for the last row with data in it, the going through it cell for cell and setting 0 in all the empty cells ?
View 12 Replies View RelatedI want to create a progress bar that will show how much percent is completed from bottom to top. It should count total total number of cases in the table. suppose they are 280 then it should show 0 to 280 in a label. 0 at the bottom and 280 at the top. then count how many cases have been completed as a percentage going up when percentage is increasing.
View 14 Replies View RelatedHow can I determine what the bottom row is in a range in VBA? I have an SheetChange event sub that takes in Target as Range. I want to know what the first/last row/column is in the Range. So, for example, say the Sheet has values in A1:B5 and I paste over A1:B4. Target will be A1:B4. I need a method that returns 4. I tried Target.End(xldown).row, but that gives me 5 (since theres data in A5).
View 4 Replies View RelatedI have the following code which goes into three different worksheets and deletes the bottom row.
View 4 Replies View Relatedsomeone show me how to change formula values. i am limited on information i can enter.i tried filling entire area with data to perhaps get a gradual movement down from 27-28 . how do i edit the formula to read 5 to 200 instead of 5-27
View 3 Replies View RelatedI need to underline certain cells based on the value of a cell in this case from column "E". Right now I run a macro to look at the value in each cell in column "E". THe macro then adds a certain amount of blank rows below the that row. So, if cell E1 has a 4, my macro runs a calculation that says 4 divided by 3, +2. So it would add 3 blank rows under row 1. It does this all the way down my column of data.
Now it gets tricky. To the right of column E, I have 6 blank columns, F,G,H,I,J,K. F,H, and J are spacer rows with no data. I need them blank with out any borders. Columns G, I, and K are the columns that I am wanting to add borders on the bottom of the cells based on the value in column E.
I need code to look at the value in E1, in this case 4, and add borders to 4 cells, in this case G1, I1, and K1 and then G2. If the value would have been 5, I need I2, 6 would add a border to K2 and 7 would add a border to G3. Catch my drift?
So, every cell in column E will not have a value because of me adding blank rows. So when there is a value, I need to add borders starting on the same row the value is in. At most, there will only be 3 underlined cells per row because of the format. Once the top row is filled, I need to drop down to the very next row.
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 Replies View RelatedI 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've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
If a sheet contains 200 rows of data, is it possible to make the last ten rows of data be shown when the sheet is opened.
View 6 Replies View RelatedI'm trying to put a border around a range and include the 3 rows beneath the last cell where data appears:
Range("A1:C1").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 1
End With
I have this data table where the totals are in the last(bottom) row.The number of rows of the table increases as new records are added to the bottom of the table. Now, the problem is , I need to have the Row that contains the Totals alwas on view no matter how big the table gets and no matter how far down/up the workshhet is scrolled.
I have tried Freeze Panes/Spliting adding new window , live shapes etc but all look dodgy and bring their own set of problems.
I have also thought about putting the Totals of the table in a row at the top of the worksheet and freezing the panes at that location but having the Totals showing at the bottom of the Table is the normal intuitive way .
I have a workbook that starts the beginning of the month by entering daily hours in cell D3 (Day 1 in cell D3, day 2 in cell E3, day 3 in cell F3 etc). Column B has several codes, but the one code the macro looks at when going down the current day is a letter "W" for "Worked". Therefore, Rows 4, 5, 9, 12, 56 (examples only - it changes daily) etc. could have a "W" and when the macro is ran, evertime it sees a "W" it includes the hours found in row 3 of the applicable day i.e. starting on row 4 the formula is =if($B4=D$3,D$3,""). This copies to the bottom row using the shortcut (Ctrl + Down Arrow) to find the bottom. What I have done is entered Zeros all the way down and changed Zero Values so they don't show.
Where I get in trouble is if a zero is removed, the shortcut stops at that break thinking that's the bottom. The bottom moves as we remove equipment out of the line up or add new equipment. What I am trying to do is have Excel figure out where the bottom row is for each daily calculation when the macro runs down the daily column.
I use this code to start at the the top of the column going down, but the way my data is setup, I need it to start at the bottom and work up.
Sub fillBlanksI()
'Declare variables
Dim FR As Long, LR As Long, i As Long, FillVal As Variant
'Stop screen flicker and speed up code
Application.ScreenUpdating = False
'Find the last row of any column in the sheet
LR = ActiveSheet.UsedRange.Rows.Count
'Loop from row i downwards until we find a value - this is the first filled row and assign its value to FillVal