move data from columns to rows

[bill gras]

Is there a way to move numbers from cells in a column and put those numbers in
cells in rows , than average those cells per row ? eg:

a b c d e
f g h
1 cool 20 move to c1 20 15 7 7 1
average 10
2 cool 15 move to d1
3 cool 7 move to e1
4 cool 7 move to f1
5 cool 1 move to g1
6 tropic 5 move to c6 5 9
average 7
7 tropic 9 move to d6
8 gears 15 move to c8 15 12 6
average 11
9 gears 12 move to d8
10 gears 6 move to e8
11 heat 7 move to c11 7
average 7
12 top 6 move to c12 6 12
average 9
13 top 12 move to d12
down to 1020 rows

column "a" duplicate entries are at random sequences from 1 to 10 entries

all your efforts will be much appreciated
thank you for your time



bill gras

 

[Ms-Exl-Learner]

Try this in your duplicate copy and keep only the Names & Values only which
is given below:-

A Column BColumn
Name Values
cool 20
cool 15
cool 7
cool 7
cool 1
tropic 5
tropic 9
gears 15
gears 12
gears 6
heat 7
top 6
top 12

Assume that you are having the data like the above (i.e.) NAMES in A Column
and VALUES is B Column. In your example there is no heading for that. Just
add the headings in A1 & B1.

Now select the B column and do right click and select INSERT. Now the
VALUES will be in C Column and B Column will be empty.

Name the B1 cell as “Group†and in B2 cell paste the below formula.

=IF(COUNTIF($A$1:$A2,$A2)=1,"A",IF(COUNTIF($A$1:$A2,$A2)=2,"B",IF(COUNTIF($A$1:$A2,$A2)=3,"C",IF(COUNTIF($A$1:$A2,$A2)=4,"D",IF(COUNTIF($A$1:$A2,$A2)=5,"E",IF(COUNTIF($A$1:$A2,$A2)=6,"F",""))))))

Now copy the B2 cell formula and paste it to the remaining cells of your B
Column depends on your A column Data.

In D1 cell type Average and in D2 cell paste this formula

=AVERAGE(SUMIF(A:A,A2,C:C)/COUNTIF(A:A,A2))

And copy the D2 cell and paste it to the remaining cells of D Column depends
you’re your a column Data.

Now place the cursor in A1 cell and select Data>Pivot Table & Pivot Chart
Report>Next> (now you can see that it’s automatically selects your data range
from A column to D column) click Next>>Layout>

Now in the Right Side you can see the Field Lists that is NAME, GROUP,
VALUES & AVERAGE.

Now click and hold the NAME field and drop it in ROW.
Click and hold the VALUE field and drop it in ROW.
Click and hold the GROUP field and drop it in COLUMN.
Click and hold the AVERAGE field and drop it in DATA.
Do double click in Average which is available in DATA and select AVERAGE and
give OK.

Give OK>>Finish.

That’s it…

If this post helps, Click Yes!

 

[bill gras]

"Ms-Exl-Learner"
exactly what I was looking for ( if you are not a genius , you soon will be
! )

Thank you very much

 

[Ms-Exl-Learner]

You're Always Welcome and Thanks for your Appreciation.