Selecting range of rows based on search criteria

[chrisbaz4444]

Hi,

I have a data set which is exported from a door control system which only exports into a text file.
I can convert this to a csv file which takes all the information and creates a single column with one row per line of data. There are 154543 rows.

What I'm trying to achieve is extracting a column of information which is made of specific rows of data contained within.

The information I'm after is 3 rows per 'door' on the system.
Each 'door' consists of 79 rows.
There are other 'devices' in the sheet which I'm not interested in which accounts for the additional rows.

Each 'door' contains a row that is identical across all doors, I'd like to run a search / filter for that, but then include 1 row above and 2 rows below the searchable term.
A wildcard search won't work as each 'door' is unique as well as having several other rows associated with the same name

I've attached an image to help explain what I'm trying to achieve, I've extracted a single 'door' set of rows.
The rows aren't in a specific pattern, eg all 'doors' together.

I may be going about this in an inefficient way - searching hasn't revealed a method that suits what I'm trying to do so far and perhaps I'm not using the correct search terms.

Thanks in advance.
Example 1.jpg

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[chrisbaz4444]

alanisdman, thank you for the advice, I've now uploaded an extract of the data.

[JohnTopley]

It would be more helpful if you could add a sample of expected output

[chrisbaz4444]

Ok, I've uploaded a sheet with expected output - column F but it can be any column.

Thanks

[JohnTopley]

In F2

Please Login or Register  to view this content.

in G2

Please Login or Register  to view this content.

in H2

=INDEX($A$1:$A$1000,MATCH("*Rdr Out*",OFFSET($A$1,(ROWS($1:1)-1)*79,0,79,1),0)+(ROWS($1:1)-1)*79)

[chrisbaz4444]

Thank you for that, I'm afraid the sheet I've supplied you isn't correct - the data isn't stored in a measured, sequential layout. eg. every 80 rows.
each 'door' is in a inconsistent location. Each door is a consistent number of rows.

I've attached an updated sample to hopefully explain what I mean.

Thanks

[JohnTopley]

It likely you will need VBA to do this (a) because of the random rows for "xxx Door" (b) the large data volume (154K rows).

with formulae it is difficult to position the next MATCH so it ignores previous (already found) ones.

[ChemistB]

Heere is a formula solution. The formula for the first instance is different from the following instances. You stated that the actual number of rows for each door is the same so I assumed that the Rdr In value is always 32 rows after the door and the RDr Out value is always 42 after the door

In F2,

Please Login or Register  to view this content.

In F3 copied down

Please Login or Register  to view this content.

In G2

Please Login or Register  to view this content.

In G3 copied down

Please Login or Register  to view this content.

[chrisbaz4444]

Hi, Thanks to all for your help with this.

The last formula works with a few issues.

There are other entries in the data with the word Door.

I can do a search and replace of all instances to a specific value.

EG, change all rows with
type: Door

and replace with the word unique or similar so its different to any other instances of the word door.

When I do this I update the formula entry which has *Door to be unique

=INDEX($A$1:$A$100000,MATCH("*Door",OFFSET($A$1,IFERROR(MATCH(F1,$A$1:$A$100000,0)+1,0),0,1000000,1),0))

=INDEX($A$1:$A$100000,MATCH("unique",OFFSET($A$1,IFERROR(MATCH(F1,$A$1:$A$100000,0)+1,0),0,1000000,1),0))

But the formula then stops working.

I worked on the assumption that it uses the *Door reference to search for the sections needed, so not sure what I'm not understanding.

[JohnTopley]

If you have replaced "door" in all case where it is not "ABC Door" then use the original formula.

What is different that it worked on the sample which several occurrences of [the "wrong"] "door" but extracted the correct information?

Post a file which has not returned the correct results.

[ChemistB]

I noticed when I was creating the formula that there were other instances with the word "door" in it. However, they all had additional text after "door" That's why I changed MATCH("*door*"..) to MATCH("*door",'...)

Do you now have instances where the text ends in door? Otherwise, it should still work. Did you accidently include an * after door in the formula?

[chrisbaz4444]

OK

The text "door" is all over the data - this is because it refers to different types of doors and how they are managed in the system.

JohnTopley: I believe this wasn't working because I don't understand the string correctly.

ChemistB: When I copy the formulas you kindly provided, F2 gives the correct output. F3 gives an empty cell, which is the same when copied down

Using the file you reposted, if I delete the information in column A1 and then paste the full data, the same happens - F2 is correct but the F3 formula is blank

This is also the same for G2 + G3

In terms of isolating the doors in question, the cell C9 is unique for each door. I'd like to find and replace this cell with a specific value on all instances (type: Door) replace this with a unique word eg. "umbrella" or something

Once done, the index? could be used to find all examples of that unique word and put + 3 cells above in F column, -30 Cells in G column, and -40 Cells in H column.

Could that work?

Apologies I haven't been able to correctly explain what I'm trying to do, and I can't upload the original file as it contains sensitive information otherwise it would be much easier!

[JohnTopley]

Without an exact copy of your file we cannot go further as we only have your last posted file which works.

You mention C9: the file we have has data in column A.

[chrisbaz4444]

All, thanks for your assistance on this. I was able to get the information required and have now completed the works.

Many Thanks again.

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.