ExcelBanter - Compare columns and display the value

On Mon, 29 Dec 2008 00:55:00 -0800, Raj
wrote:

Hi All

I have pulled data from 2 different reports here is the Data
Report 1 :
Ticket Priority
123 P1
124 P1
125 P3
126 P1

Report 2:
Ticket Time
123 3
128 4
127 3
124 5

Now i want build a 3 report where the Ticket has to compare and display in
the below format If the ticket is avialable in Report 1 and not in Report 2
then the Report 3 has to display only Ticket and Priority.

Ticket Priority Time

Is it possible to get the data in Excel please help me

If this is your expected output for your given example

Ticket Priority Time
123 P1 3
124 P1 5
125 P3
126 P1
127 3
128 4

you may try the following formulas.

Assuming that
- your ticket/prioirty are in columns A and B
- your priorities always start with a letter (not a digit)
- your ticket/time are in columns C and D
- row 1 is used for headers, data starts in row 2

In cell E1 put the header Ticket
In cell F1 put the header Priority
In cell G1 put the header Time

In cell E2 put the formula
=SMALL(A$2:C$10,1)

In cell E3 put the formula
=SMALL(A$2:C$10,1+SUMPRODUCT(--(TRANSPOSE(E$2:E2)=A$2:A$10))+SUMPRODUCT(--(TRANSPOSE(E$2:E2)=C$2:C$10)))
Note: This is an array formula that must be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Copy cell E3 down as far as needed

In cell F2 put the formula
=IF(ISERROR(VLOOKUP(E2,A$2:B$10,2,FALSE)),"",VLOOK UP(E2,A$2:B$10,2,FALSE))
In cell G2 put the formula
=IF(ISERROR(VLOOKUP(E2,C$2:D$10,2,FALSE)),"",VLOOK UP(E2,C$2:D$10,2,FALSE))

Copy cells F2:G2 down as far as needed.

Change $10 in all places to fit the size of your data in rows A to D.

Hope this helps / Lars-Åke