Hey guys, back again.
I am developing a small application using VB.Net, that extracts data from Word, imports it into Excel and sorts it for output to another document. I am at a point now that I need some assistance on something that I believe will require a bit of sideways thinking.
The data that I am sorting is attached, so you can see what I am attempting to explain here. Essentially, I originally had the extracted data in column "A", the page it was associated with in column "B" and document it came from in column "C". Column "D" was used to filter that data providing a "Yes" or "No" value based on an excel formula.
The data I have been filtering is a heap of references to other documents referenced in a thesis. The data was extracted based on it being contained in parenthesis/brackets (see: http://rdsrc.us/jxy7jj
). One of the problems with extracting data this way was that some parenthesis contained multiple references, thus the cell in column "A" contained data like:
"reference1, 2001; reference2, 2002; reference3, 2010"
So I seperated this data from the rest of the filtered data and exported it to a text file where I replaced all the ";" values with vbTab. I then copied all the data back into the worksheet. This gives me the above data looking like this:
"A1" = "reference1, 2001"
"A2" = "reference2, 2002"
"A3" = "reference3, 2010"
"A4" = "Page 1"
"A5" = "source.docx"
"A6" = "Yes"
Now I need to transpose that data so it is all contained in the original format described earlier:
"A#" = reference
"B#" = Page Number
"C#" = source document
The "D" column is not really relevant, but can be copied if that will make things tidier
My first thought is to use the Yes column as a reference point by replacing it with something unique, lets say |%%|. Starting in B2, loop until Range.Offset(, 2).Value = "|%%|", being the Page column, Then goto C2 and so on. The datails in between I am lacking, so some help would be appreciated.
This is a difficult one in my mind and I wish I could give more points. Hopefully it isn't too hard for someone else to get their head around. I hope there is enough information here to give a clear idea of what I'm trying to achieve.