<div>Hi there everyone,</div><div><br></div><div>I've been struggling with the following; might be a bit of a challenge. 
<div><br></div>I have an excel file that was exported from some XML application.</div><div>It contains expressions like those below. Each expression is in a separate cell in a single row.</div><div><br></div><div>Now, in a column, say in a second tab, I have a list/range of a couple hundred strings/attributes like <strong>Measurement_1</strong>, <strong>Ref_No </strong>and<strong> Maximum_Velocity</strong>. I need to search within each cell of this row, find every attribute from my list that appears in these cells and get them in the next row, each in a cell of its own.</div><div><br></div><div>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.</div><div><br></div><div>Ideally duplicates both within each cell and within each row should be ignored, so Row 2 should only have unique values (so <strong>Measurement_1</strong> would only appear once), but I can use the remove duplicates functionality if this is not feasible.</div><div><br></div><div>I don't know if this helps at all, but all of the required attributes are in double quote marks ("Example"), though not all of the quote marked are useful attributes. For example, "<strong>valueandunit</strong>" and <strong>" of"</strong> are useless to me and aren't in my list.</div><div><br></div><div><strong>Many thanks for your help!</strong></div><div><br></div><div>-------------<br><br></div><div><strong>Example row</strong></div><div><br></div><div>Cell A1:</div><div><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" - "></ref></ref></div><div><br></div><div>Cell B1:</div><div><ref attrid="Standard_Deviation" equalsign=" - " includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Validation_2" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=", "></ref></ref></div><div><br></div><div>C1:</div><div>Maximum <ref attrid="Compatibility" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> routing capacity and <ref attrid="Maximum_Velocity" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> plunge stroke</ref></ref></div><div><br></div><div>D1:</div><div>"<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 ">"</div><div><br></div><div>E1:</div><div>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"),".")))</div><div><br></div><div><strong>The required output would look like this:</strong></div><div><br></div><div>Cell A2: Measurement_1</div><div>B2: Standard_Deviation</div><div>C2:Validation_2</div><div>D2:Compatibility</div><div>E2:Maximum_Velocity</div><div>F2:Ref_No</div><div>G2:Object_Diameter</div><div>H2:Object_Length<br>I2:Device_Type<br>J2:Max_Temperature_Range<br>K2:Min_Temperature_Range<br><br>-------------</div>
<div><br></div>I have an excel file that was exported from some XML application.</div><div>It contains expressions like those below. Each expression is in a separate cell in a single row.</div><div><br></div><div>Now, in a column, say in a second tab, I have a list/range of a couple hundred strings/attributes like <strong>Measurement_1</strong>, <strong>Ref_No </strong>and<strong> Maximum_Velocity</strong>. I need to search within each cell of this row, find every attribute from my list that appears in these cells and get them in the next row, each in a cell of its own.</div><div><br></div><div>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.</div><div><br></div><div>Ideally duplicates both within each cell and within each row should be ignored, so Row 2 should only have unique values (so <strong>Measurement_1</strong> would only appear once), but I can use the remove duplicates functionality if this is not feasible.</div><div><br></div><div>I don't know if this helps at all, but all of the required attributes are in double quote marks ("Example"), though not all of the quote marked are useful attributes. For example, "<strong>valueandunit</strong>" and <strong>" of"</strong> are useless to me and aren't in my list.</div><div><br></div><div><strong>Many thanks for your help!</strong></div><div><br></div><div>-------------<br><br></div><div><strong>Example row</strong></div><div><br></div><div>Cell A1:</div><div><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Measurement_1" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=" - "></ref></ref></div><div><br></div><div>Cell B1:</div><div><ref attrid="Standard_Deviation" equalsign=" - " includeattrname="false" resolveto="valueandunit" separator=""><ref attrid="Validation_2" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=", "></ref></ref></div><div><br></div><div>C1:</div><div>Maximum <ref attrid="Compatibility" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> routing capacity and <ref attrid="Maximum_Velocity" equalsign="=" includeattrname="false" resolveto="valueandunit" separator=""> plunge stroke</ref></ref></div><div><br></div><div>D1:</div><div>"<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 ">"</div><div><br></div><div>E1:</div><div>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"),".")))</div><div><br></div><div><strong>The required output would look like this:</strong></div><div><br></div><div>Cell A2: Measurement_1</div><div>B2: Standard_Deviation</div><div>C2:Validation_2</div><div>D2:Compatibility</div><div>E2:Maximum_Velocity</div><div>F2:Ref_No</div><div>G2:Object_Diameter</div><div>H2:Object_Length<br>I2:Device_Type<br>J2:Max_Temperature_Range<br>K2:Min_Temperature_Range<br><br>-------------</div>
