Function that lists specific rows of a database based on a cell value

setilvenstre

New member
Joined
Oct 17, 2012
Messages
3
Reaction score
0
Points
0
Hello everyone!


I have a task at work that gives me a little bit of a headache. I'm hoping that someone will be able to help me out with it.

So the situation is the following:


I will have a database with around 2000 entries. all of them will have a name and an assigned value (among other things). the value assigned to names could be from 1 to 25 (so there is 25 possibilities in terms of values).


What I'm looking for is a function that can list all entries that belong to a specific value. For example a function that lists all names with the value 5.


I'm hoping that there is a function for this and it's just my lack of knowledge that makes this task complicated and time consuming for me (I came up with a solution for 15 entries, but it took me a lot of steps and time to make it happen).


If anyone has any ideas, I'd be extremely glad to hear them.


Thank you in advance!


All the best,


Peter
 

eisayev

New member
Joined
Oct 1, 2012
Messages
33
Reaction score
0
Points
0
Location
Baku, Azerbaijan
Hi Peter
I tried to do what you want, But every detail important to build up the formula. How will you build your sheet, and the what type of information or data you use is important also. I add my example here. Please simulate your problem on excel sheet and send us back.
My formula is like that. (array formula)
=IFERROR(INDEX(CHAR(LARGE(CODE(IF(($B$1:$B$225=E$1)*1=1;$A$1:$A$225;CHAR(1)));ROW(INDIRECT("1:"&COUNTIF($B:$B;E$1)))));$D2);"")

But I am hundred percent sure that it is not exactly what u want.
 

Attachments

  • example.xlsx
    27.7 KB · Views: 20

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
You can use the Filter option.

If your data to search is in column A, simply select cell A1 then click the data tab and filter.
It will then give you a drop down box in A1, you can then select the values you want displayed.
It will filter out all the rows that do not contain the data you select.

Simi
 

setilvenstre

New member
Joined
Oct 17, 2012
Messages
3
Reaction score
0
Points
0
Thank you for your reply Simi!

The thing is that I would need all this to happen automatically. I just wrote down part of my assignment. after these sortings are done, the data will go through additional processing. So bottom line, all this should happen automatically...
 

Simi

New member
Joined
Feb 10, 2012
Messages
190
Reaction score
0
Points
0
Location
Utah, USA
Excel Version(s)
Version 2002 Build 12527.20194
I believe this is the sort of thing you are looking for. It is done using VBA not just a formula.
This will filter/parse the data you desire to a new worksheet.
I have left the new worksheets with default names so you can rename them whatever you desire, and not have a conflict when running the code.

Simi
 

Attachments

  • setilvenstre.xlsm
    18.7 KB · Views: 13

setilvenstre

New member
Joined
Oct 17, 2012
Messages
3
Reaction score
0
Points
0
Hi Simi!

This is great!! Thank you so much for your help!!!

Best,

Peter
 
Top