converting multiple rows to one row?

[Nawzad]

Hello guys
I have data similar to the following data, how to covert multiple rows to one row?

Capture.JPG

[Glenn Kennedy]

How does this look?

=IFERROR(INDIRECT(TEXT(AGGREGATE(15,6,(ROW($A$2:$E$4)*100+COLUMN($A$2:$E$4))/($A$2:$E$4<>0),COLUMNS($H2:H2)),"R0C00"),FALSE),"")

See sheet, and remember: you may need ; instead of , Also R and C may need to be altered depending on your regional settings.

[Nawzad]

Thanks for replying
But I have many data (see attached figure)like in the figure i attached , I can not write the formula you gave me to all, I need an easier way to do that

Best regards
NawzadCapture.JPG

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (in your case 5-10 BLOCKS of data will be enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Nawzad]

I have used VBA code and it worked... thanks a lot

[Glenn Kennedy]

Please share yiur solution so that others can benefit from it.

[Nawzad]

With pleasure

I can not attache the link yet because I am new,so I will copy the answer from a website:
#Solution

The following VBA code also can help you convert a range of data to one row.

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

Sub TransformOneRow()
'Updateby20131120
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Ranges to be transform :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Paste to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
xRows = InputRng.Rows.Count
xCols = InputRng.Columns.Count
For i = 1 To xRows
InputRng.Rows(i).Copy OutRng
Set OutRng = OutRng.Offset(0, xCols + 0)
Next
Application.ScreenUpdating = True
End Sub

3. Then press F5 key to run the code, a dialog is popped out for you to select a range of contents you want to convert into a row, and click Ok,
another popped-up dialog is for you to select a single cell to put out the result. See screenshots:

Capture.JPG

Capture.JPG