Populating Cells with Table Data

B

Brett

Hi everyone. Hopefully I explain what I am trying to do clearly.
Using a formula (I'd rather not write a macro for this), I'd like to
be able to do the following:

I have a table, Table1, where the column headings people's names
(let's call this group of people PeopleA), and along the side an
abbreviation (what the abbreviation is for is meaningless).

In another table, Table2, I have a list of PeopleA (this repeats every
so often), a unique person next to each instance of PeopleA (let's
call this group of people PeopleB), and a corresponding abbreviation
for each of PeopleB.

I am looking to populate the cells of Table1 with the PeopleB data
from Table2. However, there can be overlap between the abbreviations
of PeopleB such that if the spot is already taken in Table1 by someone
from PeopleB that would correspond to the abbreviation/PeopleA, the
name from PeopleB would move to a separate section of Table1.

OK, maybe this would be more clear with an example. Let's say Table1
was as such:

2 Columns: John, Mary
6 Rows: AB, CD, EF, GH, GH, GH

Now, in Table2, there will be lines that would read:
John Susie AB
Mary Billy CD
John Henry AB

What I'm looking to do in Table1 is to take "Susie" and put it in the
cell corresponding to the John/AB coordinates. The same for Billy in
the Mary/CD coordinates. When I get to Henry, I'd like to put him in
the first available John/GH coordinates.

Is this possible? Is this clear? Thanks for reading this far, and
let me know if anyone out there needs more information!

Thanks!
 
D

Don Guillett Excel MVP

Hi everyone.  Hopefully I explain what I am trying to do clearly.
Using a formula (I'd rather not write a macro for this), I'd like to
be able to do the following:

I have a table, Table1, where the column headings people's names
(let's call this group of people PeopleA), and along the side an
abbreviation (what the abbreviation is for is meaningless).

In another table, Table2, I have a list of PeopleA (this repeats every
so often), a unique person next to each instance of PeopleA (let's
call this group of people PeopleB), and a corresponding abbreviation
for each of PeopleB.

I am looking to populate the cells of Table1 with the PeopleB data
from Table2.  However, there can be overlap between the abbreviations
of PeopleB such that if the spot is already taken in Table1 by someone
from PeopleB that would correspond to the abbreviation/PeopleA, the
name from PeopleB would move to a separate section of Table1.

OK, maybe this would be more clear with an example.  Let's say Table1
was as such:

2 Columns: John, Mary
6 Rows: AB, CD, EF, GH, GH, GH

Now, in Table2, there will be lines that would read:
John   Susie   AB
Mary   Billy    CD
John   Henry   AB

What I'm looking to do in Table1 is to take "Susie" and put it in the
cell corresponding to the John/AB coordinates.  The same for Billy in
the Mary/CD coordinates.  When I get to Henry, I'd like to put him in
the first available John/GH coordinates.

Is this possible?  Is this clear?  Thanks for reading this far, and
let me know if anyone out there needs more information!

Thanks!

You can probably do this using OFFSET and INDEX with MATCH
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top