0

I've been struggling with the following; might be a bit of a challenge.

I have an Excel file that was exported from an XML application.

Sheet 1 contains rows of strings like those below. Every odd-numbered row has about 10 of those strings, but this can vary. (I'm looking for a solution to fill in the even-numbered rows; see below.)

Sheet 2, Column A, contains a range of strings like Measurement_1, Ref_No and Maximum_Velocity, called Attributes.

I need to look up each one of these attributes in each cell of every odd-numbered row of Sheet 1, and paste every unique attribute that appears as a substring in these strings in a new row below, each in a cell of its own.

Note that every value in the generated rows needs to be unique.

Is there some function, regex or VBA script that can do that? I have searched extensively in the forums and elsewhere and failed to do it myself.

I hope this all makes sense, but I am happy to explain anything that's gibberish!


Row 1

Cell A1:
<ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" - "></ref></ref>

Cell B1:
"<ref attrid="Ref_No" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Unit" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" "><ref attrid="Vector" equalsign="=" includeattrname="false" resolveto="value" separator=" (Dia)"><ref attrid="Object_Diameter" equalsign="=" includeattrname="false" resolveto="unit" separator=""><ref attrid="Thread_Size" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=","><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" "><ref attrid="Object_Length" equalsign="=" includeattrname="false" resolveto="value" separator=" of ">"

Cell C1:
CONCATENATE("This ", LOWER(VAL("Device_Type")),F(AND(EXACT(VAL("Max_Temperature_Range"),("")),EXACT(VAL("Min_Temperature_Range"),"")),".",CONCATENATE("appeared to operate safely from a minimum temperature of ", VAL("Min_Temperature_Range"),UNIT("Min_Temperature_Range")," to a maximum temperature of ", VAL("Max_Temperature_Range"),UNIT("Max_Temperature_Range"),".")))

The desired output in Row 2 would look like this:

Cell A2:Measurement_1
Cell B2:Ref_No
Cell C2:Object_Diameter
Cell D2:Object_Length
Cell E2:Device_Type
Cell F2:Max_Temperature_Range
Cell G2:Min_Temperature_Range
4
  • (0) +1 for understatement of the month: “might be a bit of a challenge.”  (1) It seems like the cell structure of the odd-numbered rows is a red herring.  If A17 contains “how”, C17 contains “now”, E17 contains “brown”, and Q17 contains “cow”, you want A18 = “how”, B18 = “now”, C18 = “brown”, and D18 = “cow” — just the same as if F17 had contained “how”, “now”, “brown”, and “cow”.  Right?  … (Cont’d) Jun 3, 2015 at 14:15
  • (Cont’d) …  (2) Why don’t you want your output to contain “Unit” and “Vector”, as they do in Mike’s answer?  Is it because they aren’t in Sheet 2!Column A?  If so, you should have given us that (complete) column as part of the example.  (3) Are the results in Row 42 required to be in the order of first appearance in Row 41, or is it OK if they are in the order of Sheet 2!Column A?  (4) What’s the deal with the fact that B1 begins and ends with quotes, when A1 doesn’t?  Are we just supposed to ignore that?  … (Cont’d) Jun 3, 2015 at 14:15
  • (Cont’d) …  (5) If an odd-numbered row contains just Ref_No, does that “count”, or does it have to be attrid="Ref_No"? — or ⁠ attrid="Ref_No" , with spaces before and after?  How about attrid=Ref_No (without quotes)?  (6) Just out of curiosity, is it attrid or attrib?  (7) What’s the deal with C1?  Is that another red herring — a noise entry that has to be ignored — or is there a possibility that a string like that might contain a substring that needs to be found?  … (Cont’d) Jun 3, 2015 at 14:16
  • (Cont’d) …  (8) You have shown A1 containing two instances of attrid="Measurement_1".  I suppose that, even if A1 contained one and D1 contained another, you would want Measurement_1 reported only once in Row 2.  But what happens if Row 83 also contains attrid="Measurement_1"?  Would you want Measurement_1 reported in Row 84 then?  (00) Please do not respond in comments; edit your question to make it clearer. Jun 3, 2015 at 14:18

1 Answer 1

0

Your XML strings look odd - lots of incomplete tags?

Anway I would start from the XML file and load it using the Power Query Add-In. This has a specific "From XML" handler and you can easily shape the data along the way using the Power Query commands.

Here's an example I built using an edited version of your XML content (I added a root element and closing tags to make it a valid XML file).

Power Query - from XML file example

The only action that seemed necessary to achieve what you want was to select the Attribute:attrid column and choose Remove Columns / Remove Other Columns.

You can see the result in the background - an Excel table. Any time the XML file changes, you just hit Refresh to re-run the Power Query.

1
  • Thanks for the answer, unfortunately though, I cannot install add-ins in my organisation. Any ideas that do not require the use of add-ins? Furthermore, I didn't realise that Excel might actually be able to actually 'understand' XML; I rather thought it would be a look-up process, so I paid no attention to syntax when copying and pasting.
    – DimitrisX
    Jun 1, 2015 at 12:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .