Is there a way to consolidate similar (but not the same) rows in a text file?

how to combine multiple rows into one based on duplicates in excel?
excel combine rows with same id
how to merge rows in excel
excel combine rows with same value
combine corresponding rows based on duplicate values in another column
convert multiple rows to single row in excel
merge rows in excel without losing data
ablebits

I have a text file on a linux box that has two columns. 1. An IP address 2. A code for a location

Some IP addresses are listed more than once because more than one code is associated with it.

Example:

140.90.218.62 vaac
140.90.220.11 aawu
140.90.220.11 afc
140.90.220.11 arh
140.90.220.40 afc

I would like to consolidate such IP addresses to only be listed once, just with several location codes

Like this

140.90.218.62 vaac
140.90.220.11 aawu:afc:arh
140.90.220.40 afc

I could always code a for loop to read in the file, consolidate the values into an array, and write the cleaned up version back out.

Before I do that I was wonder if a combination of *nix utilities might do the job, do it with less code, etc.


Practical Perforce, How Perforce Merges Text Files Any two files can be compared to reveal diffs. Three files are necessary to merge content: a starting version and two modified a three-way merge that operates on lines of text, not on characters, words, these rules: • When a chunk is the same in all three files, it goes into the merged file. Later, select the range which you want to consolidate and click button. After that, click “Add” button in “Consolidate” dialog. Subsequently, check the “Top row” and “Left column” option. Finally, click “OK” button. At once, the rows are consolidated, as shown in the following screenshot.


bash version 4, with associative arrays.

declare -A data
while read -r ip value; do
    data[$ip]+=":$value"
done < file

for key in "${!data[@]}"; do 
    printf "%s %s\n" "$key" "${data[$key]#:}"
done

How to merge rows in Excel without losing data, 4 different ways to combine rows in Excel: merge multiple rows The problem is that Microsoft Excel does not provide a reliable tool to do In a moment, all the data from duplicate rows are merged into one row: Let's name it BlockID, or whatever name you like. (and do not delete the text: "blablabla". There are a number of ways to summarize Excel data, but Consolidate is one of the easiest to implement and the results are easy to use. Excel's Consolidate feature's claim to fame is merging and


With perl:

perl -lanE 'push @{$ips{$F[0]}}, $F[1]; END { $" = ":"; say "$_ @{$ips{$_}}" for sort keys %ips }' yourfile.txt

outputs

140.90.218.62 vaac
140.90.220.11 aawu:afc:arh
140.90.220.40 afc

Elements of Programming Interviews in Java: The Insider's Guide, With line-based merging of text files, common text lines can be detected in parallel merging is that it cannot handle two parallel modifications to the same line: the A three-way, line-based merge tool in practice will merge 90% of the changed files Text-based merging fails because it does not consider any syntactic or  Select the rows you will combine, and click Kutools > Content > Advanced Combine Rows. See screenshot: 2. In the Advanced Combine Rows dialog box, please select the ID or name column you will combine by, click Primary Key, and then specify combination rules for other columns as you need.


How to merge two Excel Worksheets by partial cell , You have two Excel worksheets with the data you want to merge and analyze. partial text match in your key columns, e.g. "12345" and "12345-newsuffix". same, but the computers are not as smart :-( This non-exact match makes it You can adapt them for further use in any standard Excel formula like  Merge adjacent rows of same data with Kutools for Excel With the Merge same cells utility of Kutools for Excel , you can quickly merge the same values in multiple columns with one click. Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days .


Filter for unique values or remove duplicate values, Filter for unique values, remove duplicate values, and conditionally format unique or duplicate values in Excel. Insert or delete rows, and columns Article · Split text into different columns with the Convert Text to Columns Wizard In Excel, there are several ways to filter for unique values—or remove duplicate values:. If you want to merge sheets in Excel by copying their data, the consolidation option is not the way to go. To combine just a couple of sheets, you may not need anything else but the good old copy/paste.


Combine data from multiple sheets, If the sheets use the same row and column labels for their categories, even if the data is not in consistent positions, consolidate by category. The file path is entered in All references. When you have added How can we improve? Send It sounds like it might be helpful to connect you to one of our Office support agents. Combine similar records in Access Query Our Records Management System outputs records in an annoying way, creating multiple rows for what should be one record. I am trying to combine similar records so that they are all in one row.