Extracting data from a long list

[Guest]

Hi. I have a very long list (1000+) of names of hotels in column A and
against each entry there is some data in column B. I want to be able to
somehow extract the data for just six or so hotels at a time without having
to go to each respective row (the hotels can be anywhere in the list). In
other words I'd like to type in a much shorter list of hotels and have Excel
paste somewhere on the sheet a list with just the data for those hotels. I
think I knew how to do this once. Very grateful for any help.

Chandler

 

[Peo Sjoblom]

Make a list of the six hotels in let's say D1:I1. Assume the data (not the
header) starts in A2, in C2 put


=COUNTIF($D$1:$I$1,"*A2*")>0

copy down 1000 rows, then select the whole range including the help column
in C
(make sure you have headers), after selecting the whole range do
data>filter>autofilter and from the dropdown in column C select TRUE, now
select all the visible data, copy it and paste it into a new sheet. Finally
delete the help column in the new list

 

[Guest]

In C1 and down put the short list.

In D1 enter:

=IF(C1="","",VLOOKUP(C1,A1:B11,2,FALSE))

and copy down as far as you did in column C

 

[Dave Peterson]

Typo alert:

I bet Peo meant:
=COUNTIF($D$1:$I$1,"*"&A2&"*")>0

Instead of:
=COUNTIF($D$1:$I$1,"*A2*")>0

 

[Peo Sjoblom]

Thanks Dave

Peo

Typo alert:

I bet Peo meant:
=COUNTIF($D$1:$I$1,"*"&A2&"*")>0

Instead of:
=COUNTIF($D$1:$I$1,"*A2*")>0

 

[Guest]

Dear Gary's student
Many thanks. What I ommitted to say was that the same hotel occurs more
than once in column A (in fact up to eight times) with a different piece of
data alongside each time in column B. The formula

IF(C1="","",VLOOKUP(C1,$A$1:$B$11,2,FALSE))

only picks up the first time the lookup_value occurs in column A and I need
all records.

Regards
Chandler

 

[Guest]

That works. Very many thanks. Though I'm sure when I use to do this I could
nominate a cell for the search results to appear in (or at least the first
search result with the rest following). Can't for the life of me remember
how I did it.

Chandler

Thanks Dave

Peo

 

[Steve G]

I have used advanced filter for this type of situation. I then copy
and paste the results to a new spreadsheet.

Steve G

 

[Peo Sjoblom]

You can use advanced filter without copying and pasting, it has a built in
copy to another location and if you start from another sheet it will copy to
that sheet, you need to create either an OR range or an OR formula to use as
criteria, so an OR criteria the traditional way would basically be a 6x7
range, for instance B1:G7, assume the header is Hotels then an OR criteria
would look like


Hotels Hotels Hotels
Hilton
Sheraton
W and so on

(don't know how this will come out, OE is notoriously bad when it comes to
this)

meaning B1:G1 have the headers (the same header), B2 the first hotel, C3 the
second, D4 the third all the way to G7 for the last hotel.

So go to the new sheet, select A1, do data>filter>advanced filter, click in
the list range and select the whole list from the source sheet

could look like

Sheet1!$A$10:$D$1000

Where sheet1 is the sheet name of the source and the list starts in A10 with
a header and ends in D1000

then the criteria range using my example would look like



Sheet1!$B$1:$G$7


finally select copy to another location and click in the cell in the goal
sheet where you want this new list, could look like

Sheet2!$A$1

then finish by clicking OK

note that you can do this without typing in the boxes, just selecting with
the mouse

 

[Guest]

Many thanks Steve and Peo, I'll play around with this.

Chandler