Hi hello_beginner,

I agree with hippo, this question is very similar to join column 3 for unique values. In fact, a few changes to the code I posted in Re: join column 3 for unique values gives the results that you require.

If you have your data in data.txt

A1 text1 NY Jan 01
A2 text2 LN Feb 02
A3 text3 SG Mar 03
A2 text2 NY Feb 02
A1 text1 SG Jan 01
A1 text1 AUS Jan 01
[download]

This script,

#!/usr/bin/env perl

use strict;
use warnings;

use Data::Table;

my $t = Data::Table::fromCSV('data.txt', 0, undef,{delimiter => ' '});
my $mt = $t->melt(['col1', 'col2', 'col4', 'col5']);
my $ct = $mt->cast(['col1', 'col2', 'col4', 'col5'], 'variable', Data:
+:Table::STRING, 'value', \&col_join);

$ct->sort('col1', 1, 0);
$ct->reorder( [ 'col1', 'col2', 'col3', 'col4', 'col5' ] );

print $ct->csv(0, {delimiter => " "});

exit;

sub col_join {
    my @data = @_;
    my $joined = join(":", @data);
    return($joined);
}
[download]

will give this output

A1 text1 NY:SG:AUS Jan 01
A2 text2 LN:NY Feb 02
A3 text3 SG Mar 03
[download]

If you often work on problems like these, I recommend reading Data::Table and the Data::Table Cookbook (especially Section 8.1: Reshaping - melting and casting for table statistics).

As mentioned by stevieb, please take a closer look at How do I post a question effectively? especially the Select an informative title section.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.