excel newbie hitting a wall

goat82

New Member
Joined
Nov 26, 2009
Messages
4
Hey guys - I am just learning that sometimes excel can't handle something and you may need some code - but am curious if there is a solution to this. If you have 4 columns of text based data and want a list of the content of the first column for rows filling certain criteria in the other three columns what function would you use?

In the real situation there are over 1000 rows of which only 5 meet the critreria for a given search and what I want is that list of 5 items on the first 5 rows of the resulting list - not spread out over 1000 rows. Using the standard "IF" format doesn't do this for me.

Any suggestions / guidance would be appreciated - sorry if this has been addressed previoulsy - I have not gone into the forum history.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
have you tried the AutoFilter or Advanced Filter features of Excel? Based on your brief explanation Filtering may be the solution for you.
 

goat82

New Member
Joined
Nov 26, 2009
Messages
4
That could be it! I've never used that function but it seems simple enough - hopefully I can build that into what I need it to do.... I'll re-post if I have another drama as that help is hugely appreciated
 

goat82

New Member
Joined
Nov 26, 2009
Messages
4
OK - I see this working as dumping a report into one tab/sheet and having other sheets pre set up to run the different filters I need to get the data I need. To make that work I need to know 2 things;

1) how do you make one sheet, or at least a column, equal ALL the lines of another column in another sheet without dragging the "=" formaula across and down 5000 rows?

2) how can I change the display of the tabs for each sheet so they are stacked rather than running along a dragger bar - I need all the tabs visible for user frendliness and I will need about 24 different filter combos.
 

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557

ADVERTISEMENT

why do you want to setup 24 different sheets? can't you just use the same sheet to apply the filter?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,716
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you need a number of filters, another option may be looking at Pivot tables.

Regards
Michael M
 

goat82

New Member
Joined
Nov 26, 2009
Messages
4
why do you want to setup 24 different sheets? can't you just use the same sheet to apply the filter?

Basically I'm building a tool to be used in several of our retail stores to analyse stock on hand. The users of this tool will be new staff in a growing business and most will have no excel experience and effective training would not be time efficient due to rotation of roles. Our inventory software is basic but has some real advantages and we're not looking at upgrading - hence needing to export reports from it into our own formatted excel pages as analytical tools. The idea of all the sheets is that from one dump there are let's say about 24 sub categories of data that would each have it's own list and ideally be quickly linked to via one mouse click - hence sheets. They all run similar but different filtration of the of the same data, breaking the 5000+ line stock report into usefully sorted lists of stock by category/pricerange/size.

If you think there's a better approach to this feel free to clue me up - I can visualise the sheets working perfectly but the 2 problems I raised would need resolution.

I will look into pivot tables - thanks for the guidance - all suggestions a massive help to me.
 

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
I was thinking along the lines of 1 sheet (data source) + 1 combobox + 1 sheet (for the filter output). The user will select a report from the combobox then (using vba code) that will filter the sheet based on the report selected.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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