how to pivot the table

I have the below table: Table1:

Index  GroupName  RcpName
1  A          pr.vf001K
2  C          pr.vb002K
3  D          pr.vk003K
4  E          pr.vb004K
5  A          pr.vb005K
6  B          pr.vf006K
7  C          pr.va007K
8  D          pr.vb008K
9  F          pr.vb009K
10  C          pr.vb007K
11  E          pr.vg010K
12  B          pr.vf006K
13  A          pr.vf001K
14  D          pr.vb013K
15  E          pr.vb004K
16  C          pr.vb007K
17  B          pr.vf09K
18  D          pr.vb013K
19  B          pr.vb19K
20  B          pr.vf006K
21  E          pr.vb004K
22  F          pr.vb023K
23  D          pr.vb013K
24  E          pr.vb010K
25  A          Tv_pr12k_mm_ty004.vg
26  G          Tv_pr.vf22k_ff01_hy004

Step1: I want to break the RcpNames at pr., and second '_' (underscore): after performing this, the above table become as below:

Table2:

Index  GroupName  RcpName
1  A  vf001K
2  C  vb002K
3  D  vk003K
4  E  vb004K
5  A  vb005K
6  B  vf006K
7  C  va007K
8  D  vb008K
9  F  vb009K
10  C  vb007K
11  E  vg010K
12  B  vf006K
13  A  vf001K
14  D  vb013K
15  E  vb004K
16  C  vb007K
17  B  vf09K
18  D  vb013K
19  B  vb19K
20  B  vf006K
21  E  vb004K
22  F  vb023K
23  D  vb013K
24  E  vb010K
25  A  pr12k_mm
26  G  vf22k_ff01

I want to pivote this table, i.e Table2(pivoting to get unique RcpNames), and get the maximum index of for each RcpName (after pivoting,now only exist unique RcpNames, so duplicacy of Indexes) , and get the corresponding GroupName based on index (here I use vlookup in excel).

Finally I want the output as below:

A  25  pr12k_mm
C  7  va007K
C  2  vb002K
E  21  vb004K
A  5  vb005K
C  16  vb007K
D  8  vb008K
F  9  vb009K
E  24  vb010K
D  23  vb013K
F  22  vb023K
B  19  vb19K
A  13  vf001K
B  20  vf006K
B  17  vf09K
G  26  vf22k_ff01
E  11  vg010K
D  3  vk003K

Please kindly help on this, many many thanks in advance.

[Sivakumaran Chandrasekaran]

you can break after pr.

i am not sure about removal of underscore

Answer 1

[Peter Perkins]

A much shorter example would make your question easier to understand.

I don't think this has anything to do with "pivoting" in the usual sense. It seems to me that you want to do two things:

1) Strip off leading and trailing parts of strings. You can use strsplit or rexexprep for that.

2) Find the last occurrence of eah unique value. You can use the second output of unique for that.

Hope this helps.

[Kanakaiah Jakkula]

Sir, How can I able to find the last occurance of each unique. Thanks.

[Sean de Wolski]

[uv,idxlast] = unique(T.RCP,'legacy')

[Kanakaiah Jakkula]

Sir,

I also want to store the group name for each corresponding RcpName (which is unique, and lastly occured). it giving some error:

??? Error using ==> cell.unique at 29 Unrecognized option.

Error in ==> Pivot at 12 [uv,idxlast] = unique(T.RCP,'legacy'); Sincerely,

[Peter Perkins]

See the doc for unique. You're looking for the second output in Sean's example. BTW, rather than 'legacy', I'd suggest 'last' (although either would work):

      [C,IA,IC] = unique(A,'rows',OCCURRENCE) specify which index is returned
      in IA in the case of repeated values (or rows) in A. The default value
      is OCCURENCE = 'first', which returns the index of the first occurrence 
      of each repeated value (or row) in A, while OCCURRENCE = 'last' returns
      the index of the last occurrence of each repeated value (or row) in A.