RE:reasons as requested
- From: "GB" <GB@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 16 Jan 2006 05:36:03 -0800
Let's see.. Yes you would get errors if you tried to directly copy the lines
indicated below. The value X must be either defined, or replaced. Where X
would be 1443. I also troubleshot my work. As I wrote the code here, and
not in Excel and rarely use the text append (&) I realize our error(s).
Before and after each usage of '&' there must exist a space. So here is a
rewrite of the code I provided earlier. I'm going to break it down to run
across several lines so that you might be able to copy it straight from here
into your code. If you want to make it one long line, then simply start at
the next line, delete until you have erased the underscore ('_') and ensure
that there is always at least one space before and after any & that is not in
the text.
As for your equation that you have used, you need to remove the last $ in
the equation. If I am not mistaken when you look at the equation that is in
every row of column H, every row compares the current row to the very first
row.
Anyways, try this code, and see if it gives you the unique list of items
intersperced with FALSE.
Sub TestThis()
Dim I As Integer
For I = 4 To 1443 '<- Where _
X is the last row you want to include data
Range("T" & I).Formula = _
"=IF(COUNTIF(H" & I & _
":H1443,H" & I & ")=1,H" _
& I & ")"
Next I
End Sub
"christopher ward" wrote:
> dear Gb - thank you for yor efforts im afraid on this topic i have not
> progressed
> i tried to copy in you
>
> For I = 4 to X <- Where X is the last row you want to include data
> Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
> Next I
>
> however got lots of compile errors
>
> im sure i can get vba to do what i want with this function but at the moment
> im like a blind man looking at the screen
>
> my code currently stands at
>
> Sub spot_duplicates()
>
> Sheets("Data").Select
> Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"
>
> End Sub
>
> my results in cells v4 to v 1443 = =IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)
> which shows as a FALSE all down the 1443 cells that the formula is placed into
> sorry i cant describe this issue any more - i have however solved the multi
> page issue on the other thread
>
> --
> C Ward
>
>
> "GB" wrote:
>
> > Maybe I'm trying to hard on this response, but want to make sure that at
> > least you and I are on the par with each other. The code that I provided
> > using the for statement, (which based on the below e-mail 1443 would be your
> > X in the for loop), should provide exactly what you are looking for. It is
> > obviously implemented in VBA, as a pivot table for this instance would not
> > work. However.... Do realize that you could have a standing pivot table that
> > gets refreshed when the source data changes. The pivot table location would
> > be known and you could still do other VBA work off of it. But, pending that
> > next step, the for statement would work...
> >
> > Here is what it does. It says, if the first item is in the entire list more
> > than once, then the location holding the "copy" of the data is equal to
> > FALSE. On the second item, it says, if this item is *also* somewhere in the
> > following lines then it should be FALSE, however if the item in Row 5 Column
> > H is only in the remaining rows of column H, then it is shown. This process
> > continues for each additional item. Obviously the last item where it will
> > look at only itself is in the list at least one time but then also obviously
> > not in the list more than once, as there is only a group of one. (Sorry for
> > all the logic speak, but that's what was necessary to determine how to get
> > what you wanted.) The end result, is that if you work your way from the end
> > of the list to the beginning, the first time you find the item it will be in
> > your list of FALSE's and unique items. Now, programmatically and depending
> > on what you do with this data, it may be better to have the item show up the
> > first time it is used from the top. To do this, instead of adjusting the
> > range from current location to the end of the list, make it go from the first
> > cell to the current location. Your population of unique items would be
> > towards the top vice the bottom.
> >
> > Get back with us if the code works, or at least if the concept does. :)
> >
> >
> > "christopher ward" wrote:
> >
> > > let me explain more , currently my system has a range of products in col H
> > > the col H contains many repeated values so code aaa can appear many times
> > > the column start at row 4 to row 1443 and in fact is the number of minutes
> > > in a given day in that we have 1440 minutes in every 24 hours
> > >
> > > the if statement must be done by programming and not by a pivot table due to
> > > the nature of the system being built, this is my first work with vba so i do
> > > apologise if i am frustrating you
> > >
> > > i actually want to build a long col of values which are either false or
> > > indeed show the unique code at least once in the list so that i can then loop
> > > on this list and force it into a list box later on to show unique codes
> > >
> > >
> > > --
> > > C Ward
> > >
> > >
> > > "GB" wrote:
> > >
> > > > Well, okay maybe not copying exactly, as I saw that you had implemented some
> > > > $'s, but at least get the quotes right. :)
> > > >
> > > > Sorry was having trouble getting all of the lines of code we have discussed
> > > > on different threads together. Please try to continue an issue on the same
> > > > thread by replying to one of the messages there. Any message...
> > > > I think you have referred to this problem like 4 times in the last 2 days on
> > > > 4 different threads.
> > > >
> > > > I have revised my formula as given in my last post to include an additional
> > > > "&I&" as seen in the COUNTIF Function.
> > > >
> > > > Again, please be sure to copy all quotes that are included.
> > > >
> > > > For I = 4 to X <- Where X is the last row you want to include data
> > > > Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H"&I&")=1,H"&I&")"
> > > > Next I
> > > >
> > > > I think your formula always tried to plug in the result of finding H4,
> > > > instead of looking to see how many times the item in the current row appears.
> > > >
> > > > "GB" wrote:
> > > >
> > > > > Your if statement is at fault, and the lack of anything after H4... The H4
> > > > > is what appears if the item appears at all (Evaluates as true). Your If
> > > > > statement says, if I have the item only one time, then show me the item. If
> > > > > I do not have the item, or it appears more than once, then do nothing
> > > > > (Evaluate to false which is what you are seeing.)
> > > > > I tried changing the =1 to >1 and >=1 and did not get a unique list, but I
> > > > > did at least remove the false.
> > > > >
> > > > > If you change your range of the countif statement to where it includes a
> > > > > search of only the current row to the last row, and do an equivalency check
> > > > > of =1, then in the end you will have a list of False at every instance that
> > > > > there is more than one of that item, and only a list of the items at the last
> > > > > usage of that item...
> > > > >
> > > > > To implement this, if you go back to my for I = 4 to X routine that was
> > > > > discussed yesterday, and revise the start row of the countif portion in the
> > > > > equation being inserted, you can implement what I just described.
> > > > > So it would look like this:
> > > > >
> > > > > For I = 4 to X <- Where X is the last row you want to include data
> > > > > Range("T"&I).Formula = "=IF(COUNTIF(H"&I&":H1443,H4)=1,H"&I&")"
> > > > > Next I
> > > > >
> > > > > And if you remember to copy the above Range.formula line exactly as written,
> > > > > including all quotes, it will provide you a list of unique items and false at
> > > > > each location where the search determines that there is another of the same
> > > > > item below. If you weed out all of the False items, then you will be left
> > > > > with a list of unique items though possibly spread out from top to bottom.
> > > > >
> > > > >
> > > > > Basically
> > > > >
> > > > > I don't see how you would get the unique product list from this form of
> > > > > evaluation. A pivot table would be more appropriate for a unique product
> > > > > list. Also explaining the reason you are trying to do what you are doing
> > > > > helps us all out in resolution.
> > > > >
> > > > >
> > > > >
> > > > > "christopher ward" wrote:
> > > > >
> > > > > > Range("T4:T1443").Formula = "=IF(COUNTIF($H$4:$H$1443,H4)=1,H4)"
> > > > > >
> > > > > > try as i may my ifcount always evaluates as false where as i want it show me
> > > > > > my unique product list - i dont care if the other cells are false
> > > > > >
> > > > > > i appreciate all the help and i have got vba to enter the formula which is a
> > > > > > step forward but im not sure i understand the ifcount principal as i thought
> > > > > > it would do this
> > > > > >
> > > > > > my code is shown below and i have been careful with both quotes and $ signs;
> > > > > >
> > > > > > Sub spot_duplicates()
> > > > > >
> > > > > > Sheets("Data").Select
> > > > > >
> > > > > > Range("v4:v1443").Formula = "=IF(COUNTIF($H$4:$H$1443,$H$4)=1,H4)"
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > --
> > > > > > C Ward
.
- References:
- RE: ifcount evaluates always false
- From: GB
- RE:reasons as requested
- From: christopher ward
- RE:reasons as requested
- From: GB
- RE:reasons as requested
- From: christopher ward
- RE: ifcount evaluates always false
- Prev by Date: is there a currency conversion function?
- Next by Date: Re: Help with code
- Previous by thread: RE:reply to other thread on integer and select
- Next by thread: RE:reasons as requested
- Index(es):
Relevant Pages
|
