I have a spreadsheet that has over 5400 rows of entries.


One of the columns is name of a vendor. We have many vendors listed and multiple times.
I am looking to see how can I extract the names of the vendors so that I can run a count function.

basically, I am looking to get all the entries from the sort drop down box into the spreadsheet.

1. Copy the column with names into separate worksheet. Use Remove Duplicates on copied list.

2. Create a Pivot table to count names. Copy names from Pivot table to another location. Or simply use this Pivot whenever you need the Vendor count (you can set filters for Pivot too - e.g. by dates)

3. On another worksheet, create an ODBC query like
where YourTable is your Excel table, and VendorNames the column with names in it.
or
You can set the query to be refreshed on Open, and you can refresh it manually whenever you want (right-click on query table, and select Refresh from dropdown menu).

You can also try
=SUM(IF(FREQUENCY(IF(A2:A11<>"",MATCH(A2:A11,A2:A1 1,0)),ROW(A2:A11)-ROW(A2)+1),1))
(adapt ranges as necessary-unchecked-Commit as array formula with Ctrl+Shift+Enter)
which should count the number of distinct vendors in one go

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Consider also perhaps a Pivot Table. It's the easiest way to get unique list of vendors and corresponding counts in one shot.