Text Lists
 

Let's say I have several rental houses. Column "A" lists house names from a
guest survey. Column C lists Comments on the house from a guest survey. On a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.

Take a look at using Advanced Filter. Plenty of good guidance at
http://www.contextures.com/xladvfilter01.html

--
Regards
Roger Govier
"Jaytee" wrote in message
...
Let's say I have several rental houses. Column "A" lists house names from
a
guest survey. Column C lists Comments on the house from a guest survey. On
a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.

on sheet 2, use formula =LOOKUP(A1,Sheet1!A1:A5,Sheet1!C1:C5)
then in A1 when you enter the house name, the description will appear

"Jaytee" wrote:

Let's say I have several rental houses. Column "A" lists house names from a
guest survey. Column C lists Comments on the house from a guest survey. On a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.

Thanks, but I tried that. It seemed to only works for the first comment on a
given house. Picture this for sheet A:

1 2
A ALPHA " LOOKS GREAT"
B BRAVO "WORST HOME EVER"
C ALPHA "JUST SUCKED!"
D CHARLIE "HAD A GROOVY TIME"

If I type in "Alpha" in A1 on sheet B, I want to see this:
1 2
A Alpha "LOOKS GREAT"
B "JUST SUCKED"

What do you think?




bigwheel" wrote:
D
on sheet 2, use formula =LOOKUP(A1,Sheet1!A1:A5,Sheet1!C1:C5)
then in A1 when you enter the house name, the description will appear

"Jaytee" wrote:

Let's say I have several rental houses. Column "A" lists house names from a
guest survey. Column C lists Comments on the house from a guest survey. On a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.

The OP wanted all comments relating to the house. I think this will only
bring up the first comment.

--
Regards
Roger Govier
"bigwheel" wrote in message
...
on sheet 2, use formula =LOOKUP(A1,Sheet1!A1:A5,Sheet1!C1:C5)
then in A1 when you enter the house name, the description will appear

"Jaytee" wrote:

Let's say I have several rental houses. Column "A" lists house names from
a
guest survey. Column C lists Comments on the house from a guest survey.
On a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.

You've got 1 & 2 on top (columns?)
And A, B, C down the side (rows?)

Assuming a standard Sheet1 format,
Column A contains the house name
Column C contains the comments

Say the data list is from A1 to C100 on Sheet1.

On Sheet2, enter the house name to look up in A1.

Enter this *array* formula in B1:

=INDEX(Sheet1!$C$1:$C$100,SMALL(IF(Sheet1!$A$1:$A$ 100=$A$1,ROW($A$1:$A$100))
,ROW(A1)))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Now, copy this formula down as many rows as you anticipate the maximum
number of comments.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Jaytee" wrote in message
...
Thanks, but I tried that. It seemed to only works for the first comment on
a
given house. Picture this for sheet A:

1 2
A ALPHA " LOOKS GREAT"
B BRAVO "WORST HOME EVER"
C ALPHA "JUST SUCKED!"
D CHARLIE "HAD A GROOVY TIME"

If I type in "Alpha" in A1 on sheet B, I want to see this:
1 2
A Alpha "LOOKS GREAT"
B "JUST SUCKED"

What do you think?




bigwheel" wrote:
D
on sheet 2, use formula =LOOKUP(A1,Sheet1!A1:A5,Sheet1!C1:C5)
then in A1 when you enter the house name, the description will appear

"Jaytee" wrote:

Let's say I have several rental houses. Column "A" lists house names
from a
guest survey. Column C lists Comments on the house from a guest
survey. On a
seperate worksheet, I want to type in the name of a house, and have
excel
list all the comments that pertain to that house.

Sheet 1
Cells B1 to C4: your data.
Cell A1:
=ROW($I1)-ROW($I$1)+1
Copied down to A4
Cell D1:
=SUMPRODUCT(--($B$1:$B1=$B1))
Copied down to D4
Cell G1:
=SUMPRODUCT($A$1:$A$4,--($B$1:$B$4=Sheet2!$A$1),--($D$1:$D$4=$A1))
Copied down to G4
Sheet 2:
Cell A1:
alpha (or whichever of the other letters)
Cell B1:
=VLOOKUP(Sheet1!$G1,Sheet1!$A$1:$C$4,3)
Copied down to B4


"Jaytee" wrote:

Let's say I have several rental houses. Column "A" lists house names from a
guest survey. Column C lists Comments on the house from a guest survey. On a
seperate worksheet, I want to type in the name of a house, and have excel
list all the comments that pertain to that house.