VB.Net - Transpose data in Excel with a catch
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.
Cheers
G_M
wf-ch1.xlsx
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.
Cheers
G_M
wf-ch1.xlsx
Had a chance to look at the document and I'm a bit confused.
Which worksheet should I be looking it?
Sheet2 just seems to have a long list of references with the page and document repeated for each.
Sheet1 doesn't seem to have any page/document data and the references are across rather than down.
If you were to keep the multiple references together in one cell this would actually be a straightforward task.
In fact I think I have some VBA code for it which could be adapted for .NET.
Which worksheet should I be looking it?
Sheet2 just seems to have a long list of references with the page and document repeated for each.
Sheet1 doesn't seem to have any page/document data and the references are across rather than down.
If you were to keep the multiple references together in one cell this would actually be a straightforward task.
In fact I think I have some VBA code for it which could be adapted for .NET.
ASKER
You want sheet1, sheet3 is the format sheet one needs to be in
ASKER
Separated by semi-colons?
By the way, why not just delete the Keep column?
Did I not post some code for that previously?
Anyway, I couldn't find any existing code so I came up with some.
It's VBA but I'll convert it to VB.NET, I can post the VBA if you want.
By the way, why not just delete the Keep column?
Did I not post some code for that previously?
Anyway, I couldn't find any existing code so I came up with some.
It's VBA but I'll convert it to VB.NET, I can post the VBA if you want.
ASKER
I have been using the keep column for other filters... it will be deleted eventually. The way my mind wrapped around this I thought it beneficial to keep the column for a solid reference. Apparently not ;o)
The VBA wouldn't be too much help to me as I can bearly get my head around the language I'm using :o) However, most of the functions I'm trying to carry out have an abundance of VBA examples on the net in comparison to VB.Net... So knowing how to translate would be benificial.
Is this something I should consider doing myself or should I just leave it to the "Experts"?
The VBA wouldn't be too much help to me as I can bearly get my head around the language I'm using :o) However, most of the functions I'm trying to carry out have an abundance of VBA examples on the net in comparison to VB.Net... So knowing how to translate would be benificial.
Is this something I should consider doing myself or should I just leave it to the "Experts"?
I just thought the column, or the data in it anyway was now kind of redundant.
It doesn't really make much difference if you delete it or keep it.
As for the VBA/VB.NET thing, what I could do is post both.
Then you could compare them and perhaps see how they relate to each other.
They are pretty close in general syntax but some other things are quite different.
No sure what you want to do yourself or leave to the Experts.
If you mean learn how to convert between the 2 I'd say a bit of both - try and learn what you can yourself and when/if you
get stuck ask anyone.
It doesn't really make much difference if you delete it or keep it.
As for the VBA/VB.NET thing, what I could do is post both.
Then you could compare them and perhaps see how they relate to each other.
They are pretty close in general syntax but some other things are quite different.
No sure what you want to do yourself or leave to the Experts.
If you mean learn how to convert between the 2 I'd say a bit of both - try and learn what you can yourself and when/if you
get stuck ask anyone.
ASKER
That sounds like a good idea. Due to the lack of VB.Net information in relation to these topics, I have been trying to get things working throught the interpretation of other languages anyway... So booth examples would be great. Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works great! Thank you again imnorie, you're a life saver :o)
I'll have a look at the VBA and hopefully I can get some clue as to how to relate the two in future. Thank you for the effort.
Cheers
G_M
I'll have a look at the VBA and hopefully I can get some clue as to how to relate the two in future. Thank you for the effort.
Cheers
G_M
Do you mean the original format in Word?
If you do couldn't you just skip the part that splits up multiple references?
Or am I missing something?