Erase cell contents based upon a condition

I have a huge list which counts the number of items in the list (per
each record) in the total row. Based upon an entered condition ( like
put a 2 in B1 and compare all entries down that column to the 2 - if
the entry in each cell equals the 2 - great - if not, erase the 2 and
leave the cell blank).  I use the CountA at the end of the row, for
each record, to see how many items this person has versus another
record.

A                B              C                 D
                  2                                Total
Jim             2               4                  2
Bob            1               3                  1

Jim's total remains at 2 because he matches the entry in B1, Bob's
total is 1 because he does not match the entry in B1.  I would like to
have Bob's entry in column B to be erased - so I can use the counta
function to get the total in column D.  Currently I am
"looking" (manual) down the column and erasing the entry which does
not match - I have approx 250 records and 390 columns to deal with

thank you for any assistance you can offer.
0
skiing
1/13/2010 5:26:28 PM
excel 39879 articles. 2 followers. Follow

2 Replies
859 Views

Similar Articles

[PageSpeed] 45

It seems as if you want to delete some values just so that you can get
COUNTA to work on your data. There are other count functions, however,
so perhaps you should look in XL Help to find what you can do with
COUNTIF (or COUNTIFS if you have XL2007). SUMPRODUCT is another
function which might be relevant, as you seem to have multiple
conditions, but it is not very clear to me what you are starting out
with and what you ultimately want to achieve.

Hope this helps.

Pete

On Jan 13, 5:26=A0pm, skiing <trpa...@chatt.com> wrote:
> I have a huge list which counts the number of items in the list (per
> each record) in the total row. Based upon an entered condition ( like
> put a 2 in B1 and compare all entries down that column to the 2 - if
> the entry in each cell equals the 2 - great - if not, erase the 2 and
> leave the cell blank). =A0I use the CountA at the end of the row, for
> each record, to see how many items this person has versus another
> record.
>
> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0 =A0 =A0C =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 D
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 =A0Total
> Jim =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A02
> Bob =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A01
>
> Jim's total remains at 2 because he matches the entry in B1, Bob's
> total is 1 because he does not match the entry in B1. =A0I would like to
> have Bob's entry in column B to be erased - so I can use the counta
> function to get the total in column D. =A0Currently I am
> "looking" (manual) down the column and erasing the entry which does
> not match - I have approx 250 records and 390 columns to deal with
>
> thank you for any assistance you can offer.

0
Pete_UK
1/13/2010 5:37:49 PM
On Jan 13, 12:37=A0pm, Pete_UK <pashu...@auditel.net> wrote:
> It seems as if you want to delete some values just so that you can get
> COUNTA to work on your data. There are other count functions, however,
> so perhaps you should look in XL Help to find what you can do with
> COUNTIF (or COUNTIFS if you have XL2007). SUMPRODUCT is another
> function which might be relevant, as you seem to have multiple
> conditions, but it is not very clear to me what you are starting out
> with and what you ultimately want to achieve.
>
> Hope this helps.
>
> Pete
>
> On Jan 13, 5:26=A0pm, skiing <trpa...@chatt.com> wrote:
>
>
>
> > I have a huge list which counts the number of items in the list (per
> > each record) in the total row. Based upon an entered condition ( like
> > put a 2 in B1 and compare all entries down that column to the 2 - if
> > the entry in each cell equals the 2 - great - if not, erase the 2 and
> > leave the cell blank). =A0I use the CountA at the end of the row, for
> > each record, to see how many items this person has versus another
> > record.
>
> > A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =A0 =A0 =A0 =A0C =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A0 D
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Total
> > Jim =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 4 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0 =A0 =A02
> > Bob =A0 =A0 =A0 =A0 =A0 =A01 =A0 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A01
>
> > Jim's total remains at 2 because he matches the entry in B1, Bob's
> > total is 1 because he does not match the entry in B1. =A0I would like t=
o
> > have Bob's entry in column B to be erased - so I can use the counta
> > function to get the total in column D. =A0Currently I am
> > "looking" (manual) down the column and erasing the entry which does
> > not match - I have approx 250 records and 390 columns to deal with
>
> > thank you for any assistance you can offer.- Hide quoted text -
>
> - Show quoted text -

I have tried many of the functions - as every column is it's own
condition - any function I would use would have approx 350 criteria
items . Below is example data

Value	2	4	5	8	9	12

name	Test 1	Test 2	Test 3	Test 4	Test 5	Test 6     Test 7
Matches (thru test 6)
Bill		4	5		9		 13                3
jim	2			8			 14                2

