Collate all data from Ticket Numbers in Row A

JonnyStout

New Member
Joined
Nov 19, 2009
Messages
10
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

stunnrock

Board Regular
Joined
Dec 29, 2017
Messages
63
One way.

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

In Q1:
Code:
=A1

In Q2 (then copied down as far as required):
Code:
=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):
Code:
=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

New Member
Joined
Nov 19, 2009
Messages
10
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.

#N/A890128
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-
#N/A-

<tbody>
</tbody>
 

stunnrock

Board Regular
Joined
Dec 29, 2017
Messages
63
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:
Code:
=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).
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top