Retrive Data from Multiple Cells

[hansjhamm]

I have a workbook with a sheet "Lot Information" where the homeowners
name and lot number is listed:

Column A - Lot #
Column C - Owner Name


"Lot Information Sheet"
Column A -Home Owner
Name Column B - Lot #
Joe
Smith
A1
Ben
Jones
A2
Al
Baker
A3
Al
Baker
A4

I have another sheet called "Checks Received". What I want is "Checks
Received" sheet to look at the name from "Lot Information" sheet and
give me the lot #(s).

"Checks Received"
Column A-Homeowner
Name Column
B - Lot#(s)
Joe
Smith
A1
Ben
Jones
A2
Al
Baker
A3, A4

The problem I am having is a homeowner can and some do own several
lots. How do I set it so, it will find each lot owned?

Thanks

Hans

 

[HKaplan]

I have a workbook with a sheet "Lot Information" where the homeowners
name and lot number is listed:

Column A - Lot #
Column C - Owner Name

"Lot Information Sheet"
Column A -Home Owner
Name                                                 Column B - Lot #
Joe
Smith
A1
Ben
Jones
A2
Al
Baker
A3
Al
Baker
A4

I have another sheet called "Checks Received". What I want is "Checks
Received" sheet to look at the name from "Lot Information" sheet and
give me the lot #(s).

"Checks Received"
Column A-Homeowner
Name                                                            Column
B - Lot#(s)
Joe
Smith
A1
Ben
Jones
A2
Al
Baker
A3, A4

The problem I am having is a homeowner can and some do own several
lots. How do I set it so, it will find each lot owned?

Thanks

Hans

Depending on exactly what you want to accomplish, you can run a pivot
table on the first table to give a list of names in the first column
and each lot they own in the next column. You can store this table as
a manual lookup reference. I can guide you through creating the pivot
table if you need help and this meets your needs.

The pivot table would look something like this:

Bob Smith 102a
104a
105a
Mary Jones 302a
404b
504a

 

[hansjhamm]

Depending on exactly what you want to accomplish, you can run a pivot
table on the first table to give a list of names in the first column
and each lot they own in the next column.  You can store this table as
a manual lookup reference.  I can guide you through creating the pivot
table if you need help and this meets your needs.

The pivot table would look something like this:

Bob Smith       102a
                      104a
                      105a
Mary Jones     302a
                      404b
                      504a- Hide quoted text -

- Show quoted text -

But a pivot table would not place all the lots owned by an owner into
a single cell would it? For example if Bob Smith is in cell A1 and he
owns 3 lots I want all 3 lots howing in cell B1. Is this possible with
a pivot table?


Thanks

 

[HKaplan]

But a pivot table would not place all the lots owned by an owner into
a single cell would it? For example if Bob Smith is in cell A1 and he
owns 3 lots I want all 3 lots howing in cell B1. Is this possible with
a pivot table?

Thanks- Hide quoted text -

- Show quoted text -

You want the lots in a single cell, separated by what? A comma, tab,
hard return, space?

 

[hansjhamm]

You want the lots in a single cell, separated by what?  A comma, tab,
hard return, space?- Hide quoted text -

- Show quoted text -

Seperated by a comma, space would be ideal

 

[HKaplan]

Seperated by a comma, space would be ideal- Hide quoted text -

- Show quoted text -

I believe it would require some VBA code on "worksheet change" (right
click the tab, select view code, and select worksheet and change in
the drop downs on the right) to do a lookup on the lots table and
return each lot in a single field separated by some delimiter, every
time you enter a persons name and check info.

One suggestion, depending on how many names and how many lots each
own, would be to manually create a table with the name in column A and
that persons lot list in column B (manually typed or using the
=concatenate function). Then do a simple vlookup on that table.

Otherwise I don't know of a single formula to extract (from your
table) the list of lots for a specific name and format it in a single
cell.