Creating Text lists

[Jaytee]

I have several rental properties. I get Guest Surveys, and list the name of a
house in column A, and the comment on the house in Column C. On a seperate
worksheet, I want to type in the name of a house in, lets say, A1, and
generate a list of comments from the first sheet that correspond to that
house name. Any help?

[Bernie Deitrick]

Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
comments that you are currently interested in.

HTH,
Bernie
MS Excel MVP


"Jaytee" <[email protected]> wrote in message
news:[email protected]...
>I have several rental properties. I get Guest Surveys, and list the name of a
> house in column A, and the comment on the house in Column C. On a seperate
> worksheet, I want to type in the name of a house in, lets say, A1, and
> generate a list of comments from the first sheet that correspond to that
> house name. Any help?

[Jaytee]

I actually did that on the appropriate page, but I'd also like to create a
"House Summary Page" in which the comment data and other scores are listed in
a printable format for the homeowner. What do you think?

"Bernie Deitrick" wrote:

> Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
> comments that you are currently interested in.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jaytee" <[email protected]> wrote in message
> news:[email protected]...
> >I have several rental properties. I get Guest Surveys, and list the name of a
> > house in column A, and the comment on the house in Column C. On a seperate
> > worksheet, I want to type in the name of a house in, lets say, A1, and
> > generate a list of comments from the first sheet that correspond to that
> > house name. Any help?
>
>
>

[Matt Lunn]

Quick and dirty solution.....

Create a command button on the sheet you wish to display the comments on and
enter this code (change Sheet1 in the code to whatever the sheet name holding
all your data is)


Dim iLoop As Integer
Dim iReturnRow As Integer

iReturnRow = 1

Range("B:B").ClearContents

For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A"))

If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then

Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3)
iReturnRow = iReturnRow + 1
End If


Next i

HTH,
Matt

"Jaytee" wrote:

> I actually did that on the appropriate page, but I'd also like to create a
> "House Summary Page" in which the comment data and other scores are listed in
> a printable format for the homeowner. What do you think?
>
> "Bernie Deitrick" wrote:
>
> > Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
> > comments that you are currently interested in.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Jaytee" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have several rental properties. I get Guest Surveys, and list the name of a
> > > house in column A, and the comment on the house in Column C. On a seperate
> > > worksheet, I want to type in the name of a house in, lets say, A1, and
> > > generate a list of comments from the first sheet that correspond to that
> > > house name. Any help?
> >
> >
> >

[Jaytee]

Command Button, would that be through a macro?

"Matt Lunn" wrote:

> Quick and dirty solution.....
>
> Create a command button on the sheet you wish to display the comments on and
> enter this code (change Sheet1 in the code to whatever the sheet name holding
> all your data is)
>
>
> Dim iLoop As Integer
> Dim iReturnRow As Integer
>
> iReturnRow = 1
>
> Range("B:B").ClearContents
>
> For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A"))
>
> If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then
>
> Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3)
> iReturnRow = iReturnRow + 1
> End If
>
>
> Next i
>
> HTH,
> Matt
>
> "Jaytee" wrote:
>
> > I actually did that on the appropriate page, but I'd also like to create a
> > "House Summary Page" in which the comment data and other scores are listed in
> > a printable format for the homeowner. What do you think?
> >
> > "Bernie Deitrick" wrote:
> >
> > > Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
> > > comments that you are currently interested in.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Jaytee" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I have several rental properties. I get Guest Surveys, and list the name of a
> > > > house in column A, and the comment on the house in Column C. On a seperate
> > > > worksheet, I want to type in the name of a house in, lets say, A1, and
> > > > generate a list of comments from the first sheet that correspond to that
> > > > house name. Any help?
> > >
> > >
> > >

[Matt Lunn]

Hi,

You could use the code in a macro. Just make sure it's always run while your
display sheet is active. Let me know if you need any help with this.

Thanks,
Matt



"Jaytee" wrote:

> Command Button, would that be through a macro?
>
> "Matt Lunn" wrote:
>
> > Quick and dirty solution.....
> >
> > Create a command button on the sheet you wish to display the comments on and
> > enter this code (change Sheet1 in the code to whatever the sheet name holding
> > all your data is)
> >
> >
> > Dim iLoop As Integer
> > Dim iReturnRow As Integer
> >
> > iReturnRow = 1
> >
> > Range("B:B").ClearContents
> >
> > For i = 1 To WorksheetFunction.CountA(Worksheets("Sheet1").Range("A:A"))
> >
> > If Cells(1, 1) = Worksheets("Sheet1").Cells(i, 1) Then
> >
> > Cells(iReturnRow, 2) = Worksheets("Sheet1").Cells(i, 3)
> > iReturnRow = iReturnRow + 1
> > End If
> >
> >
> > Next i
> >
> > HTH,
> > Matt
> >
> > "Jaytee" wrote:
> >
> > > I actually did that on the appropriate page, but I'd also like to create a
> > > "House Summary Page" in which the comment data and other scores are listed in
> > > a printable format for the homeowner. What do you think?
> > >
> > > "Bernie Deitrick" wrote:
> > >
> > > > Yes. Son't use a second sheet - simply apply a data filter to your first sheet, and show only the
> > > > comments that you are currently interested in.
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > >
> > > > "Jaytee" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >I have several rental properties. I get Guest Surveys, and list the name of a
> > > > > house in column A, and the comment on the house in Column C. On a seperate
> > > > > worksheet, I want to type in the name of a house in, lets say, A1, and
> > > > > generate a list of comments from the first sheet that correspond to that
> > > > > house name. Any help?
> > > >
> > > >
> > > >

[Ashish Mathur]

Hi,

If i have correctly understood your proble, you basically want to want to
list all the comments against that house number. This solution assumed that
youy want to get the result on the same sheet and not on a different one as
desired by you. Try this:

I assume tat your House numbers are in range A1:A7 and comments are in range
B12:B7

Suppose you enter the house number for which you want the comments in cell
A10, array enter (Ctrl+Shift+Enter) the following formula in cell B10 and
copy down

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

This is not a perfect solution but just a workaround (your lists must start
from row 1

Hope this helps

Regards,

"Jaytee" wrote:

> I have several rental properties. I get Guest Surveys, and list the name of a
> house in column A, and the comment on the house in Column C. On a seperate
> worksheet, I want to type in the name of a house in, lets say, A1, and
> generate a list of comments from the first sheet that correspond to that
> house name. Any help?

[Gord Dibben]

Have you tried VLOOKUP?

You could have some VLOOKUP formulas on an "owner" sheet
pointing back to the data sheet to fill in the blanks.

You would enter the name of the house in a cell and adjacent cells would show
the info for that house only.

For more on VLOOKUP see Debra Dalgleish's site

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben Excel MVP

On Tue, 19 Jul 2005 10:55:08 -0700, Jaytee <[email protected]>
wrote:

>I have several rental properties. I get Guest Surveys, and list the name of a
>house in column A, and the comment on the house in Column C. On a seperate
>worksheet, I want to type in the name of a house in, lets say, A1, and
>generate a list of comments from the first sheet that correspond to that
>house name. Any help?