Help with formula to tabulate data in stacked form

[Fractalis]

Hi all,

I have the below text in column A that has data with pairs of parameter,value in stacked way and separated with " = ". For this I'm trying to have a formula to tabulate it
in order to extract some parameters of each block and put them in each line.

In B2 I have this formula so far "=MID(INDEX($A$1:$A$79,MATCH("*"&B$1&"*",$A$1:$A$79,0)),16,10)" but when I copy accross and down, only
shows the data of first block.

How to tabulate the values of parameters ID,NAME,TYPE,RSGSN?

The output I'm trying to get is from column B to E.

Thanks in advance

file.xlsx
	A	B	C	D	E
1		ID	NAME	TYPE	RSGSN
2	some text some text	0	INV445	ECT	18560
3	some text	0	INV445	ECT	6103
4	some text some text	0	INV445	ECT	8180
5	some text some text	2	MODULE18	HSSD	2962
6		2	MODULE18	HSSD	13603
7	RSN = 1	2	MODULE18	HSSD	764
8	ID = 0	2	MODULE18	HSSD	6200
9	NAME = INV445
10	TYPE = ECT
11	AMDEF = STR
12	RSGSN = 18560
13	AMRUL = TOMIZ
14	RREST = 0
15	PERAR = FALSE
16	ERANN = FALSE
17	RANNO = FALSE
18
19	RSGSN = 6103
20	AMRUL = TOMIZ
21	RREST = 0
22	PERAR = FALSE
23	ERANN = FALSE
24	RANNO = FALSE
25
26	RSGSN = 8180
27	AMRUL = TOMIZ
28	RREST = 0
29	PERAR = FALSE
30	ERANN = FALSE
31	RANNO = FALSE
32
33	some text some text
34
35	some text some text
36
37	--- END
38	some text some text
39
40	some text some text
41	some text some text
42	some text some text
43	some text some text
44
45	RSN = 1
46	ID = 2
47	NAME = MODULE18
48	TYPE = HSSD
49	AMDEF =SSN
50	RSGSN = 2962
51	AMRUL = STR
52	PERAR = FALSE
53	ERANN = FALSE
54	RANNO = FALSE
55
56	RSGSN = 13603
57	AMRUL = STR
58	PERAR = FALSE
59	ERANN = FALSE
60	RANNO = FALSE
61
62	RSGSN = 764
63	AMRUL = TOMIZ
64	RREST = 0
65	PERAR = FALSE
66	ERANN = FALSE
67	RANNO = FALSE
68
69	RSGSN = 6200
70	AMRUL = TOMIZ
71	RREST = 0
72	PERAR = FALSE
73	ERANN = FALSE
74	RANNO = FALSE
75
76	some text some text
77
78	some text some text
79
80	--- END
Sheet1

 

[Fractalis]

I ended up writing the macro, and it really did take less time than the formulas. If you'd like to try it:

Thanks so much @Eric W

I was writing my explaining about the structure when I saw your already answered with a vba solution hehe.

This time it seems to work pretty nice. Sometimes is better VBA hehe and I've selected your VBA solution as the final solution.

Thanks again for support and time to help others.

Minor change to the formula. Try this.

Thanks so mjuch @Cubist. It works with sample input, but I need to made some changes to the actual input but currently is showing correctly the RSGSN values in actual data. Is up to me now, thanks so much for the help ans time.

 

[Eric W]

I noticed a minor glitch in my macro (it might miss the last few lines of the input), so I went back and fixed that. While I was at it, I updated the macro to display either the Normal Output or the extended output. The macro is actually shorter, but it relies on every sub range having at least 1 of every type of field, and RSGSN must be last. If you want to try it:

