Problem with formula used to identify unique items

[Aussie-Mick]

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

 

[Ahnold]

Check to make sure there are no spaces after the text.
"Product XYZ" will show up different than "Product XYZ "

 

[Yogi Anand]

Hi Mick:

Your use of COUNTIF (several COUTNIFs really) with a large number of records is very taxing on the spreadsheet -- and depending on your processor speed and installed memory, your system probably is also becoming very sluggish.

You may want to be practical, and ... 1) sort the records either in ascending or descending order, and then use a simple equality formula to extract only non-duplicated records, and in an adjecent column assign a value of 1 to those records. This is assuming you do need to flag unique records with the Flag 1. Talking about being practical, you may want to look at what exactly you are trying to accomplish and whether Flagging the unique records is indeed necessary.

I hope I have not taken you astray -- but really you ought to take into account these practical considerations.

 

[Aladin Akyurek]

I guess the formula fails to cover entries that fall outside the range it looks at. And, as Ahnold points out, extraneous spaces around the items/products will thwart counting. You can clean them up with ASAP Utilities. If you insist on having a running count in an additional column, the following will be faster than your current formula and the range the new formula looks at will always be the actual used range.

Book1
	D	E	F	G
7
8		Items	16
9		productXYZ	4
10		productABC	1
11		product456	1
12		productXYZ
13		productXYZ
14		etc	2
15		etc
16		productXYZ
17
Sheet1

Formulas...

F8:

=MATCH(REPT("z",255),E:E)

F9, which is copied down:

=IF(ISNA(MATCH(E9,E$8:E8,0)),COUNTIF($E$9:INDEX(E:E,$F$8),E9),"")

 

[Aussie-Mick]

Thanks guys for your replies. They have all been very helpful. It was a simple problem to fix in the end. Ahnold was spot on. The 8 cells that were creating the new unique item counts did each have one space after the text.

I simply tidied each cell up. I had done the same with half a dozen cells that had created the same problem but the spaces were within the text and consequently stood out like beacons. I didnt give spaces beyond the text a thought though.

Yogi, my system is reasonably powerful and at this stage im not having too many problems with the large spreadsheet containing such formulas filled down the columns. As you say it will put a strain on the system and i'm starting to notice that now. Particularly when using filters to sort and analyse the data. When its all said and done thats what it's all about, using the spreadsheet to analyse data efficiently and make decisions based on that manipulated data. Further to the formulas I have mentioned there are two columns that contain filled down formulas that break the product name into two parts (two columns) which really doesnt add to the efficiency when it comes to using filters to sort. Everytime a sort is done the spreadsheet recalculates all the formulas which does cause a delay and can be very tedious as you wait.

Anyway to overcome this problem what I do is highlight the whole range of cells contained within the spreadsheet, copy it, and then do a special paste of the spreadsheet within a new worksheet with data as it is at that time in order to use filters to sort. Doing this i'm pasting values and number formats only as it is only the results im concerned with when i'm analysing the data and not the formulas. This exercise takes me less than a minute but when it comes to sorting in the new worksheet the data is sorted in the blink of an eye.

Again thanks all for your help it has been of tremendous assistance.

Regards


Mick

 

[Yogi Anand]

....
Yogi, my system is reasonably powerful and at this stage im not having too many problems with the large spreadsheet containing such formulas filled down the columns. As you say it will put a strain on the system and i'm starting to notice that now. Particularly when using filters to sort and analyse the data. When its all said and done thats what it's all about, using the spreadsheet to analyse data efficiently and make decisions based on that manipulated data. Further to the formulas I have mentioned there are two columns that contain filled down formulas that break the product name into two parts (two columns) which really doesnt add to the efficiency when it comes to using filters to sort. Everytime a sort is done the spreadsheet recalculates all the formulas which does cause a delay and can be very tedious as you wait.

Anyway to overcome this problem what I do is highlight the whole range of cells contained within the spreadsheet, copy it, and then do a special paste of the spreadsheet within a new worksheet with data as it is at that time in order to use filters to sort. Doing this i'm pasting values and number formats only as it is only the results im concerned with when i'm analysing the data and not the formulas. This exercise takes me less than a minute but when it comes to sorting in the new worksheet the data is sorted in the blink of an eye.

Again thanks all for your help it has been of tremendous assistance.

Regards

Mick

Hi Mick:

The bottom line is that at present you are able to do what you need to do -- and that is what counts. But when I read that you already have some 8000 rows and the data is growing, use of computation intensive formulas is going to become a problem ... and since the same functionality is available with less expensive (computation itensity wise) formulas, you ought to consider using those.

In regard to making some computations and then converting them to values, and continuing to proceed with such measures is fine for debugging and during design stage -- but after you have everything debugged and working properly, such intermediary steps that require a pro-active action of 'don't forget to copy and paste those as values' become a headache, because undoutedly one does forget to do that at a crucial stage. But, I guess as long as the spreadsheet is for our own use, we are all guilty of not ever cleaning it up completely because we never get out of the development mode.

Once again, the bottom line is that it is performing at a clip that is tolerable and acceptable to you -- and that is what counts. So let us keep EXCELing!