Hello all,
This is my first post on the discussion boards and I am here asking for help surrounding this issue I have with manipulating the data I've managed to convert from PDF to Excel (PDF to Text) using the Intelligence Suite. I've attached a sample of the data I have managed to convert from PDF to Text as the entire data set is about 822 pages, please refer to that to facilitate the understanding of my upcoming question.
In short, I've cleaned up the PDF to Text data and have successfully arranged each piece of information that I want to ultimately become a field, into Column 1, with the corresponding data/information pertaining to that field in Column 2. So the items in Column 1, should be transposed into the header and become a field, and the items in Column 2 is the information pertaining to that field. However, I also have another set of data in Column 3 and Column 4 that is structured the same way, however, I cannot think of a way to also transpose the items in Column 3 across the top to also become part of the header fields, while also maintaining the corresponding information items to those fields in Column 4 (hope this makes sense). Take lines 9 - 17, this is essentially one record, that is laid out vertically across two columns. These two columns are what I want to turn into fields. The end result would be 16 fields (B9-B17 and D9-D16), with the corresponding items in Column 2 (C) and Column 4(E) being the record information.
Can anyone help in achieving my desired end result? It would help me structure these reports into a typical lay out; fields on x axis and records on y axis. Thanks a million!
UPDATE: I have added an example of what I am trying to achieve as my end result through a workflow.
