Change rows to columns for similar records
 

Hi - am trying to change rows to columns for similar records. How do I do that?
e.g.

Staff A Cellular No. XXXXX
Staff A Office No. XXXX
Staff B Cellular No. XXXXX
Staff B Office No. XXXXX

Wld like for it be in the following row
Staff Cellular No Office No
A XXXXX XXXXX
B XXXXX XXXXXX

Do advise. Thanks.

Change rows to columns for similar records
 

one way would be

Would be to set up your "heading" as such
Cell Phone
Staff A 1 2
Staff B 3 4

Then assuming that
Staff A is in B8
Staff B is in B9
Cell is in C7
Phone is in D7

In cell c8 enter the following formula
=SUMPRODUCT(--($B$2:$B$5=$B8),--($C$2:$C$5=C$7),$D$2:$D$5)

Copy as needed - this assumes that the "original data" is in cell b2:d5

Success - click yes....
--
Wag more, bark less


"Katherine" wrote:

Hi - am trying to change rows to columns for similar records. How do I do that?
e.g.

Staff A Cellular No. XXXXX
Staff A Office No. XXXX
Staff B Cellular No. XXXXX
Staff B Office No. XXXXX

Wld like for it be in the following row
Staff Cellular No Office No
A XXXXX XXXXX
B XXXXX XXXXXX

Do advise. Thanks.

Change rows to columns for similar records
 

Suppose you have your data in Sheet1 ColA to ColC starting from Row1.

--Add headers to your existing data.
--From Sheet2 click menu DataFilterAdvanced Filter
--Select Action as 'Copy to another location'
--In list Range mention Sheet1!A:A
--In copy to mention A1 or select the cell A1 of Sheet2
--Check 'Unique records only' and hit OK to generate a unique list of staff
names.
--Add the other headers to Sheet2 cell B1, C1 as "Cellular No" and "Office No"
--In cell B2 of Sheet2 enter the below formula and copy/drag down and to
Col C as required

=SUMPRODUCT((Sheet1!$A$1:$A$1000=$A2)*
(Sheet1!$B$1:$B$1000=B$1),Sheet1!$C$1:$C$1000)

--
Jacob (MVP - Excel)


"Katherine" wrote:

Hi - am trying to change rows to columns for similar records. How do I do that?
e.g.

Staff A Cellular No. XXXXX
Staff A Office No. XXXX
Staff B Cellular No. XXXXX
Staff B Office No. XXXXX

Wld like for it be in the following row
Staff Cellular No Office No
A XXXXX XXXXX
B XXXXX XXXXXX

Do advise. Thanks.

Change rows to columns for similar records
 

Hi Katherine

Insert a blank row above your data, which is assumed to be in columns A:C
In G1 Type Staff, in H1 type Cellular No., in I1 type Office No.
In G2
=INDEX($A$1:$C$100,(ROW()-1)*2,1)
In H2
=INDEX($A$1:$C$100,(ROW()-1)*2,3)
In I2
=INDEX($A$1:$C$100,((ROW()-1)*2)+1,3)

Amend Range to whatever value above 100 contains all of your data.
Now, copy F2:I2 down the page as far as required.

Copy columns F:IPaste SpecialValues to change from formulae to the
data you require.
--
Regards
Roger Govier

Katherine wrote:
Hi - am trying to change rows to columns for similar records. How do I do that?
e.g.

Staff A Cellular No. XXXXX
Staff A Office No. XXXX
Staff B Cellular No. XXXXX
Staff B Office No. XXXXX

Wld like for it be in the following row
Staff Cellular No Office No
A XXXXX XXXXX
B XXXXX XXXXXX

Do advise. Thanks.