Data Filtration and Convert in Required Format

ArhamRajput · May 30th 2020

Dear Experts,

Your expertise are required i have 1000+ data as i attached below for example.

In first sheet which name is "Data" contains raw data where "X" mean is incomplete.

First i change the "X" with Column header for my working manually (as i did in 1st Example Sheet).

Then filter the data and copy paste it (as i did in 2nd Example sheet)

I am doing this activity since couple of months which takes whole day to complete it.

I am looking for VBA for this task if you guys could help i will be really appreciate it.

looking forward to your response.

Fluff13 · May 30th 2020

How about

Code

Sub shoaibRajput()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Data").Range("a1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary) * UBound(Ary, 2), 1 To 5)
   For c = 5 To UBound(Ary, 2)
      For r = 2 To UBound(Ary)
         If Ary(r, c) = "X" Then
            nr = nr + 1
            Nary(nr, 1) = Ary(r, 1)
            Nary(nr, 2) = Ary(r, 2)
            Nary(nr, 3) = Ary(r, 3)
            Nary(nr, 4) = Ary(r, 4)
            Nary(nr, 5) = Ary(1, c)
         End If
      Next r
   Next c
   Sheets("2nd Example").Range("G2").Resize(nr, 5).Value = Nary
End Sub

Display More

ArhamRajput · May 30th 2020

God Bless you.

Thank you so much

Fluff13 · May 30th 2020

You're welcome & thanks for the feedback.

Data Filtration and Convert in Required Format

Participate now!

Share