Excel Tips - Transpose Turn Rows into Columns

Hi,
I have this file like:

Column1 Column2 Column3
111 A11 011
111 A11 012
111 A11 013
----------------------------
111 B11 012
----------------------------
---------------------------
14213 XXX1 011
14213 XXX1 012
--------------------------

All I want is to transpose Column3 based on Column1 and Column2, as you see I have in Column1, and 2 hte same record repeating just because of Column3. In the end I want something like this:

Column1 Column2 New_ Column3 New_Column4.... New_Column "n" 111 A11 011 012 -----
111 B11 012
112 A11 014 020
---------------------------------------------------------------------
where "n" is the number of records in column3 for the same record in column1 and column2, I mean if I have the same record in column1, and column 2 let's say 5 times then I wil have 5 columns after column2 and the records should be from the cell(1:5).

Please is there anyone who can help me with this issue?
Thank you.

I'm copy and pasting a list into Excel. Excel automatically takes my list
and puts each item into the next row. What I want to do is have each row be
the heading of a column instead. So how to I get the table to reverse
itself, so items in rows go into columns, and the columns become rows instead?

Hi All,

I basically want to turn Column B in rows. My SpreadSheet lo*oks like:
Code Value
BB_GOLD_SWAP_1M_BBRG 31MAY4
BB_GOLD_SWAP_1M_BBRG 18AUG4
BB_GOLD_SWAP_1M_BBRG 30AUG4
BB_GOLD_SWAP_1M_BBRG 25DEC3
BB_GOLD_SWAP_1Y_BBRG 26DEC3
BB_GOLD_SWAP_1Y_BBRG 1JAN4
BB_GOLD_SWAP_1Y_BBRG 2APR4
BB_GOLD_SWAP_1Y_BBRG 9APR4
BB_GOLD_SWAP_2M_BBRG 12APR4
BB_GOLD_SWAP_2M_BBRG 3MAY4
BB_GOLD_SWAP_2M_BBRG 31MAY4
BB_GOLD_SWAP_2M_BBRG 18AUG4
BB_GOLD_SWAP_2M_BBRG 30AUG4


I have 65000 rows. I want to Check each row in Column A; if *for example
the code in A2 is Equal to the Code in A1 then I want to Cop*y the Value
in Cell B2 into Cell A3. i.e. the Values Column B must be tu*rned into
Rows. The SpreadSheet will look like:
BB_GOLD_SWAP_1M_BBRG 31MAY4 18AUG4 30AUG4 25DEC3
BB_GOLD_SWAP_1Y_BBRG 26DEC3 1JAN4 2APR4 9APR4
BB_GOLD_SWAP_2M_BBRG 12APR4 3MAY4 31MAY4 18AUG4 30AUG4
In cases where the # values for a particular code exceed 250* then a
second, third (etc) row should be created for the particular* code.
PLEASE HELP

I basically want to turn Column B in rows. My SpreadSheet looks like:
Code Value
BB_GOLD_SWAP_1M_BBRG 31MAY4
BB_GOLD_SWAP_1M_BBRG 18AUG4
BB_GOLD_SWAP_1M_BBRG 30AUG4
BB_GOLD_SWAP_1M_BBRG 25DEC3
BB_GOLD_SWAP_1Y_BBRG 26DEC3
BB_GOLD_SWAP_1Y_BBRG 1JAN4
BB_GOLD_SWAP_1Y_BBRG 2APR4
BB_GOLD_SWAP_1Y_BBRG 9APR4
BB_GOLD_SWAP_2M_BBRG 12APR4
BB_GOLD_SWAP_2M_BBRG 3MAY4
BB_GOLD_SWAP_2M_BBRG 31MAY4
BB_GOLD_SWAP_2M_BBRG 18AUG4
BB_GOLD_SWAP_2M_BBRG 30AUG4

I have 65000 rows. I want to Check each row in Column A; if for example
the code in A2 is Equal to the Code in A1 then I want to Copy the Value
in Cell B2 into Cell A3. i.e. the Values Column B must be turned into
Rows. The SpreadSheet will look like:
BB_GOLD_SWAP_1M_BBRG 31MAY4 18AUG4 30AUG4 25DEC3
BB_GOLD_SWAP_1Y_BBRG 26DEC3 1JAN4 2APR4 9APR4
BB_GOLD_SWAP_2M_BBRG 12APR4 3MAY4 31MAY4 18AUG4 30AUG4
In cases where the # values for a particular code exceed 250 then a
second, third (etc) row should be created for the particular code.
PLEASE HELP

