Arrange Data Properly

[shahidsami]

Dear All,
I have huge data of BOQ but not in proper data format. I am attaching herewith excel file where first sheet contain data received from Client and 2nd sheet contains data after formatting manually.


I required you people experties on it whether through VBA can I get desired output.






Thanks & best regards,
Shahid

[大灰狼1976]

Hi shahidsami!
Welcome to vbax forum.
something like below:

Sub test()
Dim arrOri, arrRst, arrTmp, i&, j&, r&, s$
arrOri = Sheets(1).[a1].CurrentRegion
ReDim arrRst(1 To UBound(arrOri) + 100, 1 To 3)
For i = 1 To UBound(arrOri) Step 4
  r = r + 1
  arrRst(r, 1) = arrOri(i, 1)
  arrRst(r, 2) = arrOri(i + 1, 1)
  arrRst(r, 3) = arrOri(i + 3, 1)
  s = Replace(arrOri(i + 2, 1), ":", "")
  s = Application.Trim(s)
  arrTmp = Split(s, " ")
  For j = 0 To UBound(arrTmp) Step 2
    r = r + 1
    arrRst(r, 2) = arrTmp(j)
    arrRst(r, 3) = arrTmp(j + 1)
  Next j
Next i
Sheets(2).[a1].Resize(r, 3) = arrRst
End Sub

[shahidsami]

Dear 大灰狼1976,
Thanks for your reply with solution, it is working fin.

When I am going through the other data what I have some place serial no. is coming below the items. I have attached the sample for that and I need same output as before.

Thanks & best regards,
Shahid

[大灰狼1976]

Hi Shahid!
Do you need a general approach like below?

Sub test()
Dim arrOri, arrRst, arrTmp, i&, j&, r&, s$
arrOri = Sheets(1).[a1].CurrentRegion
ReDim arrRst(1 To UBound(arrOri) + 100, 1 To 3)
For i = 1 To UBound(arrOri) Step 4
  r = r + 1
  If IsNumeric(arrOri(i, 1)) Then
    arrRst(r, 1) = arrOri(i, 1)
    arrRst(r, 2) = arrOri(i + 1, 1)
  Else
    arrRst(r, 2) = arrOri(i, 1)
    arrRst(r, 1) = arrOri(i + 1, 1)
  End If
  arrRst(r, 3) = arrOri(i + 3, 1)
  s = Replace(arrOri(i + 2, 1), ":", "")
  s = Application.Trim(s)
  arrTmp = Split(s, " ")
  For j = 0 To UBound(arrTmp) Step 2
    r = r + 1
    arrRst(r, 2) = arrTmp(j)
    arrRst(r, 3) = arrTmp(j + 1)
  Next j
Next i
Sheets(2).[a1].Resize(r, 3) = arrRst
End Sub

[shahidsami]

Dear 大灰狼1976,
Thanks for your reply with solution and it is working as I have desired but the major problem is this code showing "Run-time error '9': Subscript out of range" error when record is more than 3 serial no.

In some sheet of my data the serial no. is till 350 also. I am attaching the sample file where your code is giving error. Please suggest me.

Thanks & best regards,
Shahid

[大灰狼1976]

Hi Shahid!
It works very well here with no mistakes.
Please refer to the attachment.

[shahidsami]

Dear 大灰狼1976,
Yes you are right. Now I tried again and it is working fine. Thanks so much.


Regards,
Shahid