Problem with formula used to identify unique items

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Ahnold

Well-known Member
Joined
Feb 20, 2004
Messages
636
Check to make sure there are no spaces after the text.
"Product XYZ" will show up different than "Product XYZ "
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
DEFG
7
8Items16
9productXYZ4
10productABC1
11product4561
12productXYZ 
13productXYZ 
14etc2
15etc 
16productXYZ 
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

New Member
Joined
Aug 1, 2003
Messages
15
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Aussie-Mick said:
....
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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,448
Members
425,547
Latest member
Iano

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top