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
A17
contains “how”,C17
contains “now”,E17
contains “brown”, andQ17
contains “cow”, you wantA18
= “how”,B18
= “now”,C18
= “brown”, andD18
= “cow” — just the same as ifF17
had contained “how”, “now”, “brown”, and “cow”. Right? … (Cont’d)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 ofSheet 2!Column A
? (4) What’s the deal with the fact thatB1
begins and ends with quotes, whenA1
doesn’t? Are we just supposed to ignore that? … (Cont’d)Ref_No
, does that “count”, or does it have to beattrid="Ref_No"
? — or attrid="Ref_No"
, with spaces before and after? How aboutattrid=Ref_No
(without quotes)? (6) Just out of curiosity, is itattrid
orattrib
? (7) What’s the deal withC1
? 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)A1
containing two instances ofattrid="Measurement_1"
. I suppose that, even ifA1
contained one andD1
contained another, you would wantMeasurement_1
reported only once in Row 2. But what happens if Row 83 also containsattrid="Measurement_1"
? Would you wantMeasurement_1
reported in Row 84 then? (00) Please do not respond in comments; edit your question to make it clearer.