Sub GetData()
Dim InputCell As Range, OutputCell As Range, MyTab As Variant, variables As Variant
Dim i As Long, j As Long, res() As String, loc As Long, w As String, r As Long, NormalOutput As Boolean

    Set InputCell = Sheets("Sheet9").Range("A2")            ' Where does the text data start
    Set OutputCell = Sheets("Sheet9").Range("M1")           ' Where do you want the results?
    variables = Array("ID", "NAME", "TYPE", "RSGSN")        ' What values are you looking for?
    NormalOutput = False                                    ' Set to True or False
   
    ReDim res(1 To 1, 0 To UBound(variables))               ' Set the output array
    OutputCell.Resize(, 4) = variables                      ' Set the values as headers
    MyTab = Range(InputCell, InputCell.Offset(1000000).End(xlUp)).Value ' Get the input table
   
    Application.ScreenUpdating = False                      ' Stop updating until we finish
    r = 1                                                   ' Set the first output row (as an offset)
    For i = 1 To UBound(MyTab)                              ' check each row in the input table
        w = WorksheetFunction.Trim(MyTab(i, 1))             ' remove excess spaces
        For j = 0 To UBound(variables)                      ' check to see if any of the values are found
            loc = InStr(1, w, variables(j) & " =", vbTextCompare)        ' where does the value start?
            If loc > 0 Then                                              ' Did we find one?
                res(1, j) = Trim(Mid(w, loc + Len(variables(j)) + 2))    ' save the found value
                If j = UBound(variables) Then                            ' always print when we find RSGSN
                    OutputCell.Offset(r).Resize(, UBound(res, 2) + 1).Value = res
                    r = r + 1
                    If NormalOutput = True Then ReDim res(1 To 1, 0 To UBound(variables))
                End If
                Exit For                                                 ' exit the loop
            End If
        Next j

    Next i
    Application.ScreenUpdating = True                       ' Resume updating
   
End Sub

Anyway, glad we could help!

 

[Fractalis]

I noticed a minor glitch in my macro (it might miss the last few lines of the input), so I went back and fixed that. While I was at it, I updated the macro to display either the Normal Output or the extended output. The macro is actually shorter, but it relies on every sub range having at least 1 of every type of field, and RSGSN must be last. If you want to try it:

Anyway, glad we could help!

Thanks one more time Eric. I've tried your last macro but is only printing headers. Does it work for you?

 

[Eric W]

It does, I get these 2 results from the initial test data:

Book1
	M	N	O	P
1	ID	NAME	TYPE	RSGSN
2	0	INV445	ECT	18560
3				6103
4				8180
5	2	MODULE18	HSSD	2962
6				13603
7				764
8				6200
Sheet9

Book1
	M	N	O	P
1	ID	NAME	TYPE	RSGSN
2	0	INV445	ECT	18560
3	0	INV445	ECT	6103
4	0	INV445	ECT	8180
5	2	MODULE18	HSSD	2962
6	2	MODULE18	HSSD	13603
7	2	MODULE18	HSSD	764
8	2	MODULE18	HSSD	6200
Sheet9

The only difference being the True/False flag. The logic I tweaked based on your explanation in post 10. It still works on my test data, but apparently not on a larger sample. If you want me to fix it, I'll need a sample of what you're seeing. If you're happy with the first macro, that's fine too.

 

[Fractalis]

It does, I get these 2 results from the initial test data:

The only difference being the True/False flag. The logic I tweaked based on your explanation in post 10. It still works on my test data, but apparently not on a larger sample. If you want me to fix it, I'll need a sample of what you're seeing. If you're happy with the first macro, that's fine too.

My bad, I think it was because of the sheet's name I was trying on. I changed "Sheet9" to activesheet and it works for me now. That option of select normal output or not is awesome. Thanks so much for all you help in this question I did. Best regards man

PS: Who is the man in your profile's photo? hehe

 

[Eric W]

Glad you got it figured out!

The picture is Harold Lloyd, one of the greats from the silent movie era. Over a hundred years later, and people are STILL copying his stunts! Check out his film "Safety Last" if you get a chance.