0

I have a table like below in excel spread sheet

A   1
A   2
B   12
B   4
B   56
B   68
C   7
C   8
C   34
D   10
D   11

i need to convert the table as below First entry Second entry Third entry Fourth entry 

A   1   2   -   -
B   12  4   56  68
C   7   8   34  
D   10  11
auth private
  • 1,318
  • 1
  • 9
  • 22
ganadhar vennapusa
  • 1
  • 1
  • Can you make more clear the data you are providing as an example? – arodrisa Jul 17 '15 at 11:09
  • Table contains 2 columns- 1st column has entries A,A,B,B,B,B,C,C,C,D,D and second column has their corresponding values 1,2,12,4,56,68,7,8,34,10,11 . I have to convert the table to another table where 1st column has unique values (A,B,C,D) and their values as corresponding columns. If A has values (1,2) in first table, in second table it should look like A 1 2 – ganadhar vennapusa Jul 17 '15 at 11:11
  • And the result you want to obtain? – arodrisa Jul 17 '15 at 11:13
  • I mentioned the result to be obtained as second table A 1 2 B 12 4 56 68 C 7 8 34 D 10 11 – ganadhar vennapusa Jul 17 '15 at 11:17
  • There are a few ways you can do it. Similar question here with some of them. http://stackoverflow.com/q/15754501/212869 – NickSlash Jul 17 '15 at 11:40
  • 1
    see [Aggregate, Collate and Transpose rows into columns](http://stackoverflow.com/questions/29440349/aggregate-collate-and-transpose-rows-into-columns). There are many others. This question gets asked in one form or another a couple of times a week. –  Jul 17 '15 at 12:39

1 Answers1

0

Provided your list of letters (assumed to be in ColumnA starting in Row1) is sorted and you have a separate list of just one each of those letters (say starting in D1) then a formula may achieve the results you want: 

=IF(COUNTIF($A:$A,$D1)>COLUMN()-5,OFFSET(INDEX($A:$A,MATCH($D1,$A:$A,0)),COLUMN()-5,1),"")

In F1, copied down to suit and then all formulae copied across until an entire column is blank.

pnuts
  • 58,317
  • 11
  • 87
  • 139