Aussie-Mick
New Member
- Joined
- Aug 1, 2003
- Messages
- 15
I have a database on excel that presently has 8002 rows of entries. The database is ongoing and growing.
Within the spreadsheet I have three columns in which the following formula is filled down each from row 9 to row 8010:
=IF(AND(COUNTIF(E$9:E$8908,E9)>0,COUNTIF(E$9:E9,E9)=1),COUNTIF(E$9:E$8908,E9),"")
Each of the 3 columns differs only in terms of the reference column i.e. E, F or G.
The purpose of the formula is to distinguish the number of times that a unique product name appears. When it identifies a new product it puts a number in the column and then shows the number of times that product appears down the remainder of the column.
The formula has worked well.
The problem I have noticed as the spreadsheet has got larger is that some products that have been identified further up the spreadsheet have a new item count appear as if they are a new unique product i.e.
product XYZ 28
product ABC 22
product 456 10
product XYZ blank cell
product XYZ blank cell
etc
etc
product XYZ 1
As the spreadsheet has grown the problem has magnified from 3 repeated identifications (after say 5000 entries) to now after 8000 entries there are approximately 15 products who's item count has appeared twice.
Could someone please advise if there is a range within which the above formula is able to identify that one product is the same as another. To this point it wouldnt appear to be the case as there are over 400 unique products in the database, many of which appear through out the spreadsheet i.e. one is the first entry in the spreadsheet on row 9, appears 37 times with the last entry appearing in row 7994 and the item count correctly appears once only i.e on row nine.
I have rechecked my formulas a number of times and cant see any glaring problem.
I would appreciate very much if anyone could suggest a reason for the problem and any solutions to overcome this as the database becomes even larger.
Thanks
Mick
Within the spreadsheet I have three columns in which the following formula is filled down each from row 9 to row 8010:
=IF(AND(COUNTIF(E$9:E$8908,E9)>0,COUNTIF(E$9:E9,E9)=1),COUNTIF(E$9:E$8908,E9),"")
Each of the 3 columns differs only in terms of the reference column i.e. E, F or G.
The purpose of the formula is to distinguish the number of times that a unique product name appears. When it identifies a new product it puts a number in the column and then shows the number of times that product appears down the remainder of the column.
The formula has worked well.
The problem I have noticed as the spreadsheet has got larger is that some products that have been identified further up the spreadsheet have a new item count appear as if they are a new unique product i.e.
product XYZ 28
product ABC 22
product 456 10
product XYZ blank cell
product XYZ blank cell
etc
etc
product XYZ 1
As the spreadsheet has grown the problem has magnified from 3 repeated identifications (after say 5000 entries) to now after 8000 entries there are approximately 15 products who's item count has appeared twice.
Could someone please advise if there is a range within which the above formula is able to identify that one product is the same as another. To this point it wouldnt appear to be the case as there are over 400 unique products in the database, many of which appear through out the spreadsheet i.e. one is the first entry in the spreadsheet on row 9, appears 37 times with the last entry appearing in row 7994 and the item count correctly appears once only i.e on row nine.
I have rechecked my formulas a number of times and cant see any glaring problem.
I would appreciate very much if anyone could suggest a reason for the problem and any solutions to overcome this as the database becomes even larger.
Thanks
Mick