Any Function to do this?

[Abbas999]

Hi all members,

I need your help, Check pic below:

Before:

After:

I want the data:
First Min_ab001 then Max_ab001
Then Min_ab002 and Max_ab002 and so on...

Please Help me if anyone knows a method.

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A5</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$F$2,MOD(<font color="Red">COLUMNS(<font color="Green">$A5:A5</font>)-1,2</font>)+1,INT(<font color="Red">(<font color="Green">COLUMNS(<font color="Purple">$A5:A5</font>)-1</font>)/2</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />

and drag across

Excel 2013
	A	B	C	D	E	F	G	H	I	J	K
1	min_ab001	min_ab002	min_ab003	min_ab004	min_ab005	min_ab006
2	max_ab001	max_ab002	max_ab003	max_ab004	max_ab005	max_ab006
3
4
5	min_ab001	max_ab001	min_ab002	max_ab002	min_ab003	max_ab003	min_ab004	max_ab004	min_ab005	max_ab005	min_ab006
Sheet2

 

[XOR LX]

Hi.

=INDEX($A$1:$G$2,1+ISEVEN(COLUMNS($A:A)),EVEN(COLUMNS($A:A))/2)

Copy to the right as required.

Regards

 

[hurgadion]

Hi,try this:

Sub aaa()
Dim a&, i&, j&, tbl1

a = Cells(1, Columns.Count).End(xlToLeft).Column
tbl1 = Range(Cells(1, 1), Cells(2, a))
Rows("1:2").ClearContents
For i = 1 To a
  For j = 1 To 2
     x = x + 1
     Cells(1, x).Value = tbl1(j, i)
  Next j
Next i
End Sub

Best regards.

 

[Abbas999]

Solved! Many thanks. But i have another problem.
I also have the MIN_ab001 and Max_ab001 values that need to pasted into the columns. But The order is not right. See the pic below to understand.

Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=INDEX($A$1:$F$2,MOD(COLUMNS($A5:A5)-1,2)+1,INT((COLUMNS($A5:A5)-1)/2)+1)[/TD]
[/TR]
</tbody>[/TABLE]



and drag across


Excel 2013

	A	B	C	D	E	F	G	H	I	J	K
min_ab001	max_ab001	min_ab002	max_ab002	min_ab003	max_ab003	min_ab004	max_ab004	min_ab005	max_ab005	min_ab006

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]min_ab001[/TD]
[TD="bgcolor: #FFFF00"]min_ab002[/TD]
[TD="bgcolor: #FFFF00"]min_ab003[/TD]
[TD="bgcolor: #FFFF00"]min_ab004[/TD]
[TD="bgcolor: #FFFF00"]min_ab005[/TD]
[TD="bgcolor: #FFFF00"]min_ab006[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FFFF00"]max_ab001[/TD]
[TD="bgcolor: #FFFF00"]max_ab002[/TD]
[TD="bgcolor: #FFFF00"]max_ab003[/TD]
[TD="bgcolor: #FFFF00"]max_ab004[/TD]
[TD="bgcolor: #FFFF00"]max_ab005[/TD]
[TD="bgcolor: #FFFF00"]max_ab006[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet2​

 

[Abbas999]

Problem solved but can you help in this?

Hi.

=INDEX($A$1:$G$2,1+ISEVEN(COLUMNS($A:A)),EVEN(COLUMNS($A:A))/2)

Copy to the right as required.

Regards

 

[VBA Geek]

A2

=index($a$9:$b$16,int((columns($a2:a2)-1)/2)+1,mod(columns($a2:a2)-1,2)+1)

 

[Abbas999]

A2

=index($a$9:$b$16,int((columns($a2:a2)-1)/2)+1,mod(columns($a2:a2)-1,2)+1)

Very Quick help. It is working. Thank you very much @VBA Geek