Hiding Redundant Values From Multiple Sets Along With Adjacent Values
Aug 2, 2013
I am working on creating a spreadsheet that can be updated by those unfamiliar to excel. I have a master list on one sheet and three separate lists for business, individual, and general. Each list has three columns giving name, address and postal code. There are numerous repeats on these lists and I wanted to hide any duplicates while keeping my cells aligned. So far I have been able to highlight any duplicates, but was interested in some function that would save at least one duplicate while hiding any additional ones.
View 2 Replies
ADVERTISEMENT
Mar 7, 2009
I am attaching a worksheet for which I need to calculate "National Points". I need to select 5 lowest values from "Tech" and "Speed" (Row 3 headings) events to the left of the totals column but a minimum of three "Tech" values need to be used. I have used the min, small 2, small 3 for the "Tech" set then used an IF statement for the fourth value. The formula returns the correct sum for values up to the fourth value. The problem comes on the fifth value. It seems to depend on if the 4th and 5th value come from the "Tech" events or "Speed" events. I have calculated the values manually and am unable to get them to correspond all the time. Can anyone help?
View 9 Replies
View Related
Jan 1, 1970
Can we convert the numeric amount into words..i.e. Rs. 148250.00 (Rupee One Lac Forty Eight Thousand Two Hundred Fifty Only)..Is there any simple formula in xls and ms access...
View 14 Replies
View Related
May 8, 2007
how much excel can do and the amount of experts that is willing to help out in this forum.
Is there anyway to combine all the values to omit redundant data?
For e.g.
A B
1 Apple 28
2 Pear 55
3 Orange 35
4 Pear 22
5 Pear 15
6 Orange 18
7 Apple 25
is there any VBA codings that could automatically reduced all the above data to
A B
1 Apple 53
2 Pear 92
3 Orange 53
View 9 Replies
View Related
Aug 7, 2012
I've tried several approaches on how to code this, but can't figure out how to work it. I have a large spreadsheet; which dynamically changes in both column and row counts. So, I have an array of values. If any cell has a negative value, that cell text needs to be changed to red AND that row needs to remain displayed. If ALL values in that row are >=0 then the row can be hidden.
The array is actually a pivot table named QTD
For Example: If cell D5 = 5 and F5 = -3 then Row 5 should remain displayed.
If Cell D6 = 5 and F6=0 then row 6 should be hidden because all values are > 0
Then I was thinking of using the case statement to hide the rows but can't figure the syntax. I'm open to any other means of performing the taks as long as the initial array dynamically to encompass all the data.
View 7 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
Jul 6, 2012
I have to sets of values that I have graphed....number and dollar amount.
The dollar amount is in the hundreds of thousandths and the number is in the hundreds. I want to have two verticle axis. one on the left hand side for the dollar and one on the right side for the number.
View 1 Replies
View Related
Jul 30, 2006
There are two sets of values.
The first one are located at Sheet1(a2.a6) and Second one are at Sheet2(A1.A1000). how I can write a VBA code to find the First value in Second set of values. If it found in second set, Sheet1.B1 will be entered as the found value from Second set of value otherwise "NOt found".
This task should go till the no of values in first set (in this example five times)
View 9 Replies
View Related
Jan 15, 2014
Here's a simplified example:
ColA
ColB
ColC
Row1
A
Y
A
Row2
B
N
D
[Code] .........
I'm looking to return the values in column A adjacent to the cells in Column B equal to "Y". The kicker has been returning only the cells where the condition is true. Column C displays the desired behavior.
The closest I've been able to get is with a simple IF statement but I'm pretty sure the answer is a far cry away from there and likely requires an array formula. I'd prefer not to use VLOOKUP or OFFSET but will if the alternative is very complex.
View 7 Replies
View Related
Oct 29, 2011
I have 5 and 6 digit transaction references in one column. Is there a quick way of finding the max and min values of both the 5 & 6 digit sets ?
View 3 Replies
View Related
Oct 31, 2007
2 columns from a table are:
Bill# Rates
1715 500
1715 600
1715 625
1716 750
1717 760
1717 780
1718 400
1719 650
1719 800
So there is a bill number with multiple rates. I want to find out the minimum rate for each bill number (for 1715 it is 500 and so on). Whats the easiest way to give a formula for each bill so it does it automatically? I know of SUMIF and COUNTIF but how to "MINIF"?!
View 9 Replies
View Related
Oct 21, 2009
I am trying to create a line graph to show the trends (up or down) of I-Fund vs Gold. The trouble I am having is how to set the axis. The date one is an easy one, but the gold range is more or less 800-1000, and the I-Fund is 13-18. How can I have these both on the same graph to compare? I am attaching an Excel sheet as a reference. Obviously I need to delete the empty IFund cells.
View 2 Replies
View Related
Sep 5, 2007
I am a absolute newbie to VBA, I've got 2 timetables, they show deliveries for different countries. I need to make my code as DYNAMIC as I can. TableCustomer shows the customer's name, country and their opening times, Saturday - Friday, it uses 1s and 0s to determine if they are open or closed, 1 = open, 0 = closed, so the headers are: Customer Name, Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc.
TableCompany shows the days of deliveries made to each country by the company, using 1s and 0s, 1 = Deliver, 0 = Don't deliver. The headers are Country, Saturday, Sunday, Monday, Tuesday, Wednesday etc. An example here is that, if in TableCustomer, the customer is opened(1) on a Saturday, but in TableCompany, the company does not deliver(0) to that country on the Saturday, change that 1 into a 0 in TableCustomer. But if the Customer is closed(0) on the Saturday, then don't change the value 0, because the company will not be able to deliver to them anyway, even if they can deliver to that country on that day.
View 3 Replies
View Related
Oct 11, 2008
My Workbook has 72 worksheets split into two. The first 36 include all the data, but the second 36 have one element removed.
I am very grateful to RoyUK and others who have steered me towards some code that allows me to hide and unhide the first 36 sheets exactly as I want, but when I add the second 36 the code comes up with an error saying the Procedure is too large.
I did want to split the code into two parts activated by two validated drop down menus, but this did not work either.
If I have two drop down menus at D8 and G8 is it possible to have two separate codes as follows
View 11 Replies
View Related
Jun 5, 2014
I request you to write a code for me to fill the cell values as "Not Applicable" in Column "AZ", if the "B" Column cell values = "Justified", "Approved LSAR" & "Approved SDAR".
I have attached the work book of what I am trying to accomplish.
View 14 Replies
View Related
Oct 27, 2009
Here's an example of what i'm working with,...
Range is C40:D48
View 4 Replies
View Related
Sep 4, 2009
I'm trying to hide all but one duplicate value in a list; I have a report that can give multiple results for each record, but to make it cleaner to read I want to hide the duplicate values, such as customer names etc. after they have first appeared. I'm aware this may not be a very clear explanation so have attached an example spreadsheet.
The left-hand list is similar to what I would start with and the right-hand list is what I am looking to achieve. Conditional formatting will format duplicates but will obviously all to all and can't use the remove duplicates function as it will take away the unique values alongside them in the row. Preferably I'm after a solution that works in both '03 and '07 but it's not a disaster if I can only do it in '07.
View 5 Replies
View Related
Jan 10, 2010
I have 3 columns on my spreadsheet with 100 rows:
- each cell in the 3rd column has an equation in it as such:
=h4+f5-g5 which means that the current cell in column 3 is adding the value of the cell just above it + and - the values of the cells on the left to it. This works fine, the only problem is that even though nothing is entered in row 40 for example, there still is a value based on the last calculation in row 5 for example - this value shows all the way down to row 100. Anyways,
How can I hide those values in column 3 all the way down to row 100 if nothing is entered in the other 2 rows?
View 10 Replies
View Related
May 12, 2014
I am trying to look & match key values from 2 areas of one table with two areas of another table; in turn, it'd return one value based on the lookup table...
Attached worksheet : Test booklet.xlsx
View 4 Replies
View Related
Apr 6, 2014
I'm using the following code to hide rows with zero values in my workbook. I have 10 sheets for potential data/products and a "summary" sheet that is set up to pull all the information from the 10 sheets. I often don't use all 10 sheets so there are a lot of blank cells. I set up the code below to hide zero values to make the "summary" sheet easier to work with. I am encountering a problem with cells that are formatted as dates. a zero value/empty cell shows up as 1/0/1900 and is therefore not auto hidden.
Either any adjustment to my formatting or code to automatically hide these blank date cells on the "summary" sheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)Dim c As Range
For Each c In Range("C3:C8778")
If c.Value = "0" Then
c.EntireRow.Hidden = True
[Code] ...........
View 4 Replies
View Related
Apr 18, 2014
I'm using the below macros to hide or unhide rows based off of a value in column A. Column A contains an If statement that looks for a value in column F, and depending on whether the corresponding cell in column F contains a value the if statement returns either True or NO-IF(F>0,True,"No).
It currently takes quite a bit of time to run this macro. Given the specs on this machine, I'm convinced that there is probably some loop or inefficiency in my code.
Sub Button1_Click()
Dim cell As Range
For Each cell In Range("a59:A1472")
[Code]....
View 3 Replies
View Related
Apr 27, 2014
What I need to do is hide the value in an individual cell, dependent on whether a value (any value) has been entered in another cell. I know this must be possible somewhere in conditional formatting but I can't seem to figure it out no matter how much I try!
The table below should hopefully explain exactly what I'm after. I want to hide the value in the balance column (automatically calculated)when no transaction has taken place (ie, there is no date entered in the date column). At the moment this value appears all the time.
DATE
DETAILS
DEBIT
CREDIT
[Code].....
View 7 Replies
View Related
Mar 25, 2013
I need to hide the rows in an excel whose value is equal to the array list that has been already hardcoded...How to do this...
View 1 Replies
View Related
Jul 7, 2014
I am currently building a tool that works with an undefined range of input variables, e.g. I now have a range of 200 values but it could just as easily be 400 or 100. I am using these values for further calculations and have thus 'dragged down' to cells that in some instances are empty, resulting in values that are 0. I'm also building a chart based on these values, but it shows the values that are 0 as well!
Is there any way to just plot the non-zero values without changing the data range?
View 1 Replies
View Related
Dec 7, 2009
I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).
My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!
View 2 Replies
View Related
Mar 23, 2012
Does Excel have the ability to automatically hide rows without values in certain cells?
For example:
Sheet1
ABC1Route NumberTechnicianNumber of Units Cleaned25Tony237Don749 511Ray12613James16715Chuck21817
In this example, Rows 4 and 8 would automatically be hidden, leaving the other rows displaying. Of course, somehow I'd have to "Unhide" these rows at some point to add data if needed.
This is for a spreadsheet that is about 500 rows. Conceptually, I would automatically hide the rows w/o data in column B, analyze (or print) the worksheet, then "Unhide" the columns to enter data the next day.
View 2 Replies
View Related
Mar 20, 2014
the support this board has given me as I learn VBA. I have three columns - Q, R, and S. I only need to see columns R and S if the cell values don't equal those in column Q or each other. So if I have cell values like the ones listed in the example below, then I don't need to see columns R and S.
Q Header
R Header
S Header
50
50
50
[Code]....
View 4 Replies
View Related
May 9, 2013
I have attached sample sheet.
I need to populate the Rep Names looking up 3 Criteria (Client Id,Curr Cov Id,Dom Buy Grp Id) from the Table 1, either one matches the Rep Names has to populate.
The data has to be pasted on WIP_Sheet in different columns.
View 3 Replies
View Related
Nov 4, 2008
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
View 9 Replies
View Related
Jul 14, 2006
I've got the following formula in a column: =IF(C10/B10>=1,20,IF(ISERR(C10/B10),0,0)). The purpose is to ensure that only one of two values (20 or 0) is displayed, regardless of circumstances that regularly generate the # DIV/0 error. The if(iserr(...)) part works fine when tested on its own, but when nested, it's still allowing the error to display.
View 3 Replies
View Related
