Collate all data from Ticket Numbers in Row A

[JonnyStout]

Hi,

I have a report which has ticket numbers details in column A and then the details in B to N.
This is the user, description etc.
Column A has multiple instances of the same ticket number where the ticket was updated and I want to take any information in the other columns and put it together based on ticket number (column A)
The most important information I want to get is in column I which has the updates in it.
I'm looking to get all the information if possible, or just put the information from column I together

For example:

123456 Carol 01/01/2018 Can I get my brakes fixed?
123456 Jim 02/01/2018 Can you give me your phone number?
123456 Carol 03/01/2018 No problem Jim it's 12345678
987654 Tim 01/01/2018 I need a replacement toner
987654 Jane 02/01/20118 What's your printer ID?
987654 Tim 03/04/2018 It's QFGLTO01

I would like it to be:

123456 Can I get my brakes fixed? Can you give me your phone number? No problem Jim it's 12345678
987654 I need a replacement toner. What's your printer ID? It's QFGLTO01

OR

123456 Carol 01/01/2018 Can I get my brakes fixed? Jim 02/01/2018 Can you give me your phone number? Carol 03/01/2018 No problem Jim it's 12345678

 

[stunnrock]

One way.

In P1:

=MATCH(TRUE,INDEX(ISBLANK($A$1:$A$500),),0)-1

In Q1:

=A1

In Q2 (then copied down as far as required):

=IF(SUMPRODUCT(COUNTIF(Q$1:Q1,$A$1:$A$500))<$P$1,INDEX($A$1:$A$500,MATCH(TRUE,INDEX(ISNA(MATCH($A$1:$A$500,Q$1:Q1,0)),),0)),"-")

In R2 (then copied along and down as far as required):

=IFERROR(INDEX($I$1:$I$500,SMALL(INDEX(ROW($A$1:$A$500)+(ROWS($A$1:$A$500)*($A$1:$A$500<>$Q2)),),COLUMNS($R:R))),"-")

 

[JonnyStout]

Hi, thanks for your response.
When I enter the code into P1 it returns an #N/A
Q1 returns A1 which is fine.
Q2 Then returns a #N/A
R2 returns a "-"
If I copy Q2 down then it returns an #N/A in all the cells.

[TABLE="width: 264"]
<tbody>[TR]
[TD="width: 88, align: center"]#N/A[/TD]
[TD="width: 88, align: right"]890128[/TD]
[TD="width: 88"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[stunnrock]

Hi, sorry, the test data I set up had a header at the top and did not extend as far as row 500 (the formula in P1 was to count the used rows, and Q1 was to make sure that the header was identical). So, slight change:

In P1:

=IFERROR(MATCH(TRUE,INDEX(ISBLANK($A$1:$A$500),),0)-1,ROWS($A$1:$A$500))

Leave everything else the same, but copy R2:x2 up to R1:x1 (x being whatever column you extended to).