Inverting a relation in Excel

  • Thread starter Thread starter Michael H. New
  • Start date Start date
M

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
 
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
 
Thanks!! I'll give it a try!

Michael

Biff said:
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
Click to expand...
 
Back
Top