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 |