Need to sort specific data from from large data dump in the required format

[vishwa109]

I have a data dump in the below format, in which data first 31 rows different data for a particular object(vdisk_name) and then for another

vdisk_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
vdisk_name​ ​ ​ ​ ​	E081​ ​ ​ ​ ​
capacity​ ​ ​ ​ ​	6.44245E+11​ ​ ​ ​ ​
copy_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
status​ ​ ​ ​ ​	online​ ​ ​ ​ ​
sync​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
primary​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
mdisk_grp_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
mdisk_grp_name​ ​ ​ ​ ​	EVA8K​ ​ ​ ​ ​
type​ ​ ​ ​ ​	striped​ ​ ​ ​ ​
mdisk_id​ ​ ​ ​ ​
mdisk_name​ ​ ​ ​ ​
fast_write_state​ ​ ​ ​ ​	not_empty​ ​ ​ ​ ​
used_capacity​ ​ ​ ​ ​	4.60607E+11​ ​ ​ ​ ​
real_capacity​ ​ ​ ​ ​	4.73494E+11​ ​ ​ ​ ​
free_capacity​ ​ ​ ​ ​	12887785472​ ​ ​ ​ ​
overallocation​ ​ ​ ​ ​	136​ ​ ​ ​ ​
autoexpand​ ​ ​ ​ ​	on​ ​ ​ ​ ​
warning​ ​ ​ ​ ​	80​ ​ ​ ​ ​
grainsize​ ​ ​ ​ ​	256​ ​ ​ ​ ​
se_copy​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
easy_tier​ ​ ​ ​ ​	on​ ​ ​ ​ ​
easy_tier_status​ ​ ​ ​ ​	active​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	ssd​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	27380416512​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	enterprise​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	4.46114E+11​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	nearline​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	0​ ​ ​ ​ ​
compressed_copy​ ​ ​ ​ ​	no​ ​ ​ ​ ​
uncompressed_used_capacity​ ​ ​ ​ ​	4.60607E+11​ ​ ​ ​ ​
vdisk_id​ ​ ​ ​ ​	1​ ​ ​ ​ ​
vdisk_name​ ​ ​ ​ ​	EP082​ ​ ​ ​ ​
capacity​ ​ ​ ​ ​	6.44245E+11​ ​ ​ ​ ​
copy_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
status​ ​ ​ ​ ​	online​ ​ ​ ​ ​
sync​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
primary​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
mdisk_grp_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
mdisk_grp_name​ ​ ​ ​ ​	EVA8K​ ​ ​ ​ ​
type​ ​ ​ ​ ​	striped​ ​ ​ ​ ​
mdisk_id​ ​ ​ ​ ​
mdisk_name​ ​ ​ ​ ​
fast_write_state​ ​ ​ ​ ​	not_empty​ ​ ​ ​ ​
used_capacity​ ​ ​ ​ ​	5.92415E+11​ ​ ​ ​ ​
real_capacity​ ​ ​ ​ ​	6.05311E+11​ ​ ​ ​ ​
free_capacity​ ​ ​ ​ ​	12895649792​ ​ ​ ​ ​
overallocation​ ​ ​ ​ ​	106​ ​ ​ ​ ​
autoexpand​ ​ ​ ​ ​	on​ ​ ​ ​ ​
warning​ ​ ​ ​ ​	80​ ​ ​ ​ ​
grainsize​ ​ ​ ​ ​	256​ ​ ​ ​ ​
se_copy​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
easy_tier​ ​ ​ ​ ​	on​ ​ ​ ​ ​
easy_tier_status​ ​ ​ ​ ​	active​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	ssd​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	22548578304​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	enterprise​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	5.82762E+11​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	nearline​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	0​ ​ ​ ​ ​
compressed_copy​ ​ ​ ​ ​	no​ ​ ​ ​ ​
uncompressed_used_capacity​ ​ ​ ​ ​	5.92415E+11​ ​ ​ ​ ​
vdisk_id​ ​ ​ ​ ​	2.00​ ​ ​ ​ ​
vdisk_name​ ​ ​ ​ ​	EiD010​ ​ ​ ​ ​
capacity​ ​ ​ ​ ​	1.09951E+12​ ​ ​ ​ ​
copy_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
status​ ​ ​ ​ ​	online​ ​ ​ ​ ​
sync​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
primary​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
mdisk_grp_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
mdisk_grp_name​ ​ ​ ​ ​	EVA8K​ ​ ​ ​ ​
type​ ​ ​ ​ ​	striped​ ​ ​ ​ ​
mdisk_id​ ​ ​ ​ ​
mdisk_name​ ​ ​ ​ ​
fast_write_state​ ​ ​ ​ ​	not_empty​ ​ ​ ​ ​
used_capacity​ ​ ​ ​ ​	5.87561E+11​ ​ ​ ​ ​
real_capacity​ ​ ​ ​ ​	6.09552E+11​ ​ ​ ​ ​
free_capacity​ ​ ​ ​ ​	21990756352​ ​ ​ ​ ​
overallocation​ ​ ​ ​ ​	180​ ​ ​ ​ ​
autoexpand​ ​ ​ ​ ​	on​ ​ ​ ​ ​
warning​ ​ ​ ​ ​	80​ ​ ​ ​ ​
grainsize​ ​ ​ ​ ​	256​ ​ ​ ​ ​
se_copy​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
easy_tier​ ​ ​ ​ ​	on​ ​ ​ ​ ​
easy_tier_status​ ​ ​ ​ ​	active​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	ssd​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	2415919104​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	enterprise​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	6.07136E+11​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	nearline​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	0​ ​ ​ ​ ​
compressed_copy​ ​ ​ ​ ​	no​ ​ ​ ​ ​
uncompressed_used_capacity​ ​ ​ ​ ​	5.87561E+11​ ​ ​ ​ ​