Where a person's row matches the value row - I want to count those
items.  Bill matches for Test 2, 3, and 9 - thus total of matches =3D
3,  Jim matches for test 1 and 4 - thus total of matches =3D 2.
Currently I scroll down test 1's column, remove (erase) all the items
not equal to the entry in the value row for test 1 and my =3Dcounta
function will work as it counts the cells with data in them.    I
would like to have an automatic feature in place so when I enter Test
7's value at the top - the Test 7 column of entries would retain the
entry if it matches value of Test 7 at the top but would erase the
entry if it did not match the value of Test 7 at the top.  I would
then modify the formula in "Matches" column to include the column for
Test 7.

hope this helps


0
skiing
1/13/2010 6:01:24 PM
Reply:

Similar Artilces:

Non Numbers Cells
Hello, I would like to know if I can create a Macro that will do a search in column A (from A2 to A65536) and every row that has anything else than a number, delete the entire row (not just the cell). Thanks!!! Hi try the following macro: Sub delete_rows() Dim lastrow As Long Dim row_index As Long Application.ScreenUpdating = False lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For row_index = lastrow - 1 To 1 Step -1 If not isnumeric(Cells(row_index, 1).Value) then Rows(row_index).delete End If Next Application.ScreenUpdating = True End Sub >-----Origi...

Merged cells and Pivot table
I have the following tabel A B C Region Customer Product 1 ASIA Custo Apple Since one customer can have more products and a region more customers I used in the table mergd cells to keep the overview. However when I am using this data in my pivot table only the first complete row is stated in the pivit table. Any ideas how to solve this? regards Johan Johan, Select your table, unmerge all the cells, then use Edit Go To... Special Blanks. Then press = and eith...

Help Adding up multiple calculated cells
I have 8 columns that MAY contain NOTHING or may contain text chosen from a lookup name from a data validation pick list. Lic3 won't contain a value UNLESS Lic1 AND Lic2 are populated. I have 8 columns range named Lic1, Lic2, etc to hold a chosen license name. I have 8 other columns used for number counts relating to the lookups I have range named Lic1Cnt, Lic2Cnt, etc which hold a number such as 12. I need to add up the value calculated for each Lic based on looking up the text in a table, getting the price and then multiplying by the Lic? Cnt number. Some records may have only a singl...

Count of cells since first purchase
Hi, Looking for a bit of help with the below. I want to calculate how many weeks have passed since a customer first purchased from me. So for example see below. I want to calculate in cell E2 how many weeks have passed since Account 1 first purchased from me. In cell E3 i want to calculate how many weeks it is since Account 2 first purchased from me. Week 3 April Week 4 April Week 1 May 10 Account 1 2 3 Account 2 1 Account 3 ...

