<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-AU</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="9" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="10" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="59" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;} </style> <![endif]--> Good Morning,
I’m currently having some difficulty trying to separate and ID numbers into differing columns in excel. I have a list of ID’s generated in another program and it dumps to a .csv file in the following format;
I want the ID number and hole depth to be removed from the raw data and correlated together in another cell under the hole ID’s letter. So holes A100, A101, A102... are grouped together with all other “A” ID holes and their depths. I have been using the following formula under column I with some success;
=IF(OR(MID(A2,1,1)=$I$1,MID(A2,1,2)=$I$1),A2,"")
I then use a VLOOKUP formula to find the depth to match the particular hole ID and it correlates in column J. This works through hole ID’s A to Z. The problem I’m having is when the hole ID’s have double letter ID’s i.e AA or AB they get grouped into the holes with ID “A”. Can someone give me some guidance on a way to use a formula to stop this from happening?
I’m currently having some difficulty trying to separate and ID numbers into differing columns in excel. I have a list of ID’s generated in another program and it dumps to a .csv file in the following format;
<table class="MsoNormalTable" style="width: 488pt; margin-left: 5.4pt; border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="651"> <tbody><tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> <tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> <tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> <tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> <tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> <tr style="height: 15pt;"> <td style="width: 37.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="50"> </td> <td style="width: 58.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="78"> </td> <td style="width: 63.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="85"> </td> <td style="width: 53.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="72"> </td> <td style="width: 62.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="84"> </td> <td style="width: 48.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="65"> </td> <td style="width: 36.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="49"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56"> </td> </tr> </tbody></table>
A
B
C
D
E
F
G
H
I
J
ID
NORTHING
EASTING
COLLAR RL
HOLE DEPTH
AZIMUTH
ANGLE
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
A
64
A100
760834.138
6425367.277
426.35
16.66
0
-90
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
A100
16.66
A101
760826.215
6425363.008
426.53
16.54
0
-90
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
A101
16.54
A102
760818.293
6425358.738
426.53
16.24
0
-90
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
A102
16.24
A103
760810.37
6425354.468
426.35
15.75
0
-90
</td> <td style="width: 41.8pt; padding: 0cm 5.4pt; height: 15pt;" nowrap="nowrap" valign="bottom" width="56">
A103
15.75
I want the ID number and hole depth to be removed from the raw data and correlated together in another cell under the hole ID’s letter. So holes A100, A101, A102... are grouped together with all other “A” ID holes and their depths. I have been using the following formula under column I with some success;
=IF(OR(MID(A2,1,1)=$I$1,MID(A2,1,2)=$I$1),A2,"")
I then use a VLOOKUP formula to find the depth to match the particular hole ID and it correlates in column J. This works through hole ID’s A to Z. The problem I’m having is when the hole ID’s have double letter ID’s i.e AA or AB they get grouped into the holes with ID “A”. Can someone give me some guidance on a way to use a formula to stop this from happening?
