Transposing Data


Board Regular
Aug 18, 2002
Dear friends,

I have some problems with transposing my data. Say, for example that we have the following table:
1FirmInvestorStakeTypeFirm1st largest investorType2nd largest InvestorType3nd Largest InvestorType

Column A reports the firm's name, column B the names of the major ivestors within the firm (e.g. firm1 has 3 major investors--john, nick and helen), column C the stake of each investor and column D the investor type. Instead of having the data in such a format (i.e. columns A to D), I need to find a way and transpose the data and make them look as in columns F to L. For example I want in column F the company name to be reported, in columns G and H the stake and the type of the 1st largest investor respectively, in columns I and J those of the second largest and in columns K and L those of the third largest investor and so on. Please be aware that one investor can hold shares in more than one companies (e.g. John has invested in both firm1 and firm3)

I have been trying to find a formula to transpose the data since early this morning but I have not been successfull so far

Any help would be greatly appreciated


Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Here is one possible solution:
3firm1nick84 firm25546645
4firm1helen92 firm32536200
5firm2nick665 000000
6firm2micael45  000000
7firm2jack554  000000
8firm3helen2538 000000
9firm3john62  000000

Column G is used to determine which firm names are unique, and it can be hidden from view (if you choose to retain the dynamic formulas and do not convert everything to values).

Note the anchors in all formulas, they are important so you can copy and paste the blocks of information. In other words, when built properly, you should be able to enter the formulas in cells J2 and K2, and fill down as far as needed, then copy and paste J:K to L:M, N:O, etc, as needed for the number of investors.

It Adjust ranges as needed, but do not set the range in columns J or K to the full column (in other words, B:B is NOT acceptable and wil result in an error).

The formula in cell J2 is an Array formula, enter it as =LARGE(IF($I2=$B$2:$B$9,$D$2:$D$9,0),INT(COLUMN(B:B)/2)), then press Ctrl+Shift+Enter rather than just enter.

Let me know how you make out.
Upvote 0
There was a reply 10 minutes ago that just dissapeared???????????

Did I do anything wromng so that teh extra reply was deleted?????????

please help because the suggestion deleted was really important for my work
Upvote 0
My assumption is that NBVCS (I think that us the order of the letters in his name) realized that his solution was giving some erroneous results, and deleted it rather than re-posting a correction.

In essence, his solution was similar to mine, except he used a CSE formula for the Type cilumn, rather than the sumproduct that I did... the techniques are functionally interchangeable, though Sumproduct() is less processor intensive in this case. He also chose to perform a test to suppress zeroes in the results, which I decided not to do to conserve system resources: instead, zeroes can be globally suppressed on the sheet by selecting Tools->Options->View and then de-selecting Dsiplay Zeroes.

Edit: Is there something about my solution that does not work for you, that perhaps I could tweak for a more desireable result or presentation?
Upvote 0
Dear Hatman,

your formulas work perfectly and many thanks for your help. You really just made my life much easier today.

I am learning so many things by this forum and the reason why I wanted to have NBVCS's reply is just to familirize myself with alternative approaches for sorting different problems. I see now what he did and, of course, I would agree with you about the advantages of the SUMPDODUCT function. I will let you know if there are any problems with the formulas in the future

Many thanks again, it is always great to receive help from such clever guys like you

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back