Excess Column Consolidation

[jldarby]

So, I am trying to consolidate columns of Stata output, but I cannot for the life of me figure out how to do so efficiently.

There are 666 columns of data and 40,000+ rows, but I only need select data from each column based on the group number in Column 1, as seen in the screenshot below.

Additionally, it is not a consistent number of cells from each column.

Is there a function I can use to do this? Or anything to make it simpler? I appreciate any input!

group3		1	2	3
1	-0.7319769	-0.7319769	0.0388026	-0.1950958
1	1.458545	1.458545	0.8811382	0.6414637
1	1.341578	1.341578	2.558916	0.8218915
1	0.0352279	0.0352279	0.3083141	0.1015673
1	0.1477271	0.1477271	0.1990785	0.117075
1	-0.0239062	-0.0239062	-0.0001809	0.0202252
1	-0.0860018	-0.0860018	0.5778255	0.1154793
1	0.7192206	0.7192206	1.307652	0.5013363
1	0.7932782	0.7932782	1.000598	0.4751873
1	-2.321617	-2.321617	-1.195768	-0.81506
1	-1.915917	-1.915917	0.235807	-0.4159741
2	0.9878593	1.75246	0.9878593	0.7301992
2	0.248257	-0.44651	0.248257	-0.0651583
2	-0.6440582	-4.658056	-0.6440582	-0.8872776
2	0.6071137	-0.0629604	0.6071137	0.1285889
2	-0.0126544	-0.4375088	-0.0126544	-0.1172441
2	1.368064	1.335624	1.368064	0.6900043
2	-2.07568	0	-2.07568	-1.625271
2	-1.480671	0	-1.480671	-1.339827
2	-0.7909263	-1.47055	-0.7909263	-0.5671665
2	0.2973338	0.3050405	0.2973338	0.1886359
2	-0.6661274	-2.607719	-0.6661274	-0.7394468
2	-0.0568002	-0.0916928	-0.0568002	-0.0136846
2	0.180681	-0.9019451	0.180681	-0.211238
2	2.313737	1.376234	2.313737	0.8117952
2
3	0.8914	2.358269	1.235061	0.8914
3	1.243444	3.644564	2.739577	1.243444
3	1.312255	3.392379	5.00429	1.312255
3	0.3816218	0.5147745	0.9656394	0.3816218
3	-0.0589167	-0.3078779	0.0697584	-0.0589167
3	1.130784	2.944819	2.378066	1.130784
3	0.1490783	0.2187029	0.2415823	0.1490783
3	-3.131838	0	-6.404656	-3.131838
3	-0.0015069	-0.0716653	-0.0291563	-0.0015069
3	0.574928	1.01567	1.201611	0.574928
3	-0.5341317	-1.377318	-0.7383488	-0.5341317
3	-1.473002	0	-1.766249	-1.473002
3	-0.5346901	-1.786477	-0.346334	-0.5346901
3	-0.9732029	-4.966533	-0.919046	-0.9732029
3	0.6348688	0.9934548	1.669873	0.6348688
3	-0.0912796	-0.6875041	0.4643932	-0.0912796

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[alansidman]

I'm not understanding what you want to happen. Please explain with clear examples of what you want. Looking at your screen shot, I cannot envision your expected results. Please clarify with specific examples.

crosspost: http://www.excelforum.com/excel-formulas-and-functions/1160740-consolidating-excessive-columns.html

 

[jldarby]

Sorry, my screenshot didn't come through correctly with the color-coding The unlabeled column is the expected results. The first 11 cells in the unlabeled column correspond to the 11 cells in the column labeled 1 (in the group3 column, "group" 1 corresponds to column 1. The next 15 cells in the unlabeled column correspond to the 15 cells in the column labeled 2, and so forth.

 

[Joe4]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.