Dynamically pulling information when data is inconsistent

[anderma8]

I have a bit of a logic problem I'm trying to figure out. I have data that has inconsistent attributes and I want to figure out if I can programmatically extract the data: My data has a SINGLE column. Column A reflects the individual entries and the attributes associated with them. Column B is my data:

[TABLE="width: 375"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Arrived[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]damaged[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]En-route[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]TBD[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Grapes[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]TBD[/TD]
[/TR]
</tbody>[/TABLE]

I would prefer to get output leveraging only three rows:

Banans Yellow Shipped Arrived damaged
Apples Red En-route TBD
Grapes Green TBD

I was trying to leverage a nested IF statement and since I have over 100 entries, I don't want to copy/paste (transpose).... Any suggestions?

 

[Akuini]

Column A reflects the individual entries and the attributes associated with them.

What do you mean by 'the attributes'?
'the attribute' must be sequential numbers, i.e 1,2,3... etc?
If yes, then try this code:
I assumed data start at row 1 & put the result in col C onward.

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1109378a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1109378-dynamically-pulling-information-when-data-inconsistent.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        
        j = i
        [COLOR=Royalblue]Do[/COLOR]
            i = i + [COLOR=crimson]1[/COLOR]
            [COLOR=Royalblue]If[/COLOR] i = UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) + [COLOR=crimson]1[/COLOR] = va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
            
            k = k + [COLOR=crimson]1[/COLOR]
            Range(Cells(j, [COLOR=brown]"B"[/COLOR]), Cells(i, [COLOR=brown]"B"[/COLOR])).Copy
            Range([COLOR=brown]"C"[/COLOR] & k).PasteSpecial Transpose:=[COLOR=Royalblue]True[/COLOR]
       
    [COLOR=Royalblue]Next[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Akuini]

Sorry, my code in post 2 is flawed.
Try this one:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1109378a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1109378-dynamically-pulling-information-when-data-inconsistent.html[/COLOR][/I]

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
va = Range([COLOR=brown]"A1"[/COLOR], Cells(Rows.count, [COLOR=brown]"A"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))
    
    [COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
        
        j = i
        [COLOR=Royalblue]Do[/COLOR]
        i = i + [COLOR=crimson]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i > UBound(va, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = va(i - [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) + [COLOR=crimson]1[/COLOR]
        i = i - [COLOR=crimson]1[/COLOR]
            
            k = k + [COLOR=crimson]1[/COLOR]
            Range(Cells(j, [COLOR=brown]"B"[/COLOR]), Cells(i, [COLOR=brown]"B"[/COLOR])).Copy
            Range([COLOR=brown]"C"[/COLOR] & k).PasteSpecial Transpose:=[COLOR=Royalblue]True[/COLOR]
       
    [COLOR=Royalblue]Next[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[anderma8]

THANKS for your feedback! I put the sequential numbering in column A to help explain the data but in reality, I will only have column B to work from. I will look at your solution tomorrow and let you know. Much appreciated!!

 

[Akuini]

Hmm, in that case, what criteria do you use to "group" the data?

 

[anderma8]

it's an extract that I receive so it's not a set output which is one of my issues. Sometimes it's with 3, 4 or 5 attributes.

 

[Fluff]

Is there anything that shows the start or end of a group?

 

[anderma8]

I think I found a way: If I concatenate the fields, then I can sort them and remove the ones that do not make sense. Once I have the base data, I can break it out into columns via a break in my separator (,). Sorry for the delayed reply, I thought of this last night and am studying for a completely unrelated class. Thanks for your help!!!

 

[Fluff]

Glad you sorted it & thanks for the feedback