Quick question for all the Excel gurus on this forum:

I have a spreadsheet with the following fields dumped into one row:
Ministry,Load Date,Fiscal End,Vendor Name

Below that I have over 9000 rows with the info related to the above
fields.
What I want to know is how would I sort the data so that I could find
out how many total contracts vendor X was given for example regardless
of ministry?

Can I use Excel to sort it or do I need to create a function or a query
in VB?

any help is appreciated.

Thanks,
Vik

Sounds like the job for a pivot table, Data-"Pivot Table and Pivot Chart
Report"
--
Regards,
Dave


"vik" wrote:

Quick question for all the Excel gurus on this forum:

I have a spreadsheet with the following fields dumped into one row:
Ministry,Load Date,Fiscal End,Vendor Name

Below that I have over 9000 rows with the info related to the above
fields.
What I want to know is how would I sort the data so that I could find
out how many total contracts vendor X was given for example regardless
of ministry?

Can I use Excel to sort it or do I need to create a function or a query
in VB?

any help is appreciated.

Thanks,
Vik

maybe if you use some kind of filter you would have the data you need, i
mean, in the menu bar, in the top, you can find a option "Data" then "Filter"
and "Auto filter".

good luck.

"vik" wrote:

Quick question for all the Excel gurus on this forum:

I have a spreadsheet with the following fields dumped into one row:
Ministry,Load Date,Fiscal End,Vendor Name

Below that I have over 9000 rows with the info related to the above
fields.
What I want to know is how would I sort the data so that I could find
out how many total contracts vendor X was given for example regardless
of ministry?

Can I use Excel to sort it or do I need to create a function or a query
in VB?

any help is appreciated.

Thanks,
Vik

You don't need to sort it to determine the number of contracts. If you
have a small number of vendors you could use a formula that counts the
number times a particular vendor name occurs. If you list your vendor
names in a column starting at A2 of a summary tab, you can use the
formula =COUNTIF(A2,VendorRange) where vendor range is the column in
the sheet of 9000 rows that shows the Vendor Name. Copy this formula
down for the entire list of vendors.

If you have too many unique vendor names to use the method above,
create a pivot table. You should be able to follow the wizard by
selecting any cell in the data table, then launching the wizard by
selecting Data | Pivot Table Wizard.

- John
www.johnmichl.com

Hi John,

Thanks for the reply. My problem is two-fold:

(1) I have a large number of unique vendors and

(2) each row has about 15 different categories with each category only
separated by a comma. So basically when this data was extracted to the
excel spreadsheet, it put all 9000 entires into a spreadsheet with only
1 column and 9000 rows. When I try to do a pivot table excel won't
recognize the data so that option doesn't work for me.

Regards,
Vik

Vik
First order of business would be to convert that single column into
multiple columns. Use the Excel feature "Text to Columns" to parse
this out for you.

1) Highlight the column of data
2) From the menus select Data | Text to Columns
3) Choose Delimited then Next
4) Deselect all Delimiter boxes except check the Comma box
5) Click Finish to do it or Next to add formatting for each column.

That should get you to a more usuable database.

- John
www.JohnMichl.com

Vik
First order of business would be to convert that single column into
multiple columns. Use the Excel feature "Text to Columns" to parse
this out for you.

1) Highlight the column of data
2) From the menus select Data | Text to Columns
3) Choose Delimited then Next
4) Deselect all Delimiter boxes except check the Comma box
5) Click Finish to do it or Next to add formatting for each column.

That should get you to a more usuable database.

- John
www.JohnMichl.com