Making a MsgBox return cell data when search finds different data in an adjacent cell
Thank you for helping me with this in advance. I'm grateful for all the help I've found in these groups. I'm running a macro that is searching a sheet for data. When it finds that data, I need a msgbox to pop up with the data from that column's top cell. I have it searching the sheet and finding the data listed in the search. Because of the possibility of multiple winners, the msgbox will pop up each time someone scores. This macro works fine as it is except I would like to add the winners names to the message boxes. thanks again, JasonK With Worksheets(1).Range(&quo...

VBA code for copying on a range were cells are blank
Hi everyone, me again!! So, you were all so helpful with my earlier prob, thought i'd keep you going with another prob i'm having. OK, so what I want to do (in VBA if poss), is to search the range B2:E2 and see if they are blank. If yes, then I want to copy the cells above and then carry on searching that range. I can't do it upwards as its likely that there are going to be more than one set of blanks, e,g. A B C D E F 1 Joe Bloggs | 309873 | Smith, John | 08:00 | 12:00 | Back Office 2 | | ...

Formula to strip figures from cells text strings
I'm trying to write a cell formula that strips figures from cells text strings & totals them into one cell. The figures will always have a decimal. For example: Cell A5 has a text string "924 Social 9.78" Cell B5 has a text string "984 163.94 981 7.84" Cell C5 has a text string "920 Kitchen 7.86 Cell D5 has a text string :988 Youth 42.46 Need to arrive a the total amount of 231.88 in cell E5 (9.78+163.94+7.84+7.86+42.46)-do not include the non-decimal figures 924, 984, etc. Any ideas? Thanks for all your help. mikeburg -- mikeburg --------------...

Copy contents of a cell into a blank cell
How do I copy the contents of cell B2 into cell D2 only if D2 is empty Hi It cannot be done with a formula - only with VBA. The following code should achieve what you described Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$D$2" Then If Range("D2") = "" Then Range("D2") = Range("B2").Value End If End Sub To Use Copy Code above Right click on sheet tab>View Code Paste code into white pane that appears Alt+F11 to return to Excel -- Regards Roger Govier "fire9...

Specifying Cells to Count?
Hi everyone, I am having trouble adding in Excel... I have a list of IDs in A:A, and a list of numbers in B:B. For example: 1 0.1 1 0.5 3 0.1 3 0.2 3 0.3 6 0.1 6 0.5 8 0.3 8 0.9 8 0.8 8 0.4 Now, I need to calculate the number of instances of 0.1 corresponding to the value of 1 in A:A, the number of instances of 0.1 corresponding to 3 in A:A, and so on. I've tried FREQUENCY(), COUNTIF(),and a few others... I know the number of instances that 1,2 and any other number shows up in A:A, and I think I can use COUNTIF() -- however, I can't find a way to dynamically specify the number of...

How to keep a cell empty at the same time formula embedded?
Can we have an empty cell but formula embedded in that cell. A cell cannot be both "empty" and contain something...........you can however, put a formula there that will display a blank cell according to the conditions of it's calculations........... =(IF(A1>5,A1,"")......will display a blank cell if A1 is 5 or less Vaya con Dios, Chuck, CABGx3 "vic_dic" <vic_dic@discussions.microsoft.com> wrote in message news:A590D8A6-7E86-49B7-AA9A-12DE213E670C@microsoft.com... > Can we have an empty cell but formula embedded in that cell. What are you...

Cells formatted for general and text show scientific format
I am working on an Amazon.com shipping confirmation upload. The workbook is downloaded from amazon.com. When I enter the Order Item ID number which is 14 characters, it displays in scientific format. I have tried general, number and text formats. Number format works if there is no leading zero but general and text do not. - Are you formatting the column for Order Item ID number before entering the data? That should solve the problem. - If you are formatting after entering the data, selecting F2 (Edit) in a cell will then put that cell into the correct text format (except for leedi...

More than 3 on Conditional Formatting
Can it be done? I don't want to have to write a routine in VBA to scan all the cells just to change the colour..... i.e. 1 is red 2 is a lighted red, 3 is amber 4 is a ligther amber, and 5 green and 6 a lighter green... I need some help here guys... Anyone point me in the right directions... Regards. modify one of these. The first one belongs in the SHEET code module. right click on the sheet tab>view code>insert this>save Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column <> 1 Then Exit Sub With Target.Interior Select Case UCase(Target) Case "...

Knowledge Base
I am having a heck of a time using this new knowledgebase. I have yet to get any relevant responses no matter how I word a search. I was able to get the old knowledgebase a few times but I can no longer find it. Any suggestions about how to use this? or better yet, how to access the "old" knowledgebase? Thanks. Are you referring to Customer Source? I also have issues with it, and always end up coming here to find my answers! "Tracey D" wrote: > I am having a heck of a time using this new knowledgebase. I have yet to get > any relevant re...

Formula for selecting a varying cell reference
I'm trying to tie 2 worksheets together. The first is a data entry sheet, set up by month. The second is a sheet that I want to pick up monthly data -- i.e., the most recent month of information entered on the data entry sheet. Is there a formula I can write in the monthly sheet that will look at the data entry sheet and go to the last monthly column that has data and retrieve that? I guess I'm thinking like a flowcharting yes/no process starting in December -- i.e.,: Is December blank? Yes -- go to November; No -- use this data -- etc., etc., etc. Any ideas? ...

Storing VBA Code In a Cell to Use real Time
I want to store multiple values in Column A and VBA Conditional Code in Column B. The Conditional Code is just text and is just stored on the worksheet. When a macro is run and a variable equals the value in column A then the code stored in Column B executes inside the macro. For example, A1 = "ALLEX" and B1 = "If Not Left(route,1) = "K" . So, when the macro runs and checks to see if "ALLEX" is in Column A. If so ,then it will use the associated Condition: If Not Left(route,1) = "K". directly in the Macro. So, if both condition...

Conditional Formatting with Text
Hei gurus, How can I tell CF to turn a cell green if the cells contain text "great" and red if the cells contain "unacceptable??" TX a great deal cell value>="great">format>patterns (choose green) add cell value>="great">format>patterns (choose red) >-----Original Message----- >Hei gurus, > >How can I tell CF to turn a cell green if the cells >contain text "great" and red if the cells >contain "unacceptable??" > >TX a great deal >. > Select the range of cells Format > Co...

Clearing Check Boxes and Cells
Ok, this is either going to kill me or make me stronger.......... I have successfully combine my "clear" functions into one button now (clearing text/numbers from cells and clearing checks from boxes)................. NOW....... NOW.......... my problem is that when I protect the sheet........ the "clear" button that worked perfectly gives a "Run-time error'1004', Unable to set the Value property of the CheckBoxes class". If I turn protection back off, the button works perfectly. I have to be able to protect the sheet so users can only check boxes ...

NAICS Code
Hello, I have a list of NAICS codes formatted as numbers that I would like to format as hierarchical text. Current List 10 12 101 112 Desired List 10 101 112 12 Does anyone know how to change the cell format so that the "Current List" becomes the "Desired List"? Thank you in advance for your help. It looks like you could preformat that column as text and enter your values as text. Then you could sort the data as text. Some versions of excel will ask if you want to sort values that look like numbers as numbers. You should say no to that prompt. And just changing t...

Selecting Cells
Please help, When starting excel, I click on a cell and get the cross pointer but when I move the mouse to put a formula or data into that cell all cells are selected in the direction of mouse movement. I am unable to de-select the cells and the only thing I can do is close excel from the task bar. I have come across this problem once before But cannot remember how to solve it. Thanks in advance for any help with this issue. Regards Paul Paul You sould only get the cross hair if you move the mouse over the box at the bottom right-hand of the cell. This is used to enter a fill of a s...

Custom patterns for cell shading
Hello, Is there a way to add 'custom patterns' to the cell shading option? I was hoping for a nice 'arrow pattern' for the conditional formatting. (I'm doing a presentation of traffic flow, and for flow north or south [ie if Northbound > Southbound, flow is northbound], I would like to graphically show the flow.) Any assistance or ideas would be greatly appreciated. I'm currently using windings arrows, but a form of pattern / conditional formatting would be awesome. Jools -- World of Warcraft *isn't* work? ...

Repeating Characters in Cells with info.
I am trying to repeat a period to connect every column in a list of marathon records. I want it to read name.............time..............date...............location. how do i write the formula to do this? You could give the cells a custom format of: General*.;-General*.;0*.;@*. Positive;negative;0;text The *. says to repeat the dot enough to fill the cell (to the right). You may not want it on the numbers, though. Stressed Student wrote: > > I am trying to repeat a period to connect every column in a list of marathon > records. I want it to read > name.............tim...

I want to know which cell(s) are the MAX
Hi everyone, I`m relatively new to Excel but I think I`ve mastered the basics enough not to get flamed here. My question is this, I have a bunch of values, that I want to find the biggest out of, that is easily done using the MAX function. Now, I also need excel to tell me the number of the cell in which is the biggest, for example, say we had 5 cash values $3, $7, $9, $10, $3, and those values were numbered 1, 2, 3, 4, 5 in a list, I not only want to know that $10 is the biggest, but also that it is at number 4. Is there any way to do this please? Hi one way: =MATCH(MAX(A1:A99),A1:A99,0) ...

Count cells in a column that contain dates
Column J contains dates in some cells, and text in others. I need to count the number of cells in this one column that contains dates. How can I do this? If the only cells that are in that column that are numeric (like dates), you can use: =count(a:a) (Dates are just numbers formatted nicely in excel.) Cachod1 wrote: > > Column J contains dates in some cells, and text in others. I need to count > the number of cells in this one column that contains dates. How can I do > this? -- Dave Peterson ...

Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a range of cells and will not release. I end up having to force Excel to close and restart. Any help would be greatly appreciated. It does it with most versions of Excel. Hello Check if the F8 key was not pressed (you would have the "EXT" mention on the Excel Status bar), if yes press F8 again. Apart from that I think this could be a mouse driver problem so to make sure try and update your mouse driver (esp if you have a wheel mouse). Apart from that? HTH Cordially Pascal "Wolfer50" <Wolfer...

How do I lock a formula in a cell in an Excel worksheet?
I created a disbursement worksheet which is simple to use, just involves plugging in numbers and it figures everything out for us. However my coworker keeps erasing her formulas wehn entering info. Is there a way to lock a formula while still allowing numbers to be entered in the cell? Thanks for your help. Kathie Sure, ensure the formulas cells are locked and the other cells unlocked. When you password protect the sheet, make sure the section "Allow all users of this worksheet ti:" to not have Select locked cells checked. HTH ...