0

I have a spreadsheet that includes separate data columns for clients and the bonds that they hold.

In following columns are the dates on which those bonds will pay interest in the future and (i.e. payment date 1, payment date 2, etc.) and the actual amount of interest to be received (i.e. payment amount 1, payment amount 2, etc.)

I would like to create a pivot table that lists the clients and their individual holdings of bonds in columns. Along the top I would like to have a list of all future interest dates and the body of the table would include the amounts to be received on those dates. I have included a simple example below.

Client A     June 30   July 31   August 31  September 30
  Bond 1     1,000                           1,000
  Bond 2     1,000     1,000     1,000       1,000

Client B
   Bond 2    2,000     2,000      2,000      2,000
   Bond 3              1,000      1,000

I can easily set-up the columns on the left but I am unable to create a header row that provides the simple picture above. Help!?

Thank you.

Phil

  • 1
    The " simple example below" .. is the raw input data or the desired output? (I think others need both to replicate your case..) – p._phidot_ Jun 20 '19 at 0:13
  • Client Bond Date 1 Date 2 Date 3 Date 4 Pmt 1 Pmt 2 Pmt 3 Pmt 4 A 1 6/30 9/30 1000 1000 A 2 6/30 7/31 8/31 9/30 1000 1000 1000 1000 B 2 6/30 7/31 8/31 9/30 2000 2000 2000 2000 B 3 7/31 8/31 1000 1000 – PDL14 Jun 20 '19 at 12:55
  • The above is a representation of the desired output. The source data with headers would appear as follows.. – PDL14 Jun 20 '19 at 12:55
  • I'm sorry the data input view did not present well. The picture at the top is my representation of the desired output. However, the data I have exists currently in columns. The data for Client A, bond 1 would be shown in distinct columns. I will include commas as the delimiters. Client A, bond 1, June 30, Sep 30, , , 1000, 1000, , , This data would correspond to the column headers: Client, Bond, PayDate1, PayDate2, PayDate3, PayDate4, Pmnt1, Pmnt2, Pmnt3, Pmnt4 – PDL14 Jun 20 '19 at 18:21
  • 1
    please edit the question and include the additional info. (you can highlight the pasted data (during editing) , then press Ctrl+K to convert it into the 'grayed'/code format as above) – p._phidot_ Jun 21 '19 at 0:39
0

I can easily solve the problem about PIVOT but I have not understood the requirements exactly. I also dont know whether I can send you the Excel File through e-mail or not. As I am not aware about this. Thanks.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.