Reformatting question

[KenLeg]

Hi

I have been playing with this for a little while, but just cant seem to get it right.

I have the following grid - of countries in Column A and Row 1 - with data values.

<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=64>us</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>-</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>7</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>55</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>-</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>62</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>-</TD></TR></TBODY></TABLE>


What I am trying to do is to "transpose" this, using a function if available, to more like the below format.

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16 width=64>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=64>-</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>2</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>7</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>55</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>-</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>10</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>ae</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>3</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>uk</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>62</TD></TR><TR style="HEIGHT: 12pt" height=16><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=16>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>us</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>-</TD></TR></TBODY></TABLE>


Can someone help - can this be done ?

Thanks in advance

Ken.

 

[VoG]

Try running this macro - output beneath your existing table

Sub Xpose()
Dim LR As Long, LC As Long, i As Long, j As Long, k As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
k = LR + 1
For i = 2 To LR
    For j = 2 To LC
        k = k + 1
        Cells(k, 1).Value = Cells(i, 1).Value
        Cells(k, 2).Value = Cells(1, j).Value
        Cells(k, 3).Value = Cells(i, j).Value
    Next j
Next i
End Sub

 

[Hashiru]

Hi

From a close look at your sceniro, it seems as if you will have to use macros to do that or you manually do that in excel normal interface.

Just a suggestion.

 

[Rick Rothstein]

I have assumed you posted simplified data and designed the following code accordingly (if not, the following code will automatically adapt if you add more countries in the future).

Sub TransposeData()
  Dim X As Long, LastRow As Long, LastColumn As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
  Columns("B").Insert
  For X = LastRow To 2 Step -1
    Rows(X + 1).Resize(LastColumn - 2).Insert
    With Cells(X, "B").Resize(LastColumn - 1)
      .Offset(, -1).Value = .Offset(, -1).Cells(1).Value
      .Value = WorksheetFunction.Transpose(Range("C1:" & Split(Cells(1, LastColumn + 1).Address, "$")(1) & "1"))
      .Offset(, 1).Value = WorksheetFunction.Transpose(Cells(X, 3).Resize(, LastColumn - 1))
    End With
  Next
  Rows(1).Delete
  Range("D1:" & Split(Cells(1, LastColumn + 1).Address, "$")(1) & Cells(Rows.Count, "A").End(xlUp).Row).Delete
End Sub

 

[MikeWx]

I assumed you were trying to get the result in another group of columns. If your original input data is in A1:D4, you can get the transposed result by copying the following formulas in F2:H2, then copying them down. My formulas will handle 10 rows of input data, but you can increase the the $A$10 to $A$ your actual number in the F2 formula and likewise the $D$10 in the H2 formula; before copying them down.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>ae</TD><TD>ae</TD><TD>-</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>=INDEX($A$2:$A$10,ROUNDUP(ROW(A1)/3,0),1)</TD></TR><TR><TD>G2</TD><TD>=INDEX($B$1:$D$1,1,ROW(A1)-(3*(ROUNDUP(ROW(A1)/3,0)-1)))</TD></TR><TR><TD>H2</TD><TD>=INDEX($B$2:$D$10,ROUNDUP(ROW(A1)/3,0),ROW(A1)-(3*(ROUNDUP(ROW(A1)/3,0)-1)))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[VoG]

FWIIW my code is also independent of the numbers of columns and rows (within the constraints of Excel's limitations).

 

[Hashiru]

Sub Transpose()
Dim RowCount As Long, ColumnCount As Long
'Determine the number of rows in the data set
Set WSO = Application.Worksheets(1)
Set WSN = Application.Worksheets(2)
RowCount = WSO.Cells(Rows.Count, 1).End(xlUp).Row
ColumnCount = WSO.Cells(1, Columns.Count).End(xlToLeft).Column
'select the data set
WSO.Cells(1, 1).CurrentRegion.Select
With Selection

For i = 2 To RowCount
For j = 2 To ColumnCount

If IsNumeric(WSO.Cells(i, j)) Then

WSN.Cells(1, 1).Value = "Country1"
WSN.Cells(1, 2).Value = "Country2"
WSN.Cells(1, 3).Value = "Value"
'Determine the next data entry row
NextRow = WSN.Cells(Rows.Count, 1).End(xlUp).Row + 1

WSO.Cells(i, 1).Copy Destination:=WSN.Cells(NextRow, 1)
WSO.Cells(1, j).Copy Destination:=WSN.Cells(NextRow, 2)
WSO.Cells(i, j).Copy Destination:=WSN.Cells(NextRow, 3)


End If
Next j

Next i
End With

End Sub


This solution assumes that you have the sheet with the original data set named sheet1 and the new data sheet named sheet2

 

[Hashiru]

Hi MikeWx

Can you help me out with how you develop these formulae that did this wonderful job. I will particularly want to understand how you use the excel functions.

Thanks in advance

 

[Hashiru]

Hi VoG

I am in a learning curve on these things. In that light I tried to test various solutions. When I test your solution it wrote values upto cell B781. Is there a way you can modify that solution so it gives something like the propose solution.

Thanks for your reply in advance

 

[VoG]

My results with the sample data

Excel Workbook
	A	B	C	D
1		ae	uk	us
2	ae	-	2	7
3	uk	55	-	10
4	us	3	62	-
5
6	ae	ae	-
7	ae	uk	2
8	ae	us	7
9	uk	ae	55
10	uk	uk	-
11	uk	us	10
12	us	ae	3
13	us	uk	62
14	us	us	-
15
Sheet1