PDA

View Full Version : Arrange Data Properly



shahidsami
07-18-2019, 11:32 PM
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
07-19-2019, 02:14 AM
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
07-19-2019, 02:56 AM
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
07-19-2019, 07:02 AM
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
07-20-2019, 03:45 AM
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
07-22-2019, 01:43 AM
Hi Shahid!
It works very well here with no mistakes.
Please refer to the attachment.

shahidsami
07-22-2019, 08:27 AM
Dear 大灰狼1976,
Yes you are right. Now I tried again and it is working fine. Thanks so much.


Regards,
Shahid