Covert flat column into multiple columns

Question

I have a sheet with data in this format:

ID  TYPE    FEED    SUB-F   START_TIME  KEY         VALUE
1   BMMM    sc11    sc11-1  7:32:43 AM  VAL_MSTIME  1382702237170
1   BMMM    sc11    sc11-1  7:32:43 AM  VAL_AMSUBMI 26345
1   BMMM    sc11    sc11-1  7:32:43 AM  VAL_AMCOMP  26345
2   BMMM    sc22    sc22-2  10:32:43 AM VAL_MSTIME  1382665563434
2   BMMM    sc22    sc22-2  10:32:43 AM VAL_AMSUBMI 26345
2   BMMM    sc22    sc22-2  10:32:43 AM VAL_AMCOMP  26345
3   BMMM    sc33    sc33-3  1:32:43 PM  VAL_MSTIME  1382700749449
3   BMMM    sc33    sc33-3  1:32:43 PM  VAL_AMSUBMI 26345
3   BMMM    sc33    sc33-3  1:32:43 PM  VAL_AMCOMP  26345

I want to convert the key column into separated columns. Only three keys are given. The result should look like this:

ID  TYPE    FEED    SUB-F   START_TIME  VAL_MSTIME  VAL_AMSUBMI VAL_AMCOMP
1   BMMM    sc11    sc11-1  7:32:43 AM  1382702237170   26345   26345
2   BMMM    sc22    sc22-2  10:32:43 AM 1382665563434   26857   26857
3   BMMM    sc32    sc33-3  1:32:43 PM  1382700749449   26876   26876

How might I accomplish this using Excel formulae, or VBA?

Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) — Siddharth Rout, Oct 28 '13 at 19:15
Brut force method would be two loops. Outside loops thru Sub-f column and inside loop through key. — Automate This, Oct 28 '13 at 19:36
@SiddharthRout Where were you Mr. Police Officer when [link](http://stackoverflow.com/questions/12962828/excel-transpose-some-rows-into-columns-depending-on-values-in-another-column) question was posted? I am posting this question because I don't know how to do it. As a matter of fact I am not asking for code although it'd definitely help. — locorecto, Oct 28 '13 at 19:39
I honestly don't agree with the close reason here. I find that the documentation for Excel functions is pretty bad, or at least hard to find and it looks like you put at least some work into making the question clear. [First get the unique values from the ID column](http://stackoverflow.com/questions/13121624/list-distinct-values-in-excel). The rest seems to be answered by [this](http://spreadsheets.about.com/od/excel2010functions/qt/2010-04-26-excel-2010-max-function.htm). I think your best bet is asking this on SuperUser instead. — Sumurai8, Oct 29 '13 at 16:27

score 3 · Answer 1 · answered Oct 28 '13 at 19:35

It may be easy without code (so hopefully your question will NOT be closed) as you do say excel or vba.

Copy your Value column twice on the immediate right. Delete top cell from first extra column and top two cells from second. Copy one set of key labels and transpose into KEY. Add something that counts in threes (eg =MOD(ROW(),3)) and copy that down. Filter on that column to select 0 and 1 and delete selected rows. Delete KEY column and added count column.

Note that the answer is not as shown above, given the input as shown above.

Covert flat column into multiple columns

1 Answers1