If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|