0

I have the following tabular data:

Raw data:

Description
String A Q1FY18
String B Q1FY18, String B Q2FY18
String C Q4FY17, String C Q1FY18, String D Q2FY18
String E Q2FY18

I would like to split this column into multiple columns. The target data frame would look like this:

Desired output:

Description  Period
String A     Q1FY18
String B     Q1FY18
String B     Q2FY18
String C     Q4FY17
String C     Q1FY18
String D     Q2FY18
String E     Q2FY18

What comes to mind is that the original column is comma delimited, so there must be a way to create new columns by splitting the strings using the comma as the delimiter. This step would look something like this:

First step

Description  Period1 Period2
String A     Q1FY18
String B     Q1FY18  Q2FY18
String C     Q4FY17  Q1FY18
String E     Q1FY18  Q2FY18

The next step would be to collapse the table to get the desired output. It would be easier if I could use other tools for data manipulation such as R or Python, but those are not an option in this case due to end-user limitations. How would you go about transforming the raw data to get to the desired output? Is there some way to split the raw data using the comma as the delimiter and having access automatically determine the number of new columns it should create? I would appreciate your help!

Agarp
  • 433
  • 7
  • 15
  • https://stackoverflow.com/questions/38439091/split-field-into-multiple-records-in-access-db – Rene Apr 27 '18 at 05:49

1 Answers1

0

You could try something like this:

Dim strArray() As String

   strTest = "String B Q1FY18, String B Q2FY18"
   strArray = Split(strTest, ",")

   For intCount = LBound(strArray) To UBound(strArray)
      Debug.Print Trim(strArray(intCount))
   Next
Daniel PC
  • 46
  • 4