counting

[MAMIBUSSOL]

i have collated some data into 1 sheet, the sheet contains purchase transactions, date, supplier totals etc.

i need to count the number of suppliers, making sure I only count each supplier once, the supplier is listed in column B

 

[T. Valko]

i have collated some data into 1 sheet, the sheet contains purchase transactions, date, supplier totals etc.

i need to count the number of suppliers, making sure I only count each supplier once, the supplier is listed in column B

There are several ways to do this. The best option depends on some factors.

What is the specific range of cells in question? (need to know how big the range is)

Are there any empty cells within the data range that's to be counted?

 

[crusader]

PivotTable is one option...

 

[MAMIBUSSOL]

the suppliers are listed in alphabetical order, so the only blanks will appear at the bottom of the list, however as for the range, there are currently 202 rows of data, but this may change. ie this time round we have 202 rows, however tomorrow might be 100, and wednesday might be 250

 

[MAMIBUSSOL]

crusader what is pivot table?

 

[T. Valko]

the suppliers are listed in alphabetical order, so the only blanks will appear at the bottom of the list, however as for the range, there are currently 202 rows of data, but this may change. ie this time round we have 202 rows, however tomorrow might be 100, and wednesday might be 250

Try this...

=SUMPRODUCT((B2:B250<>"")/COUNTIF(B2:B250,B2:B250&""))

 

[MAMIBUSSOL]

Sorry maybe I should have been clearer, I need to do this using coding

the function does work however the i could have more than the 250, I won't know the number of transactions until I get the files

 

[T. Valko]

Sorry maybe I should have been clearer, I need to do this using coding

the function does work however the i could have more than the 250, I won't know the number of transactions until I get the files

OK, just hang in there and maybe someone will respond with the code.

If using the worksheet formula you can simply use a larger range that you know for certain will be enough for your needs. For example, reference down to row 300?

Good luck!

 

[crusader]

crusader what is pivot table?

Sorry maybe I should have been clearer, I need to do this using coding

PivotTable is an analysis tool that can be accessed through the Insert -> PivotTable menu in Excel 2007.

I am sure someone will assist you in concocting code to meet your need: I don't have that expertise - yet.