0

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

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Someone gave me other solution but I will explore yours as well. Just to keep the answers in the loop here's what's been suggested (and worked): You could create the structure with the following formulae: • In D2 =IF(B2<>OFFSET(B2,-1,0),C2,"") • In E2 =IF(D2<>"",IF(OFFSET($D2,COLUMN()-4,)="",IF(OFFSET($C2,COLUMN()-4,)="","",OFFSET($C2,COLUMN()-4,)),""),"") Copy the E2 formula across to, say, Z2 (i.e. enough to accommodate the maximum # entries for any one company), then copy D2:Z2 down as far as needed.
Looks good too. So you can use the formulae version that update itself dynamically, or the PivotTable version which i think is easier to use (without needing very long formulae) but will need to refresh the table to update the values. Its up to you, have fun! :)
0

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

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

Thank you guys for all your ideas!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.