Help, too difficult for me.

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

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 are:

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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!

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>