0

I have a list of equipment assets and attributes which is a raw data dump from a database. Among attributes like equipment status (like running, in standby etc.) or Oil level it also includes things like headers and place holders as separate record line. So that is what I am working with in terms of data.

What I would like to do with this data is populate another sheet with that information by selecting it based on a partial text line among other criteria. Here is a sample of what a cell I am checking on would contain.

"\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\BU-1002\P-1866A"

What I would be checking for would be "DE-OILING D" portion of that text line. This can include hundreds of records and by pulling the records with only that partial text I won't pull the other hundreds of records that I don't need.

Now I am hoping this is possible with a function. I know that I can count the number of these records that are in that data list with a COUNTSIF statement (This formula works for me "=COUNTIF('DBASE SHEET'!B:B,A4)"). So I know that it can be detected but for the life of me I can't figure out how to pull the other data fields by checking for the "De-Oiling D" text as the conditional argument and then reference the other data based on that criteria.

I have tried index(), indirect(), and vlookup() and it is either something completely different or maybe a combo of these functions. Either way I cannot seem to get the formula to work.

Now here is the other wrinkle. Once I have detected said record that I want to pull over to the new worksheet I want to be able to create a list of the new data but without a whole bunch of blank lines in it.

Record sample using | as cell separators:

|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING C\P-1370 | NULL            |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING C\P-1370 | RUNNING STATUS  |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING C\P-1370 | OIL LEVEL       |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING C\P-1370 | SUCTION PRESSURE|
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1470 | NULL            |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1470 | RUNNING STATUS  |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1470 | OIL LEVEL       |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1570 | NULL            |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1570 | RUNNING STATUS  |
|\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D\P-1570 | OIL LEVEL       |

What I would like to have as output is the following without having spaces or any of that stuff:

|DE-OILING C | P-1370 | RUNNING STATUS  |
|DE-OILING C | P-1370 | OIL LEVEL       |
|DE-OILING C | P-1370 | SUCTION PRESSURE|
|DE-OILING D | P-1470 | RUNNING STATUS  |
|DE-OILING D | P-1470 | OIL LEVEL       |
|DE-OILING D | P-1570 | RUNNING STATUS  |
|DE-OILING D | P-1570 | OIL LEVEL       |
7
  • Can you add a screenshot of the input data and screenshot of desired output? May 31, 2015 at 19:08
  • Sadly this was my first post and apparently cannot add pictures. :(
    – Grapeman
    Jun 1, 2015 at 15:09
  • This is a sample of the data in the cell I am checking on. A4 contains \Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D*
    – Grapeman
    Jun 1, 2015 at 15:49
  • Reworded for clarity hopefully.
    – Grapeman
    Jun 3, 2015 at 14:53
  • 1
    And what about the lines that contain "NULL" in the last column? You want to exclude them?
    – DavidPostill
    Jun 3, 2015 at 16:22

1 Answer 1

1

You're on the right track with considering the COUNTIFS() statement. Treat the text string like you would when comparing numbers, using > and < qualifiers to check whether each string begins with the section you're looking for.

=COUNTIFS('DBASE SHEET'!B:B,">"&A4,'DBASE SHEET'!B:B,"<"&A5)

where A4 contains your

"\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING D"

and A5 contains

"\Hierarchy\P2 PROCESS DE-OIL\DE-OIL\DE-OILING E"

If you need a dynamic formula to auto-populate A5, try

=LEFT(A4,LEN(A4)-1)&CHAR(CODE(RIGHT(A4,1))+1)

Not the answer you're looking for? Browse other questions tagged .