<TBODY> </TBODY>

vdisk_name​ ​ ​ ​ ​	EiD010​ ​ ​ ​ ​
capacity​ ​ ​ ​ ​	1.09951E+12​ ​ ​ ​ ​
copy_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
status​ ​ ​ ​ ​	online​ ​ ​ ​ ​
sync​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
primary​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
mdisk_grp_id​ ​ ​ ​ ​	0​ ​ ​ ​ ​
mdisk_grp_name​ ​ ​ ​ ​	EVA8K​ ​ ​ ​ ​
type​ ​ ​ ​ ​	striped​ ​ ​ ​ ​
mdisk_id​ ​ ​ ​ ​
mdisk_name​ ​ ​ ​ ​
fast_write_state​ ​ ​ ​ ​	not_empty​ ​ ​ ​ ​
used_capacity​ ​ ​ ​ ​	5.87561E+11​ ​ ​ ​ ​
real_capacity​ ​ ​ ​ ​	6.09552E+11​ ​ ​ ​ ​
free_capacity​ ​ ​ ​ ​	21990756352​ ​ ​ ​ ​
overallocation​ ​ ​ ​ ​	180​ ​ ​ ​ ​
autoexpand​ ​ ​ ​ ​	on​ ​ ​ ​ ​
warning​ ​ ​ ​ ​	80​ ​ ​ ​ ​
grainsize​ ​ ​ ​ ​	256​ ​ ​ ​ ​
se_copy​ ​ ​ ​ ​	yes​ ​ ​ ​ ​
easy_tier​ ​ ​ ​ ​	on​ ​ ​ ​ ​
easy_tier_status​ ​ ​ ​ ​	active​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	ssd​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	2415919104​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	enterprise​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	6.07136E+11​ ​ ​ ​ ​
tier​ ​ ​ ​ ​	nearline​ ​ ​ ​ ​
tier_capacity​ ​ ​ ​ ​	0​ ​ ​ ​ ​
compressed_copy​ ​ ​ ​ ​	no​ ​ ​ ​ ​
uncompressed_used_capacity​ ​ ​ ​ ​	5.87561E+11​ ​ ​ ​ ​

<TBODY> </TBODY>

I would like to get the data sorted out in the below format

vdisk_name	easy_tier_status</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	tier <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	capacity	tier	capacity	tier	capacity
E081​	active</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	ssd <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	27380416512	enterprise <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	4.46E+11 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	nearline <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	0 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>
EP082</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	active	ssd	22548578304</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	enterprise	5.83E+11 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	nearline	0
EiD010	active	ssd	2415919104</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	enterprise	6.07E+11 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	nearline	0

