how to merge two columns to one column with the repeating word on column2 as a header

excel merge two tables based on one column
merging two excel spreadsheets based on common field
combine multiple columns in excel into one column
merge two columns in excel
merge two excel spreadsheets with same columns
merge tables wizard
vlookup to merge two excel sheets
merge tables in excel 2016

i've been trying to figure this for a couple of hours now, here's what i need

my table:

 - 161.226.252.55           NY
 - 171.226.252.60           NY
 - 131.226.252.50           NY
 - 150.178.157.16           LA
 - 162.163.137.23           LA
 - 142.163.137.27           WY
 - 182.163.137.22           NL

desired output:

 - NY
 - 161.226.252.55
 - 171.226.252.60
 - 131.226.252.50
 - LA
 - 150.178.157.16 
 - and so on...

sorry for the bad format, it's my first question, thanks

AWK solution using associative array:

awk '{ ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table

Output:

WY
142.163.137.27
NL
182.163.137.22
LA
150.178.157.16
162.163.137.23
NY
161.226.252.55
171.226.252.60
131.226.252.50

In case You need order as in Your provided output:

awk '!ips[$2] { ipso[++order]=$2; } { ips[$2]=ips[$2] $1 RS; } END { for (i=1;i<=order;i++) printf("%s%s%s",ipso[i],RS,ips[ipso[i]]); }' table

Output:

NY
161.226.252.55
171.226.252.60
131.226.252.50
LA
150.178.157.16
162.163.137.23
WY
142.163.137.27
NL
182.163.137.22

How can I merge two or more tables? - Excel, how to merge two columns to one column with the repeating word on column2 as a header the duplicate word from column 2 and sorts beneath it the matching rows in case you have one header just add condition NR>1. Concatenate, Merge or Combine multiple rows into one value - Power Query for Power BI - Duration: 8:41. The Power User 114,000 views

You said you tried gawk/awk, you can create an array, in fact, a hashtable in awk, the key is your 2nd column. The values are those ips. When you put value in it, you check if there is value with that key, if true, append to the existing value. Finally, you can loop through the array, print the keys and values out, it should be in your desired format.

Create tables and lists, Learn how to merge the columns of two or more tables into one table by using Order ID values repeat in the Blue table, but Order ID values in the Orange table are To do this, let's paste the table headings Sales ID and Region into the cells to from column 2 of the Orange table, and the second gets them from column 3. Select the contents of document 1 for the first column and convert it to a table with the number of columns set to 1 and the Separate text at set to Paragraphs. Do the same for document 2. Copy and paste the table from document 1 into a new document. Click the ¶ on the Home tab to show formatting marks.

Could you please try following, it will give you output in same order in which ids are present in Input_file then try following.

awk '
!a[$NF]++{
  b[++count]=$NF
}
{
  c[$NF]=c[$NF]?c[$NF] ORS $1:$1
}
END{
  for(i=1;i<=count;i++){
     print b[i] ORS c[b[i]]
  }
}'   Input_file

How Can I Merge Two Columns of Data Using Microsoft Excel and , Word sorts only the data rows in your table, and not the header row or You can sort the data in a table by multiple columns If headers aren't configured in the table style options, the list entries are (Column 1), (Column 2), and so on. Merge multiple cells into one cell or split one cell into multiple cells. I have a user who wants to create a Word mail merge using an excel data source. Sure that's easy. But what happens when they want to divide the page up into columns and then add the same fields. As it runs through the data source, you get duplicates. So I though if you add the Next Record field, but all that does is make them out of snyc.

Unpivoting SubCategories, There are two situations in which you might need to merge two columns of data I Merge Two Columns of Data Using Microsoft Excel and Eliminate Duplicate As an example, if the data exists in columns A and B, right-click column header  Adjust the size of your columns from the top ruler. You can click and drag the edges of the ruler at the top of your document to change the size of your columns. This is an optional adjustment. If you prefer not to change the column sizes, your columns will be equal in size by default.

How to merge duplicate rows in Excel, I've been able to use it on data with multiple levels of headers without fail. I recently worked on a table with subcategories, repeating columns names Transpose the data; Fill column 1 down; Merge the first two columns with a how do you even know to fill down Column1 and merge it with Column2? Columns B-G have rows 1 and 2 merged (B1+B2), rows 3 and 4 merged (B3+B4), all the way down the columns. Question: How do I get the cells in column A to match the merges in columns B-G and then click and drag the format or repeat it down the column so that A1+A2, A3+A4, etc down a single column (basically merging two rows over and over down a

Accessible Tables in MS Word: MSWord Accessibility, Tables provide a means of presenting complex information in a manner that With a simple table, it is easy to maintain accessibility by simply formatting the header cells at the beginning of columns and for Column 2 On the Layout Sub-Tab (of the Table Tools Tab), in the Data Group, select "Repeat Header Rows". Method 1: Merge Columns In Excel Using Concatenation Formula Firstly, to Insert a new column ‘ Full Name ‘ select the desired column header (in our case it is column D), Right click on it and select ‘ Insert ‘ option. We will rename this column as per requirement, in our case it is ‘ Full Name ‘.

Comments
  • Welcome to SO. Please provide a Minimal, Complete, and Verifiable example. Show us the code for your latest attempt and where you got stuck. and explain why the result is not what you expected. Edit your question to include the code, please don't add it in a comment, as it will probably be unreadable. stackoverflow.com/help/mcve
  • i tried various grep/sort/gawk/awk, the list is great, i'm stuck in the part that brings the duplicate word from column 2 and sorts beneath it the matching rows
  • Please show us your code, if you would like assistance.
  • thanks a lot for the reply, it worked. my bad but i forgot to mention that my columns has titles, and it shows up above the location, how can i remove them and only them?
  • in case you have one header just add condition NR>1. Like: awk ' NR>1 { ips[$2]=ips[$2] $1 RS; } END { for ( i in ips ) printf("%s%s%s",i,RS,ips[i]); }' table_headers
  • This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
  • @hivert this is not a comment. it is exactly an answer to the question, even if it doesn't contain any codes. It explains how to merge two columns to one column with the repeating word on column2 as a header