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

Cell	Formula
A5	=INDEX($A$1:$F$2,MOD(COLUMNS($A5:A5)-1,2)+1,INT((COLUMNS($A5:A5)-1)/2)+1)

<thead>
</thead><tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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