<TBODY>
</TBODY>

and so on

so that mean from the dump I need to sort only above mentioned 8 fields and here I have just mentioned 3 data set but I have to do it for limitless data.
Also each set of data has 31 rows(fields) which is fixed and out of which I want to search 8 fields as mentioned above

Please help me with this and let me know if any more more details required

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I have created a macro which you will need to insert into a macro Module and run.

I have assumed that the data will always be in blocks of 31 rows. The sample data you supplied has some rows missing. If that is typical data then I will need to do some re-working.

Basically, the macro reads in the headings from column A and the data from column B. You can set the start row in case your data does not start in row 1 because of headings, perhaps, by changing this value:

Const dtaRow As Long = 1

You can vary the block size from 31 as well.

Const blkSize As Long = 31

There is also a column list setting which will allow you to easily add columns to the output. Just add them to this list:

colList = Array(1, 23, 24, 25, 26, 27, 27, 28)

Option Base 1

Sub Transpose()

    Const blkSize As Long = 31      ' Set the number of rows for one block of data
    Const dtaRow As Long = 1        ' Set the first data row to allow for headings etc
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim arr1 As Variant
    Dim arr2 As Variant
    Dim arr3 As Variant
    Dim arr4 As Variant
    Dim i As Long, j As Long
    Dim noRows As Long
    Dim colList As Variant
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    colList = Array(1, 23, 24, 25, 26, 27, 27, 28) ' Choose column numbers for output
    
    With ws1

        ' Process the column Headings
        arr1 = .Cells(dtaRow, "A").Resize(blkSize, 1)
        ReDim arr2(1 To 1, 1 To UBound(colList))
        For j = 1 To UBound(colList)
            arr2(1, j) = arr1(colList(j), 1)
        Next
    
        ' Process the data
        noRows = .Cells(.Rows.Count, "A").End(xlUp).Row - dtaRow + 1
        arr3 = .Cells(dtaRow, "B").Resize(noRows)
        ReDim arr4(1 To (noRows / blkSize), 1 To UBound(colList))
        For i = 1 To noRows / blkSize
            For j = 1 To UBound(colList)
                arr4(i, j) = arr3(((i - 1) * blkSize + colList(j)), 1)
            Next
        Next
        
    End With
    
    ' Output the results
    With ws2
        .Cells.Clear
        .Range("A1").Resize(1, UBound(colList)) = arr2
        .Range("A2").Resize((noRows / blkSize), UBound(colList)) = arr4
        .Columns(1).Resize(, UBound(colList)).AutoFit
        .Activate
    End With

End Sub

 

[vishwa109]

thanks mate for the "beautiful" script that you gave.

I wld ask for lil more favour.

The excel file on which I am running this macros is being generated as a result of a batch program and "sheet1" of excel has by name abc. ("." at the end) and sheet 2 is not being creteted.

So I need some command on the top to create sheet 2.

Also I tried to change the script line Set ws1 = ThisWorkbook.Worksheets("Sheet1") to Set ws1 = ThisWorkbook.Worksheets("abc.") but it shows error

what could be the workaround mate.....

 

[RickXL]

Hi again,

You say the data is being created by a batch program.

Does that mean that you have one workbook with the macros in it and a different workbook with the data in it? If so, that would account for the "abc." worksheet name not being accepted. Please let me know how many workbooks you have and if there are more than one say how you identify the data workbook.

To add another worksheet you really need to check whether it exists first. This is so you can run the macro twice without it crashing and complaining that the sheet already exists on subsequent runs. A Function macro would provide a general solution which could be used in other macros. For instance:

Function wsAdd(Name As String, Optional WB As Workbook) As Worksheet
    If WB Is Nothing Then Set WB = ThisWorkbook
    With WB
        On Error Resume Next
        Set wsAdd = .Worksheets(Name)
        On Error GoTo 0
        If wsAdd Is Nothing Then
            Set wsAdd = .Worksheets.Add(After:=.Worksheets(.Worksheets.Count))
            wsAdd.Name = Name
        End If
    End With
End Function

This would be called in the main macro like this:

Sub test()
    Dim ws1 As Worksheet
   
    Set ws1 = wsAdd("Sheet2")
End Sub

If you let me know about the workbooks I will put it all together for you.