Extract value with no same numbers row between values

[PsychX]

Hi All,

i have problem with my excel table. I have table(sheet BA11) with almost 4k row and in this table are subtable repeated every 30-36 row. Every subtable has same layout but not same number of row.
I need extract from table 2 value and this is Name and Total. Name alwas start NAME: xxx xxx and his position is A4, A39, A73 etc. and Total was in G30, G35, G99 etc. i need extract this list of name and his total to next table(sheet)

Name xxx xxx 15
Name xxy xxy 11
Name xxz xxz 17

Can me anybody help me? i Can try use index, match, offset but i have always problem with not same numbers of row between values.

Thanks.

Marcel

 

[Fluff]

Duplicated post https://www.mrexcel.com/forum/excel-questions/1038253-extract-data-table-another.html#post4983702

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it. Please be patient and do not attempt to post the question again.