Hello,

I have managed to get data out of an Oracle database into my excel
spreadsheet and I now need to analyze a string for variable names and
display their values only.

For each unique script name in column A (I've oncluded two in my
example) I have multiple rows of text in column B. For each unique
script name I want to search corresponding text fields in column B for
the strings v_event_name2, v_remote_host and v_ftac and display their
values, which are on the same line between the single quotes
('<value'), in a different columns. Preferably all in one row behind
the unique script name under column headers that have the name of the
variables.

I have attached my example spreadsheet to this message. I hope this
makes sense. Your help will be greatly appreciated. Regards,

Menno Hubert.
The Netherlands


+-------------------------------------------------------------------+
|Filename: exceltip_Menno.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4230 |
+-------------------------------------------------------------------+

--
Menno
------------------------------------------------------------------------
Menno's Profile: http://www.excelforum.com/member.php...o&userid=27869
View this thread: http://www.excelforum.com/showthread...hreadid=502067

Hi!

Tell me if I'm headed in the right direction:

v_event_name2 varchar2(30) := 'EVT_SAP_NL_5500_SD_DOWNL_WEEK';
v_remote_host varchar2(255) := 'zoms403a'; -- change here if required
v_ftac varchar2(255) := 'INDDOWNL'; -- change here if required
and event_name = v_event_name2;

The first 3 lines above contain the substrings you are interested in. The
values you want extracted for those 3 lines a

EVT_SAP_NL_5500_SD_DOWNL_WEEK
zoms403a
INDDOWNL

Is that correct?

The 4th line contains one of the substrings but doesn't contain a "value".
It appears that the "value" to be extracted is preceded by an equal sign
(=).

If this is correct I can get those values extracted but it'll be up to you
to place them where you want them!

Based on your sample file:

Enter this formula in C3 and copy down:

=IF(AND(ISNUMBER(FIND("=",B3)),OR(ISNUMBER(SEARCH( {"v_event_name2","v_remote_host","v_ftac"},B3)))), MID(B3,FIND("'",B3)+1,FIND("'",B3,FIND("'",B3)+1)-FIND("'",B3)-1),"")

Biff

"Menno" wrote in
message ...

Hello,

I have managed to get data out of an Oracle database into my excel
spreadsheet and I now need to analyze a string for variable names and
display their values only.

For each unique script name in column A (I've oncluded two in my
example) I have multiple rows of text in column B. For each unique
script name I want to search corresponding text fields in column B for
the strings v_event_name2, v_remote_host and v_ftac and display their
values, which are on the same line between the single quotes
('<value'), in a different columns. Preferably all in one row behind
the unique script name under column headers that have the name of the
variables.

I have attached my example spreadsheet to this message. I hope this
makes sense. Your help will be greatly appreciated. Regards,

Menno Hubert.
The Netherlands


+-------------------------------------------------------------------+
|Filename: exceltip_Menno.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4230 |
+-------------------------------------------------------------------+

--
Menno
------------------------------------------------------------------------
Menno's Profile:
http://www.excelforum.com/member.php...o&userid=27869
View this thread: http://www.excelforum.com/showthread...hreadid=502067

Biff,

You are corretc in all your assumptions and it works nicely with the
formula. I still want to try to place the data as follows:
<SCRIPT_NAME1<VALUE1<VALUE2<VALUE3
<SCRIPT_NAME2<VALUE1<VALUE2<VALUE3

Script name and extracted values in one row in separate columns for
each script name found.

Do you if this can be done? Regardless of the answer, thanks a lot for
your help!

Menno.


--
Menno
------------------------------------------------------------------------
Menno's Profile: http://www.excelforum.com/member.php...o&userid=27869
View this thread: http://www.excelforum.com/showthread...hreadid=502067

Hi!

Need to make a slight tweak of the extraction formula. I discovered an
instance of a "false positive" that resulted in an error of #VALUE!.

Change this portion:

FIND("=",B3)

To:

FIND(":=",B3)

I still want to try to place the data as follows:
<SCRIPT_NAME1<VALUE1<VALUE2<VALUE3
<SCRIPT_NAME2<VALUE1<VALUE2<VALUE3

You'd have to enter the unique script names:

D3 = SAP_NL_5500_SD_DOWNL_WK_NCOPY
D4 = NL_5700_SD_DOWNL_WK_N_NCOPY

Enter this formula as an array using the key combo of CTRL,SHIFT,ENTER in
cell E3:

=IF(COLUMNS($A:A)<=SUMPRODUCT(--($A$3:$A$198=$D3),--($C$3:$C$198<"")),INDEX($C$3:$C$198,SMALL(IF(($A$ 3:$A$198=$D3)*($C$3:$C$198<""),ROW(C$3:C$198)-ROW(C$3)+1),COLUMNS($A:A))),"")

Copy across until you get a return of blank then down.

Based on your sample file which is fairly small these formulas will work
just fine. However, if your "real" application has 1000's of rows of data
these formulas may take a "few seconds" to calculate.

Here's your file with this implemented:

http://s64.yousendit.com/d.aspx?id=2...W0Y00O2EZ2CZN4

Biff

"Menno" wrote in
message ...

Biff,

You are corretc in all your assumptions and it works nicely with the
formula. I still want to try to place the data as follows:
<SCRIPT_NAME1<VALUE1<VALUE2<VALUE3
<SCRIPT_NAME2<VALUE1<VALUE2<VALUE3

Script name and extracted values in one row in separate columns for
each script name found.

Do you if this can be done? Regardless of the answer, thanks a lot for
your help!

Menno.


--
Menno
------------------------------------------------------------------------
Menno's Profile:
http://www.excelforum.com/member.php...o&userid=27869
View this thread: http://www.excelforum.com/showthread...hreadid=502067

Biff,

Excellent stuff. Thank you very much.

Menno.


--
Menno
------------------------------------------------------------------------
Menno's Profile: http://www.excelforum.com/member.php...o&userid=27869
View this thread: http://www.excelforum.com/showthread...hreadid=502067