Transposing Data

[cflorackis]

Dear friends,

I have some problems with transposing my data. Say, for example that we have the following table:

transpose.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Firm	Investor	Stake	Type		Firm	1st largest investor	Type	2nd largest Investor	Type	3nd Largest Investor	Type
2	firm1	john	10	1		firm1	10	1	9	2	8	4
3	firm1	nick	8	4		firm2	55	4	6	6	4	5
4	firm1	helen	9	2		firm3	25	3	6	2
5	firm2	nick	6	6
6	firm2	micael	4	5
7	firm2	jack	55	4
8	firm3	helen	25	3
9	firm3	john	6	2
Sheet1

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

c.

 

[hatman]

Here is one possible solution:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Firm	Investor	Stake	Type				Firm	1stlargestinvestor	Type	2ndlargestInvestor	Type	3ndLargestInvestor	Type
2	firm1	john	10	1		2		firm1	10	1	9	2	8	4
3	firm1	nick	8	4				firm2	55	4	6	6	4	5
4	firm1	helen	9	2				firm3	25	3	6	2	0	0
5	firm2	nick	6	6		5			0	0	0	0	0	0
6	firm2	micael	4	5					0	0	0	0	0	0
7	firm2	jack	55	4					0	0	0	0	0	0
8	firm3	helen	25	3		8			0	0	0	0	0	0
9	firm3	john	6	2					0	0	0	0	0	0
Sheet2

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.

 

[cflorackis]

Your suggestions are extremely helpful. Many thanks to both of you.

c.

 

[cflorackis]

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

 

[hatman]

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?

 

[cflorackis]

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

c.