Find Top5 Or Top10 Or As Required Based On A Value In A Column
May 11, 2009I need to find out Top 5/Top 10 or as required based on two columns. I have clearly explained the way it is required in the attached sheet.
View 9 Replies
ADVERTISEMENT
Top5, Bottom5 From A Column Of Values.
May 17, 2009I have a column of values. I want to extract the 5 highest and 5 lowest without doing a sort on the column then manually selecting them. Can this be done with formulae? If not, any other method?
View 3 Replies View RelatedUserform One Combobox Three Data Columns Select Required Column Based On Radio Button
Jun 10, 2013I have a user form that has one combo box on it that right now references one column of data.
Now I am being asked to have three columns of data and the combo box to show one of the three when a certain criteria is met.
I believe it would be easiest to have an additional combox with a change event when the box is populated with "whatever" in the field
So S:3 to S:5 have A, B, C
And EC:1-EC:59, ED:1-ED:59, EE:1-EE:59 contain the data that should show when S:3, S:4, or S:5 is selected.
If S:3 is selected then the list in EC:1 - EC59 would show and so on.
Find Empty Cell In Column And Apply Required Character To Empty Visible Cells?
May 8, 2014I am looking to find all visible cells in column E that are blank, and then add ''B'' to those empty cells.
I am using code similar to the below:
[Code] .....
VBA Top10 Into Listbox
Sep 21, 2009I've a sheet with Names in column B and durations in column H.
Please could someojne point me in the right direction to populate a listbox with those 2 columns based on the highest 10 values? (they are times in hh:mm:ss format on the sheet)
Also, what if i wanted to filter by the top-10 times in column H, but for all names ending in "BN"?
Using Find Function In VB - Object Required
Aug 6, 2013This was my attempt, it says 'Object required'
Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, cancel As Boolean)
If Target.Column = 16 Then
Select Case Mid(Target.Text, 1, Worksheet.Function.Find("to", Target.Text, 1) - 2)
Case "CAS"
MsgBox ("Cashiers")
Formula To Find Required Character In Cells?
Dec 26, 2011I have following table,
Item NoSectorDestination
12346589BOM-DXBN/A
12346589DXB-FRAFRA
12346589FRA-DXBN/A
12346589DXB-BOMN/A
87665976NAG-BOMN/A
87665976BOM-DXBN/A
87665976DXB-PARPAR
87665976PAR-DXBN/A
87665976DXB-BOMN/A
87665976BOM-NAGN/A
Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
Macro To Find Required Data Than Copy/paste In Specific Cell Address
Nov 4, 2009I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.
LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
Required Input Based On Validation
Jun 13, 2007I have a form that when users hit submit, the data they entered gets transfered to Access (thanks to our MVP SydneyGeek for providing me with a macro!) I want to be able to limit or control the users who sends data, by making sure they enter every information that is needed.
I have a column that has a list using Validation. How do I make certain columns required based on what they choose on the list.. if they don't fill in those columns, I don't want them to be able to click Submit, showing an error that would say "Missing Required Fields".
Find Cell Based On Column And Row
Dec 2, 2009I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z
So for example B would be P1W1 and C would be P2W2, etc
what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ?
Apart from pivot table.
Find Records Based On More Than One Column
Feb 9, 2007I have a very large table that has a one to multiple relationship in a GIS. GIS doesnt support that type of relation ship. I need to find records that are related to the single key but have different values than the first record for that key.
eg
Col 1 col 2 Col 3(key)
ab ab 1
bc bc 1
In this example, only the first row (ab) will be regarded in the GIS. so I need to pull out the second row and put it into another dbf based on the three columns so that I can then put it into the GIS so that the second row is considered and not ignored.
Find Value In Table Based On Value In Row & Column
Mar 11, 2008I have a table where the columns are named: A, B, C, D and E and the lines are named: 1,2,3,4 and 5. All the cells of this table are filled with values.
I want the function to return the value on the correspondent cell everytime I type a combination of column and line. For instance if I type "B2" the function would return the value on this position...
Actually I am looking for something like a combination of the functions "hlookup" and "vlookup"...
Deleting Sheets Based On Name - Runtime Error Object Required
Jun 27, 2014I have a macro which creates and names worksheets. I am making a button which also deletes the latest of these created worksheets, but doesn't delete other sheets. I am getting the error: Run-time error '424': Object Required. Here is my code for deleting the sheet:
[Code] .....
MSCount stores the highest "MS#" sheet.
The first line of the IF statement is where the error is.
Merge Data From Sheet2 To Sheet1 Based On Required Criteria
May 1, 2014I need to merge data from sheet 2 to data on sheet1.
Sheet 1 data is in the format given below with start date in G column and campaign ID in column H.
Start Date
Campaign ID
17-May-14
3834177
[Code].....
I need to look up Campaign ID of sheet 1 in sheet 2 and add all insertions and platform for that campaign to next columns i.e. I and J in sheet 1. For ex Campign ID 3834177 of sheet 1 has 5 insertions corresponding to it in sheet 2 hence the insertions and their Platform would transfer to sheet 1. Since Campign ID 3834177 is only in 2 rows on sheet 1, 3 more rows would need to be inserted in sheet 1 to accommodate 5 items and it would look like.
Start Date
Campaign ID
Insertion ID
Platform
[Code].....
Find Column Based On Active Cell
May 16, 2013How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.
View 3 Replies View RelatedRequired VB Script / Excel Tip To Locate Latest Value In Column
Jan 9, 2014I need a VB Script or Excel tip to Locate the latest value in the column.
Example1
Value
1
2
3
4
5
6
Latest Value
6
Example2
Value
1
2
3
4
5
Latest Value
5
Note: I have 1 Row and 'N' Column. Column may have null value, which need to be ignore as mentioned in the example.
Also., if in future new value has been entered in the column then the Latest value has to be updated automatically with the new value.
Find Max Or Highest Time In Column G Based On Criteria?
Mar 20, 2014Im looking to find the max or highest time in column G based on the criteria (date selected and name) So if the date is the 10/02/2014 in G10 it would look up that date and the name and bring back the time in column H. I have put an example answer in H12.
View 9 Replies View RelatedFind Highest No. In A Column But Inclusion Is Based On Data In Other Cells
Oct 26, 2009I’m keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes it’s only 50. I’ve used MAX to find the highest score in Column D of a spreadsheet and it did what I required. However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.
Col B Col C Col D Col E Col F
date Location score out of % hit
25/10/2008A6210062
08/11/2008P3510035
22/11/2008A4210042
06/12/2008P6610066
20/12/2008A6010060
03/01/2009P295058
17/01/2009A5110051
31/01/2009P4010040
In other words I want to find the highest score for location P but only if that week it was out of 100 shots. It would return 66. Then I can do the same formula for location A and it would return 62. I can’t see how to do this and have searched the forum to no avail. It doesn’t look like I can just use the MAX anymore and I’ve tried incorporating that into a (nested) IF but unsuccessfully.
COUNTIFS On Filtered Table To Find Sum For A Column Based On 2 Criteria
May 23, 2013I'm trying to use countifs on a filtered table to find the sum for a column based on 2 criteria.
I've used =SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet3!K7,ROW(Sheet3!K7:Sheet3!K7:K20000)-ROW(Sheet3!K7),0)),--(Sheet3!K7:Sheet3!K20000="Yes"))
for a single criteria which is great, but I also need it to look at column B.
In a non-filtered table it works as:
=COUNTIFS('Sheet3 '!$K:$K,"Yes",'Sheet3 '!$B:$B,B5)
Is there any way to do this?
Find Columns By Name And Highlight Them Certain Colors Based On Text In Another Column
Jul 9, 2009I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................
Macro That Prompts To Chose Files And Copies Them Based On Criteria - Error 424 / Object Required
Mar 6, 2013I have written a macro that prompts the user to chose some files and if they meet certain criteria, it copies them to a specified folder.However, I have run into this 424 error "Object required".
Code:
Sub FILES2SFTP()
Dim FileNames As Variant
Dim I As Integer
Dim fso As Variant
Dim Data As String
ChDrive "G:"
ChDir "G:TEST"
[code]....
The error is in this line:
If fso.getfilename(FileNames(I).Name) = ("Name1" & Data & ".xls" Or "Name2" & Data & ".xls") Then
Find A Group Of Unique Rows- Find Non-zero Value In A Column - Fill Column With That Value?
May 27, 2014There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
Find Rows W/same Value In Column A And B And Offset Column C To Next Available Column
Aug 3, 2006I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
Aug 6, 2009Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
Find Most Recent Date In Column Headings In A Range - Insert New Column And Heading
Apr 4, 2014I have a 2 groups of column headings with a different month and year in each heading so
1st Group of columns range
Columns AJ through AX
Column Heading example "Expense Ratio February 2013......next Column over is "Expense Ratio March 2013"
2nd Group of columns range AY though CE
Column Heading example "Capital Balance February 2013......next Column over is "Capital Balance March 2013"
Each new month I need to add a new Expense Ratio column after the most recent expense ratio Column. (i.e. Find "Expense Ratio March 2013" and I need to add a column after that with heading "Expense Ratio April 2013"
Same thing for Capital Balance - add a new Capital Balance column after the most recent Capital Balance Column. (i.e. Find "Capital Balance March 2013" and I need to add a column after that for "Expense Ratio April 2013"
Because the ranges keep changing month over month, how do i do this.
Find Multiple Numbers In One Column And Replace With Single Phrase In Another Column
Jan 31, 2013I have taken over this spreadsheet for my work, and it is basically a statement in excel. What I want to do is find a list of invoice numbers in column B populated from a remittance, and then replace column F to say a specific thing depending on check number and date paying for that invoice. So if a check printed today I would have it replace column f to say paid 1/31/13 check # xxxxx. Currently I am searching for each invoice indivudually and then replacing with check number and date. There are about 200 invoices per month that I deal with, and it is a big waste of time!
For example from this
invoice #1 / reconciled (DATE)
invoice #2 / reconciled (DATE)
To this:
invoice #1 / paid (DATE) check # (xxxxx)
invoice #2 / paid (DATE) check # (xxxxx)
Find Median Of Set Of Numbers On Column That Correspond To Dates On Left Column
Sep 9, 2013I am trying to find the median of a set of numbers on a column that correspond to dates on the left column. I want a monthly median average of the numbers on the right which correspond to the dates on the left. So for example. I want to make an equation that gives me the median of all the numbers on the right if they fall within the range of a certain month(in this case October). I've tried These:
=IF(COUNTIFS(A:A,">=10/1/12",A:A,"
Find Lowest 5 Numbers In Column A With Highest Values In Column B
Oct 5, 2013I am looking for a formula that will find the lowest 5 number group in column A with a total value from column B under $100 in the below table.
1 $28.75
2 $28.00
3 $27.75
4 $24.75
5 $25.50
6 $25.25
7 $25.25
8 $16.50
9 $24.75
10 $26.50
11 $24.50
12 $27.00
13 $26.50
14 $23.50
15 $23.00
16 $19.25
17 $20.75
18 $25.00
19 $20.50
20 $20.50
21 $17.75
22 $22.50
23 $20.50
24 $19.75
25 $20.00
26 $10.75
27 $11.25
28 $5.25
29 $13.00
30 $9.00
Script To Find Column Header Doesn't Work If It Is Second To Last Or Last Column
Mar 3, 2014[Code] ......
This works, UNLESS "My Column Header" is the last column, or second to last column, then it jumps left two columns, instead of landing on the correct column.
The purpose of this script is to select a cell directly in that column that I was searching for.
Find The Column Name Purple & Delete The Whole Column If There Are No Blank Cells
Mar 18, 2009I'd like a macro that does 3 things..
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.