Inverting a relation in Excel

[Michael H. New]

Is there any way to invert a relation in Excel? What I mean is, given
the following type of table:

Ticket_Num Responder
1001 Bob
1002 Ted
1003 Alice
1004 Ted
1005 Bob

how can I generate a table like:

Responder Ticket1 Ticket2 Ticket3
Alice 1003
Bob 1001 1005
Ted 1002 1004

In practice, I know a priori the maximum number of "tickets" a responder
can have.

Thanks for the help!

Michael

[Biff]

Hi!

It'll take a few steps but it's a "breeze".

Assume your original table is on Sheet1 in the range A1:B6.

You want the new table on Sheet2 starting in cell A1 with the header:
Responder

Navigate to Sheet2 A1.

Goto Data>Filter>Advanced Filter.
Select: Copy to another location
Put the cursor in: List Range then navigate to Sheet1 and select the column
header Responder and the range of names
Put the cursor in: Copy To and enter A1
Select: Unique Records Only
Click OK

That will copy the list of unique names and the header to Sheet2.

Now sort the names ascending.

Put you other headers in: Ticket1, Ticket2, etc.

Enter this formula in Sheet2 B2 using the key combo of CTRL,SHIFT,ENTER:

=INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A2,ROW($1:$5)),COLUMN(A:A)))

Drag copy across to the last column that has a Ticketn header then down as
long as the list of names extends in column A.

Any name that doesn't have a corresponding entry for Ticketn will return a
#NUM! error. You can hide them using conditonal formatting or account for
them in the formula itself and return blanks or, if this is going to be a
one time conversion to a static table, you can just convert the formulas to
constants and delete all the #NUM!'s.

Note in the formula this expression: ROW($1:$5)

This refers to the size of the range in B2:B6. For accurate results the ROW
reference must start with 1 and end with the total size of your actual
range. If the actual range was B100:B104 the ROW reference would still be
1:5.

Biff

"Michael H. New" <[email protected]> wrote in message
news:[email protected]...
> Is there any way to invert a relation in Excel? What I mean is, given
> the following type of table:
>
> Ticket_Num Responder
> 1001 Bob
> 1002 Ted
> 1003 Alice
> 1004 Ted
> 1005 Bob
>
> how can I generate a table like:
>
> Responder Ticket1 Ticket2 Ticket3
> Alice 1003
> Bob 1001 1005
> Ted 1002 1004
>
> In practice, I know a priori the maximum number of "tickets" a responder
> can have.
>
> Thanks for the help!
>
> Michael

[Michael H. New]

Thanks!! I'll give it a try!

Michael

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> Hi!
>
> It'll take a few steps but it's a "breeze".
>
> Assume your original table is on Sheet1 in the range A1:B6.
>
> You want the new table on Sheet2 starting in cell A1 with the header:
> Responder
>
> Navigate to Sheet2 A1.
>
> Goto Data>Filter>Advanced Filter.
> Select: Copy to another location
> Put the cursor in: List Range then navigate to Sheet1 and select the column
> header Responder and the range of names
> Put the cursor in: Copy To and enter A1
> Select: Unique Records Only
> Click OK
>
> That will copy the list of unique names and the header to Sheet2.
>
> Now sort the names ascending.
>
> Put you other headers in: Ticket1, Ticket2, etc.
>
> Enter this formula in Sheet2 B2 using the key combo of CTRL,SHIFT,ENTER:
>
> =INDEX(Sheet1!$A$2:$A$6,SMALL(IF(Sheet1!$B$2:$B$6=$A2,ROW($1:$5)),COLUMN(A:A))
> )
>
> Drag copy across to the last column that has a Ticketn header then down as
> long as the list of names extends in column A.
>
> Any name that doesn't have a corresponding entry for Ticketn will return a
> #NUM! error. You can hide them using conditonal formatting or account for
> them in the formula itself and return blanks or, if this is going to be a
> one time conversion to a static table, you can just convert the formulas to
> constants and delete all the #NUM!'s.
>
> Note in the formula this expression: ROW($1:$5)
>
> This refers to the size of the range in B2:B6. For accurate results the ROW
> reference must start with 1 and end with the total size of your actual
> range. If the actual range was B100:B104 the ROW reference would still be
> 1:5.
>
> Biff
>
> "Michael H. New" <[email protected]> wrote in message
> news:[email protected]...
> > Is there any way to invert a relation in Excel? What I mean is, given
> > the following type of table:
> >
> > Ticket_Num Responder
> > 1001 Bob
> > 1002 Ted
> > 1003 Alice
> > 1004 Ted
> > 1005 Bob
> >
> > how can I generate a table like:
> >
> > Responder Ticket1 Ticket2 Ticket3
> > Alice 1003
> > Bob 1001 1005
> > Ted 1002 1004
> >
> > In practice, I know a priori the maximum number of "tickets" a responder
> > can have.
> >
> > Thanks for the help!
> >
> > Michael