Hi all

I have a spreadsheet which has the following fields plus a few others:

Invoice Ref | Product Code | Product Description | Product Qty |

This spreadsheet is created from an Access query.

For each row with an Invoice Ref their is data in the other 3 columns. If the invoice included more than 1 product then the invoice ref is repeated on the row below with the product details in the neighbouring columns. For example:

Inv Ref | Code | Product Desc | QTY
2850078 100883 (DT20 Twin Channel Digital Programmer, , ) 1
2850078 100311 (Worcester Greenstar 30CDi Combi NG, , ) 1
2850078 100800 (Greenstar HE II Standard 100mm ) 1


I need to transpose the data so that the invoice ref only appears once but data in the code, product desc and qty columns which have a matching invoice ref all appear in separate rows.

So it would become

Inv Ref | Code1 | Code2 | Code3 | Prod Desc1 | Prod Desc 2 etc etc

Any idea how I might achieve this. Kind like a IF (A2=A3) THEN TRANSPOSE B2,C2,D2

hi guys,

(Note: excel for Mac, so VBA not really an option)

I have data where the fourth field is in multiple columns, and I need it in multiple rows:

e.g.
masks 1954 producta productb productc
coats 1543 productd
shoes 1234
hats 9876 producta productf productg productz productd

and I need it in the format:
masks 1954 producta
masks 1954 productb
masks 1954 productc
coats 1543 productd
shoes 1234
hats 9876 producta
hats 9876 productf
hats 9876 productg
hats 9876 productz
hats 9876 productd

in other words the fourth field (products) is in multiple columns, and I need the fourth field only transposed to multiple rows, and the other fields repeated.

any help appreciate (and excel for mac is very limited with VBA. prefer formulas if possible, or mac friendly vba if not)

David




Category Blah1 Blah2 Products

Is it possible to transfer text from cells in a row so that they form a
single column. In other words is it possible to turn a row 90 degrees so that
the cells which were beside each other now are underneath each other.

Hi All
I've a pretty challenging work to do, I am trying to use the TRANSPOSE funcion to transpose rows/columns, till now, with no luck.

Because the required columns to output are not fixed, this approach doesn't get the point...


Please, see the attached file for an example.,

EXAMPLE.xls

Example.jpg


Any suggestion will be very very well accepted...

Hi All
I've a pretty challenging work to do, I am trying to use the TRANSPOSE funcion to transpose rows/columns, till now, with no luck.

Because the required columns to output are not fixed, this approach doesn't get the point...

Please, see the attached file for an example:

EXAMPLE.xls

Example.jpg


Any suggestion will be very very well accepted...

Dear All,

I have a question, what should I change in the following code in order for it to automatically transpose rows into columns? My rows are formatted as follows:

A B C D E
1 Country Countrycode 1960 1961 ..
2 Afghanistan AFG 120.00 100.00 ..
3 Albania ALB 240.00 320.00 ..

(an example is added

I need them to be like this:

A B C D
1 Afghanistan 1960 120.00
2 Afghanistan 1961 100.00
.. .. .. ...
3 Albania 1960 240.00
4 Albania 1961 320.00

The horizontal list starts at column c and continues for 50 years ( 50 columns)

Code:

Sub x()
 
Dim rList As Range, rCell As Range, n As Long, rData As Range
 
Application.DisplayAlerts = False

With Sheets(1)
    n = 1
    Sheets.Add().Name = "Temp"
    .Range("A1", .Range("A1").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("Temp").Range("A1"), Unique:=True
    Set rList = Sheets("Temp").Range("A2", Sheets("Temp").Range("A2").End(xlDown))
     For Each rCell In rList
        .Range("A1").AutoFilter Field:=1, Criteria1:=rCell
        Set rData = .AutoFilter.Range
        Set rData = rData.Offset(1, 1).Resize(rData.Rows.Count - 1, rData.Columns.Count - 1)
        Sheet2.Range("A" & n) = rCell
        rData.Copy
        Sheet2.Range("B" & n).PasteSpecial Transpose:=True
        n = n + 2
    Next rCell
    Sheets("Temp").Delete
    .AutoFilterMode = False
End With
 
Application.DisplayAlerts = True
    
End Sub