How to retrieve a list of rows from another list in excel?

[Bokazoit]

I have this sheet with close to 1000 rows. Each row contains a store (it's address, phone, etc.), and it also contains information about visitfrequencies for our salesstaff. The sheet looks like this:

Konzs Chainnr Storenr Sales District Customer Number Customer Name 1 Street Postcode City
K DK601 DK60109 DK10 DK110999 URTH SUPER SPAR HJØRRINGVEJ 401 9750 ØSTER-VRÅ
K DK601 DK60109 DK10 DK4202156 VENKØB SUPER SPAR NORDENS ALLE 14 9800 HJØRRING
K DK100 DK10004 DK10 DK400507 DAGLIGBRUGSEN BINDSLEV VESTERBRO 9 9881 BINDSLEV
K DK100 DK10004 DK10 DK176289 DAGLIBRUGSEN L P HOUMØLLERSVEJ 22 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK273474 HAVNE-SUPER A/S SYDHAVNSVEJ 8 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK297853 HARALD CHRISTIANSEN EFTF. A/S VESTER STRANDVEJ 6-8 9990 SKAGEN



I can see it's a little messy but hope You can see through it still

If this list is in Sheet2, how do I - in sheet1, only show chains (second coloumn) with number DK601? And what if I wanna see deeper and only see stores (third coloumn) with number DK60125?

To info there is 5 chains and 15 different storenames and almost 1000 stores

So what I need is to get a list of stores i.e I choose only to see all DK100 stores. The list generated I would need to apply a new set of formulas so the above is only part of the task in hand

Hope You can help

 

[matpj]

have you tried using the auto-filter so that you can choose which codes you want to filter on?


Matt
London (UK)

 

[Bokazoit]

Yes and the autofilter is fine but it's not what I need. The user (for now me) should enter some criterias in some cells and few of the criterias is used to sort the list a gave above (the real list has almost 1000 rows though) and other criterias incorporates timedefinitions (workhours), traveltime etc.

The end result should give a list a stores that meets all the criterias, so I need to retrieve that list before I can add more criterias

 

[xlbo]

Have a look at ADVANCED filter functionality

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Bokazoit]

Okey I try again. If I don't wanna use the filterfunction then what? If I have the list above in sheet1 (copied again for Your convinience ):

Konzs Chainnr Storenr Sales District Customer Number Customer Name 1 Street Postcode City
K DK601 DK60109 DK10 DK110999 URTH SUPER SPAR HJØRRINGVEJ 401 9750 ØSTER-VRÅ
K DK601 DK60109 DK10 DK4202156 VENKØB SUPER SPAR NORDENS ALLE 14 9800 HJØRRING
K DK100 DK10004 DK10 DK400507 DAGLIGBRUGSEN BINDSLEV VESTERBRO 9 9881 BINDSLEV
K DK100 DK10004 DK10 DK176289 DAGLIBRUGSEN L P HOUMØLLERSVEJ 22 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK273474 HAVNE-SUPER A/S SYDHAVNSVEJ 8 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK297853 HARALD CHRISTIANSEN EFTF. A/S VESTER STRANDVEJ 6-8 9990 SKAGEN

And I would like to see only those stores with chainnr DK601 in a new sheet:

K DK601 DK60109 DK10 DK110999 URTH SUPER SPAR HJØRRINGVEJ 401 9750 ØSTER-VRÅ
K DK601 DK60109 DK10 DK4202156 VENKØB SUPER SPAR NORDENS ALLE 14 9800 HJØRRING
K DK601 DK60125 DK10 DK273474 HAVNE-SUPER A/S SYDHAVNSVEJ 8 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK297853 HARALD CHRISTIANSEN EFTF. A/S VESTER STRANDVEJ 6-8 9990 SKAGEN

In my new sheet I would apply some formulas such as CountA to get the amount of remaining stores. If that doesn't fit my needs I would add one more criteria so that I would only see those stores with storenr. DK60125:

K DK601 DK60125 DK10 DK273474 HAVNE-SUPER A/S SYDHAVNSVEJ 8 9900 FREDERIKSHAVN
K DK601 DK60125 DK10 DK297853 HARALD CHRISTIANSEN EFTF. A/S VESTER STRANDVEJ 6-8 9990 SKAGEN

If that meet my needs I would stop and the list of stores will be the guideline for a raid.

I would like to apply criterias in sheet2 and either by running a macro or something else, I get a list in sheet2 that meet those criterias

Hope this explains my problem better