Using VBA to extract various fields in a Table

Hello, this is my second post and was impressed by the response to my first post yesterday.

I will outline the issues using sample data and have attached a file.
VBA is new to me although I am familiar with Arduino and basic.

By way of introduction to the data and its meaning, Questionnaires are completed by a number of people and these are listed in Column A headed �Record ID�


Each RecordID (Person) may connect with a service provider, these are listed in Cells H1 to M1 - an example is H1 which has "Senior Community Engagement Officer, Department of Communities�. This data is classed as a Role and Organisation (R&O) - note the number of R&Os can vary from 2 to 20 and the RecordID can vary from 1 to 25.
I have managed to obtain a subroutine via this Forum (thank you Jindon) to tease these out and list them separately under Column headings �Role� & �Organisation�/



If you read along each RecordID you can see where a service provider has been connected with. There may be more than one service provider. Secondly, each time they connect with a service provider there is a Frequency Eg; Daily, 2-3 times a week, weekly, monthly, quarterly, yearly).
UPDATE (31-03-24) I HAVE RECEIVED A SUBROUTINE THAT SOLVES THE IMMEDIATE ISSUE - SEE THREAD.

ANOTHER REQUIREMENT HAS ARRIVED WHERE IN THE "Data" TAB THERE ARE TWO MORE TABLES TO ASCERTAIN WHETHER THE "Connection" IS "Formal" OR "Informal" Responses can be "yes, "No" or a blank cell.


I have provided a second Tab named �Example Result� to illustrate the solution I am looking for. I currently do this manually and you can imagine that larger data sheets are very challenging!

Here is the first part of the solution - the second tab shows more. Please note, I didn�t finish the solution for "formal" and "Informal" Connections as I only wanted to provide an illustration.


You will see that a RecordID may be listed more than once dependent upon the Connections they have made. Similarly, a RecordID may not appear as in RecordID = 4 (presumably the questionnaire was incomplete.

Thanks you for your interest

See if this works,

Please Login or Register  to view this content.