Text Lists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
Click to expand...
 
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
 
The OP wanted all comments relating to the house. I think this will only
bring up the first comment.
 
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!
==============================================
 
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
 
Back
Top