Flatten or Collapse Excel Multi-column Data Into One Dimension

You can flatten or collapse multi-column Excel data into one row or column using GnuWin utilities.

1. Copy the worksheet data into the clipboard.
2. Open a CMD window and enter this chain of commands: getclip | tr -s [:cntrl:] \n | putclip.
3. Paste the data back into your worksheet. You should get a column of data.

The getclip-putclip pair of programs gets and puts data in the system clipboard and is part of the CygWin package in GnuWin. tr translates control characters (e.g. TABs) to NEWLINEs and the -s option squeezes out repeated characters. This chain of commands works because each column of Excel data separated by a TAB character in the clipboard. Here are more examples of this pattern.

If you want to remove duplicates in your data, insert sort -u into the chain: getclip | tr -s [:cntrl:] \n | sort -u | putclip. For example, if you start with this input (note the trailing TABs in rows 1 and 2) ...

1  
2 4 
3 5 7
4 6 8
5 7 9

... you end up with the following:

1
2
3
4
5
6
7
8
9

If you want a single row output, replace all control characters with TABs: getclip | tr -s [:cntrl:] \t | putclip. In this case, you cannot include sort because it sorts lines of data and there is only one line in the output.

1 2 4 3 5 7 4 6 8 5 7 9