General help please.

Anx · Apr 8th, 2002, 05:13 AM

Hi, I have to sort some data on a spreadsheet with the following column headers;

Location,Pallet No,Description,Product ID,Qty,Price,Product Code,Comments,Date Returned.

Many of the items repeat but in different columns, i.e. same product code/description etc but different date, is there a way to filter these please?

I also need to be able to show on a seperate worksheet three different price %'s depending on the time the stock has been in storage. Some of the stock will also be out of date and i also need to show this on the sheet, is it possible to filter or search dates in some way ?

I also need to display items worth less then 10.00 after these have been calculated.

I'm also not allowed to use any macros,

any ideas or advice would be welcome.

Thanks.

[ This Message was edited by: Anx on 2002-04-08 04:16 ]

Dave Hawley · Apr 8th, 2002, 05:20 AM

Hi Anx

Sounds like a good job for Excels feature(s)

1. Data>Filter>Auto Filter
2. Data>Filter>Advanced Filter
3. Data>Pivot Table

Anx · Apr 8th, 2002, 05:23 AM

Thanks, i'll give it a look.

Anx

Anx · Apr 8th, 2002, 06:24 AM

Hi, what does advanced filter do please? it doesn't appear to have any effect.

I've also looked at Pivot Tables but don't really understand their purpose or how to use it to achieve what I want.

Thanks

Anx

Anx · Apr 8th, 2002, 06:36 AM

...also how can I create a formula that will display e.g. dates that are over 6 months old onto another sheet please ?

Thanks again

[ This Message was edited by: Anx on 2002-04-08 05:36 ]

Jay Petrulis · Apr 8th, 2002, 06:37 AM

Quote:

On 2002-04-08 05:24, Anx wrote:
Hi, what does advanced filter do please? it doesn't appear to have any effect.

I've also looked at Pivot Tables but don't really understand their purpose or how to use it to achieve what I want.

Thanks

Anx

Hi Anx,

Advanced Filter is an underused feature, in my opinion. It is very powerful when used correctly, but is definitely tricky to understand at first. Check the Excel help and it may get you started.

Same with Pivot Tables.

http://www.cpearson.com/excel/pivots.htm

is another introduction to pivot tables.

Sorry, I cannot find a good link to advanced filters. David McRitchie's site might help (address not known off hand).

Will edit post if I find an advanced filter link.

Bye,
Jay

Anne Troy · Apr 8th, 2002, 06:39 AM

To help out here:

http://www.geocities.com/davemcritchie/excel/excel.htm

Anx · Apr 8th, 2002, 06:45 AM

Excellent, thanks very much for your help.

Anx

Apr 8th, 2002, 06:39 AM	#7
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	To help out here: http://www.geocities.com/davemcritchie/excel/excel.htm __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 05:13 AM	#1
Anx New Member Join Date: Mar 2002 Posts: 22	Hi, I have to sort some data on a spreadsheet with the following column headers; Location,Pallet No,Description,Product ID,Qty,Price,Product Code,Comments,Date Returned. Many of the items repeat but in different columns, i.e. same product code/description etc but different date, is there a way to filter these please? I also need to be able to show on a seperate worksheet three different price %'s depending on the time the stock has been in storage. Some of the stock will also be out of date and i also need to show this on the sheet, is it possible to filter or search dates in some way ? I also need to display items worth less then 10.00 after these have been calculated. I'm also not allowed to use any macros, any ideas or advice would be welcome. Thanks. [ This Message was edited by: Anx on 2002-04-08 04:16 ]

Apr 8th, 2002, 05:20 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi Anx Sounds like a good job for Excels feature(s) 1. Data>Filter>Auto Filter 2. Data>Filter>Advanced Filter 3. Data>Pivot Table

Apr 8th, 2002, 05:23 AM	#3
Anx New Member Join Date: Mar 2002 Posts: 22	Thanks, i'll give it a look. Anx

Apr 8th, 2002, 06:24 AM	#4
Anx New Member Join Date: Mar 2002 Posts: 22	Hi, what does advanced filter do please? it doesn't appear to have any effect. I've also looked at Pivot Tables but don't really understand their purpose or how to use it to achieve what I want. Thanks Anx

Apr 8th, 2002, 06:36 AM	#5
Anx New Member Join Date: Mar 2002 Posts: 22	...also how can I create a formula that will display e.g. dates that are over 6 months old onto another sheet please ? Thanks again [ This Message was edited by: Anx on 2002-04-08 05:36 ]

Apr 8th, 2002, 06:45 AM	#8
Anx New Member Join Date: Mar 2002 Posts: 22	Excellent, thanks very much for your help. Anx