[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

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
0
G_M
Asked:
G_M
  • 5
  • 5
1 Solution
 
NorieCommented:
What do you mean by the original format?

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?
0
 
NorieCommented:
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.
0
 
G_MAuthor Commented:
You want sheet1, sheet3 is the format sheet one needs to be in
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
G_MAuthor Commented:
I thought you might say that... how about working with this data then?

 wf-ch1.xlsx
0
 
NorieCommented:
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.
0
 
G_MAuthor Commented:
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"?
0
 
NorieCommented:
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.
0
 
G_MAuthor Commented:
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
0
 
NorieCommented:
Here you are, this is the VB.NET
        Dim objXL As Excel.Application
        Dim objWB As Excel.Workbook
        Dim wsSrc As Excel.Worksheet
        Dim wsDst As Excel.Worksheet
        Dim rngDst As Excel.Range
        Dim rngSrc As Excel.Range
        Dim NoRefs As Long
        Dim strPg As String
        Dim strDoc As String
        Dim strFile As String
        Dim arrRefs As Array
        Dim LastRow As Long
        Dim I As Long

        strFile = "C:\wf-ch1-Trans.xlsx"

        objXL = New Excel.Application

        objWB = objXL.Workbooks.Open(strFile)

        wsSrc = objWB.Worksheets("Sheet1")

        LastRow = wsSrc.Range("A" & wsSrc.Rows.Count).End(Excel.XlDirection.xlUp).Row

        wsDst = objWB.Worksheets.Add

        wsDst.Range("A1:C1").Value = Split("Reference,Page,Document", ",")

        rngDst = wsDst.Range("A2")

        For I = 2 To LastRow

            rngSrc = wsSrc.Range("A" & I)

            arrRefs = Split(rngSrc.Value.ToString, ";")
            strPg = rngSrc.Offset(, 1).Value.ToString
            strDoc = rngSrc.Offset(, 2).Value.ToString
            NoRefs = UBound(arrRefs) + 1

            With rngDst.Resize(NoRefs)
                .Value = objXL.WorksheetFunction.Transpose(arrRefs)
                .Offset(, 1).Value = strPg
                .Offset(, 2).Value = strDoc
            End With

            rngDst = rngDst.Offset(NoRefs)

        Next I

        objXL.Visible = True
        objXL.UserControl = True

        '		objWB.Close(True)

        '		objXL.Quit()

        objWB = Nothing
        objXL = Nothing
    End Sub

Open in new window


This is the VBA:
Option Explicit

Sub SplitAndTrans()
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngDst As Range
Dim rngSrc As Range
Dim NoRefs As Long
Dim strPg As String
Dim strDoc As String
Dim arrRefs
Dim LastRow As Long
Dim I As Long

    Set wsSrc = Worksheets("Sheet1")

    LastRow = wsSrc.Range("A" & Rows.Count).End(xlUp).Row

    Set wsDst = Worksheets.Add

    wsDst.Range("A1:C1").Value = Split("Reference,Page,Document", ",")

    Set rngDst = wsDst.Range("A2")

    For I = 2 To LastRow

        Set rngSrc = wsSrc.Range("A" & I)

        arrRefs = Split(rngSrc.Value, ";")
        strPg = rngSrc.Offset(, 1)
        strDoc = rngSrc.Offset(, 2)
        NoRefs = UBound(arrRefs) + 1

        With rngDst.Resize(NoRefs)
            .Value = Application.Transpose(arrRefs)
            .Offset(, 1) = strPg
            .Offset(, 2) = strDoc
        End With

        Set rngDst = rngDst.Offset(NoRefs)

    Next I

End Sub

Open in new window

0
 
G_MAuthor Commented:
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now