Find matching string and get string of data in offset cell from raw dataset

robystar · Feb 23rd 2023

Attached is a shortened version of the imported dataset. Please note how the data in the RAWDATA tab is grouped with multiple labels and values in a single cell.

In the JSN_FILTER tab I need to find the matching JSN code in the RAWDATA which is located in a single cell along with miscellaneous data. Once this code is located I need to obtain the "BTU / Hour" data. This data is located is single cell along with miscellaneous data as well. The "BTU / Hour" text here is followed by (5) spaces. Unfortunately, the "BTU / Hour" data is inconsistent. It's sometime 0, blank, or a (3) or (4) digit number. A blank should be interpreted as 0. Any help would be greatly appreciated.

**Carim** · Feb 24th 2023

Hi,

Since you are very probably using Excel 365, would you attaching a version of your Test file in which, beforehand, you would have "Copy-Paste Values" in your tab JSN_FILTER

This would allow to see the point you have already reached ... in your "Extraction-Saga"

robystar · Feb 24th 2023

Quote from Carim

Hi,

Since you are very probably using Excel 365, would you attaching a version of your Test file in which, beforehand, you would have "Copy-Paste Values" in your tab JSN_FILTER

This would allow to see the point you have already reached ... in your "Extraction-Saga"

See attached with JSN_CopyPasteData tab containing just the filtered values that I have been able to extract so far. Hopefully I've interpreted your request correctly. "Extraction-Saga" is the correct term.

**Carim** · Feb 24th 2023

Thank you very much for the test file

However, the issue is that I cannot see the actual source from which you do need to extract BTU/Hour ...

By the way, for sure you know that BTU is kind of 'old-fashioned' ... the modern international unit for Heat Energy is the Joule.

robystar · Feb 24th 2023

Okay, I understand. See attached CSV file with txt extension containing the RAWDATA only. It's very messy, but should work for referencing the relative location for the data. This information is extracted from a U.S. Military Standard document so you can expect most of the information to be at least 10 years behind the modern world lol.

**Carim** · Feb 24th 2023

WHAOU ... Your "Extraction-Saga" is far worse than what I thought

Have you tried to open your Text file with WORD ... using Unicode UTF-8 ...

robystar · Feb 24th 2023

I have done so. I think it looks more chaotic than it is. If the data were randomly placed then I think this would be a somewhat hopeless effort. My thinking that the data extraction is even possible is the consistency between the data sets. The JSN code is always preceded by the JSN title followed by a consistent number of spaces. The BTU / Hour data is always located in a single cell starting the text "Utilities" the same number of cells down from the JSN number. I'm thinking if the JSN number can be matched then the BTU / Hour value fan be located consistently by finding the string in the cell offset from the JSN cell.

**Carim** · Feb 24th 2023

Thanks for all you additional explanations.

So, if we were to recap :

1. The cell Utilities is always 7 Rows below the JSN cell

2. The BTU / Hour is always located at the 1'237 position

3. The following item Phase 1 is always located at a given position, following your target

4. The Number you do need to extract is always located between the two above positions

As an example, to avoid all Zeros ... row 282

Code

=MID(RAWDATA!A282,(SEARCH("BTU / Hour",RAWDATA!A282)+10),SEARCH("Phase1",RAWDATA!A282)-(SEARCH("BTU / Hour",RAWDATA!A282)+10))

Does it make some sense ???

**Carim** · Feb 24th 2023

Based on the assumptions made in previous message and provided the worksheet structure remains coherent ...

attached is your test file with a macro ...

Hope this will help

**Carim** · Feb 25th 2023

Once you have tested the macro ... feel free to share your comments

robystar · Feb 27th 2023

Quote from Carim

Based on the assumptions made in previous message and provided the worksheet structure remains coherent ...

attached is your test file with a macro ...

Hope this will help

I would say this worked exactly as I had hoped. It took about 30 seconds to complete through an adjusted range of 22737 rows. I only received (2) Value#! errors likely due to inconsistent formatted data. Not worth addressing with error handling. Thank you so much for your effort.

**Carim** · Feb 27th 2023

Thanks for your Thanks AND for the Like

Pleased to hear you have managed to fix your initial problem.

Now ... did not know you were dealing with such a large database ... Would you need an Array-based solution ?

robystar · Feb 27th 2023

You're welcome. That would actually work better due to macro security issues on our network. Much appreciated.

**Carim** · Feb 27th 2023

For security I do not know if this could make a difference ...

As far as speed is concerned, you could have a go with following

Code

Sub arrBTU()
Dim c As Range
Dim arr(1 To 25000)
Dim i As Long
    For Each c In Sheet1.Range("A2:A25000")
        If Left(c, 3) = "JSN" Then
           i = i + 1
           arr(i) = Evaluate("=Clean(MID(RAWDATA!A" & c.Row + 7 & ",(SEARCH(""BTU / Hour"",RAWDATA!A" & c.Row + 7 & ")+10),SEARCH(""Phase1"",RAWDATA!A" & c.Row + 7 & ")-(SEARCH(""BTU / Hour"",RAWDATA!A" & c.Row + 7 & ")+10)))")
        End If
    Next c
    ' Display
    Sheet2.Range("G2:G" & i + 1) = Application.Transpose(arr)
End Sub

Display More

robystar · Feb 27th 2023

Okay, I thought you meant cell based array formula (no macro). After a few trials it seems the execution times for each code are about the same for me. Could be my workstation.

**Carim** · Mar 13th 2023

Hi again,

For your 25'000 + rows ... Arrays should speed up the process

Code

Sub Arr2BTU()
' Two Arrays to Speed Up Processing 25'000 + Rows - Ozgrid 28 Feb 2023
Dim arr(), at()
Dim i As Long, x As Long
Dim lastrow As Long
lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
arr = Sheet1.Range("A2:A" & lastrow)
    For x = LBound(arr) To UBound(arr)
        If Left(arr(x, 1), 3) = "JSN" Then
           i = i + 1
           ReDim Preserve at(0 To i)
           at(i) = Replace(Trim(Mid(arr(x + 7, 1), (InStr(1, arr(x + 7, 1), "BTU / Hour") + 10), (InStr(1, arr(x + 7, 1), "Phase1") - InStr(1, arr(x + 7, 1), "BTU / Hour")) - 10)), Chr(10), "")
        End If
    Next x
    ' Display Result in Sheet2 - Column G
    Sheet2.Range("G2:G" & i + 1) = Application.Transpose(at)
End Sub

Display More

Hope this will help

Find matching string and get string of data in offset cell from raw dataset

Participate now!

Share

Similar Threads

Help Extracting Data From String

Tags