CODEWITHSUNDEEP
excelexcel-formulatransposevba
Lakis Juzwa
Lakis Juzwa

Reputation: 13

Transpose multiple rows into columns

I've came across this task and I'm stuck big time. I've tried a PivotTable but it didn't give me the desired result. The only thing that works is a manual transpose but the number of records is 5k odd.

What I'm trying to achieve here is to transpose the data from rows for the company into columns so at a later stage to be able to count the number of votes and average per company.

SO33969916 question example

Upvotes: 0

Views: 633

Answers (3)

pnuts
pnuts

Reputation: 59495

Since you mentioned a PT did not suit , assuming RATING is in F2, please try in G3 copied down to suit: 

=IF(AND(COLUMN()-7<COUNTIF($E:$E,$E3),$E2<>$E3),OFFSET($F3,COLUMN()-7,0),"")

then drag all the formulae to the right until an entire column appears blank. Note this requires the TARGET ATTENDEE ORGANIZATION column be sorted.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60389

If what you want is the number of votes and average per company, that can be done in a variety of ways.

Using a Pivot Table, drag companies to the rows area; drag rating to the values area twice. Then change the Value Field setting on one of the Ratings to Count; and on the other to Average.

Add some formatting and various options gives you:

enter image description here

Or if you have a list of the Organizations (Company Names) in, let us say, G3:Gn, and your data table in columns A:C, you can use formulas:

Count:    H3: =COUNTIF($B$1:$B$1000,G3)
Average:  I3: =AVERAGEIF($B$1:$B$1000,G3,C1:$C$1000)

And fill down as far as needed.

enter image description here

Upvotes: 0

Rosetta
Rosetta

Reputation: 2725

PivotTable can do the job. All you need is a helper column using COUNTIFS. Notice the formula in cell D2.

enter image description here

And the PivotTable would look like this (set to Tabular Layout)

enter image description here

enter image description here

A note to take here is COUNTIFS can get really slow when the number of records grow to around 10k or more (or just my slow pc :/). When this happens, the workaround is: first sort your data, then use COUNTIFS over a limited number of cells only. For example, at cell D2, the formula will be =COUNTIFS(A2:A102,A2,B2:B102,B2), hence counting only 100 records rather than the whole bunch as you fill down the formula.

Upvotes: 1

Related Questions