it seems like VBA is the way to go. Can I use the Visual Studio Express to do this? Or do I need to buy the full version?
you have vba in 2016 so don't understand why you would want vs express, and anyway, it can be done in SQL
You have fairly straight forward data - always 4 rows per record so do the following
1. add a heading to your data column - call it say 'data'. Save the file - we'll call it NameList - and close
2. Now in Access, import the file (External data tab>Excel) and follow the prompts, ensure you tick the 'has headers' box and let access choose a primary key (which will be called ID) and the table name will be sheet1 - if not substitute your name for sheet1 in the following query
Code:
SELECT [F].[RecID]+1 AS ContactPK, F.Data AS Facility, A1.Data AS Addr1, A2.Data AS Addr2, P.Data AS Phone INTO Contacts
FROM (((SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
FROM Sheet1
WHERE ((([ID]-1) Mod 5)+1)=1) AS F INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
FROM Sheet1
WHERE ((([ID]-1) Mod 5)+1)=2) AS A1 ON F.RecID = A1.RecID) INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
FROM Sheet1
WHERE ((([ID]-1) Mod 5)+1)=3) AS A2 ON A1.RecID = A2.RecID) INNER JOIN (SELECT ([ID]-1)\5 AS RecID, (([ID]-1) Mod 5)+1 AS RowID, Data
FROM Sheet1
WHERE ((([ID]-1) Mod 5)+1)=4) AS P ON A2.RecID = P.RecID
When you run this query, it will create a table called contacts ait a unique contact primary key and your data