Counting and then concatenating

[JorgeVazquez]

Hi all,

Hope someone can help me with the following.

I have this type of outcome:

GN0030 | GN0030 | GN0030 | GN0030 | GN0030 | GN0030 | XX51500 | XX51500

CB0000 | DP0063 | MN0000 | XHT | XHT | XHT | XHT | XX51500 | XX51500

CB0000 | CB0000 | CB0000 | GN0125 | XX51500 | XX51500

I want to count and concatenate back the result so the lines would look like this:

6 x GN0030 | 2 x XX51500

1 x CB0000 | 1 x DP0063 | 1 x MN0000 | 4 x XHT | 2 x XX51500

3 x CB0000 | 1 x GN0125 | 2 x XX51500

Any ideas on how can I get this done?

Thanks!

[Federica_FF · Accepted solution]

Hi!

What is exactly the pipe/vertical line in your input and what is your input?

It's an excel file and the vertical line represent where a new column start?

Or you have a singlestring with value separated by space|space?

A way could be to use a record ID to give a unique ID to each record/line and then split to rows each value (or transpose if it's an excel file, it depends on your input) so that you have 1 value per row. Then you can easly summarize the values groupng by record ID and counting. With a formula tool is then possible to concatenate values and count adding all the texts you need and then cross tab the final result.

See the example attached

[RodL]

Assuming that the lines and order are significant, you could first assign a Record ID to each line.

Then...

• Text to Columns (Split to Rows) with Pipe Delimiter
• Summarize grouping by RecordID and your identifier with a count.
• A Formula to concatenate the count, then an "x", and then your identifier.
• A Summarize tool grouped by the RecordID set to concatenate with a pipe delimiter.

That should get you close...

[JorgeVazquez]

Hi RodL,

Thank you for your answer.

This was very helpful.

Thanks!

[JorgeVazquez]

Hola Federica,

Thank you so much for this.

I got it! Your model helped me to solve my query.

The pipes where just spaces in my data. Everything is in one column but I have that information in separate rows and with unique IDs, so I could follow your example and get it done!

Thank you for taking the time to answer.