Extract information from cells

[Brianfree]

Hi, I have some data from an xml file in excel which I need to extract certain data, please see example below..

<vXREF>
<Comp>aaa</Comp>
<CompPart>12345</CompPart>
<Xref>98765</Xref>
</vXREF>
<vXREF>
<Comp>bbb</Comp>
<CompPart>22222</CompPart>
<Xref>33333</Xref>
</vXREF>
<vXREF>
<Comp>aaa</Comp>
<CompPart>67890</CompPart>
<Xref>09878</Xref>
</vXREF>

Is it possible to covert to something like... (only where Comp = aaa) or this could be filtered out afterwards?

Comp, CompPart, Xref
aaa, 12345, 98765
aaa, 67890, 09878


Please can any one help?

Many thanks

Alan

 

[SkipVought]

hi,

Have you tried using Data > Text to columns as a start?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Brianfree]

Hi, thanks for your reply.. I have managed to convert to the following


example..

aaa
12345
09876
bbb
23456
09876

I have tried rows to columns be I am struggling to achieve the following..

aaa, 12345, 09876
aaa, 23456, 09876


Many thanks

Brian (Alan)

 

[SkipVought]

Use the IF function to do/start sonething when you encounter the value you want.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Brianfree]

Hi, I have figured it out using the offset function.

Thanks for helping!

Many thanks

Brian

 

[SkipVought]

It would help other members who like to browse to glean good ideas, if you were to post your OFFSET() solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[strongm]

A little VBA may also help, eg:

[blue]Option Explicit

Public Sub Example()
    ParseAndDisplay "<vXREF><Comp>aaa</Comp><CompPart>12345</CompPart><Xref>98765</Xref></vXREF><vXREF><Comp>bbb</Comp><CompPart>22222</CompPart><Xref>33333</Xref></vXREF><vXREF><Comp>aaa</Comp><CompPart>67890</CompPart><Xref>09878</Xref></vXREF>"
End Sub

' requires reference to Microsoft XML library
Private Sub ParseAndDisplay(strXML As String)
    Dim importxml As New DOMDocument
    Dim parts As Object
    Dim partline As IXMLDOMElement
    Dim myRange As Range
    
    Set myRange = Selection
    importxml.LoadXML "<ExampleImport>" & strXML & "</ExampleImport>"

    Set parts = importxml.getElementsByTagName("vXREF")
    
    For Each partline In parts
        If partline.ChildNodes(0).Text = "aaa" Then
            myRange.Range("A1") = partline.ChildNodes(0).Text
            myRange.Range("B1") = partline.ChildNodes(1).Text
            myRange.Range("C1") = partline.ChildNodes(2).Text
            Set myRange = myRange.Offset(1)
        End If
    Next
End Sub[/blue]