vlookup - based on finding specific text within a cell

Hi!
How would I go about creating a vlookup formula for the following problem please?

In sheet1 column A, I have a long text log file dump (from a desk full of buttons) that needs deciphering - certain text strings which are repeated down through the rows mean a specific button was pushed. The English interpretation (the result I'm after of course) of these strings are on sheet2 column B, with the string that I need to search for within the log on sheet2 column A. (There is many more than these, but this is sufficient to start this task)

I somehow need a resulting column on sheet1 that will have the 'decoded' version wherever the strings are found within that row. Note that the number of digits on the date stamp changes; so this search string is not always the same number of digits from the left.

I hope this makes sense? Sorry my Excel skills are very limited, especially with correct syntax. Would somehow FIND be what I'm after?..

File attached.
Thanks, much appreciation in advance!

Paul
Chief button pusher.

Hi Paul,

The easiest thing to do is perform a "Text to Columns" function to separate out the data you need. The main value you want follows a "=" sign.
Select the whole column A and use the "Text to Columns" function under the "Data menu/ribbon.

In the first window select "Delimited". Hit Next. Under Delimiters select "Other:" and enter a "=" in the box next to it. Hit Finish.

This separates all your data into columns. The main value you want should be in Column C.

Do the same thing with your data in sheet 2

Put this Vlookup formula in column D and copy down.

Formula:

Please Login or Register  to view this content.

Hope that works for you.
See attached.

ARRAY formula in B1 of Sheet1, then drag down

Please Login or Register  to view this content.

Originally Posted by southward

Hi Paul,

The easiest thing to do is perform a "Text to Columns" function to separate out the data you need. The main value you want follows a "=" sign.
Select the whole column A and use the "Text to Columns" function under the "Data menu/ribbon.

In the first window select "Delimited". Hit Next. Under Delimiters select "Other:" and enter a "=" in the box next to it. Hit Finish.

This separates all your data into columns. The main value you want should be in Column C.

Do the same thing with your data in sheet 2

Put this Vlookup formula in column D and copy down.

Formula:

Please Login or Register  to view this content.

Hope that works for you.
See attached.

Thank you for your help.
You will notice though that the text along the lines of 'BUTTON.1.3.3' is not always these three digits; (or even in fact three digits), and yet retains the same last few digits - so unfortunately this doesn't quite work...

Originally Posted by kvsrinivasamurthy

ARRAY formula in B1 of Sheet1, then drag down

Please Login or Register  to view this content.

Wow! Thank you so much... Awesome! :D

I'm staring at it in wonderment... can you explain in laymans terms exactly how you are extracting the right elements of the text?? I can't even begin to decipher this!

You are Welcome

Formula works as follows
Mid function selects the string in A column (say A2) Sheet1 from "[".
If function gives the row numbers in Sheet2 which contain this string.
Small function selects the lowest row number.
Index gives the text in Sheet2 in that row of B column.

Originally Posted by kvsrinivasamurthy

Small function selects the lowest row number.

Hmmm.. I don't understand the use of finding the lowest row number?

Originally Posted by FlameRetired

Another way.

Formula:

Please Login or Register  to view this content.

Non-array entered.

This makes a lot of sense to my hurting brain - thanks!

By the way is there a way to automatically populate this formula and thus data down through a column - once say a much longer log was pasted into column A sheet 1? Without using shortcut keys, or the mouse on the bottom right of the selection box? I.e can a formula apply directly to a whole column - rather than a specific cell - if this makes sense? Automatically auto fill...

On another note; is it possible in this formula below to have a count of total number of filled rows on sheet 2 column A to look up to replace the figure 125 that occurs twice in this line? IE automatically keep the formula up to date based on how many rows I add in to decode into English? I am adding to this list daily...
Like use a COUNT sheet 2 column A or something similar rather than hard coded into he formula?

Please Login or Register  to view this content.

Thanks!

Originally Posted by FlameRetired

It depends.

Mostly it's not a good idea to reference whole columns / rows. It makes Excel work unnecessarily hard.

Is this what you meant?

Edit Perhaps you could use Dynamic Named Ranges (DNRs). They shrink and grow automatically.

Not to worry - ~12500 lines (rows) seems to be the longest log file I've seen (It's actually based on 1MB .txt)- so I've just dragged this formula in B down to 13000 rows, to allow for large pastes into column A.

Here's another method, non array entered

Sheet1, B1

=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$11,A1),Sheet2!$B$1:$B$11),"")

Note, there cannot be any blanks in Sheet2!$A$1:$A$11

Originally Posted by Jonmo1

Here's another method, non array entered

Sheet1, B1

=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$11,A1),Sheet2!$B$1:$B$11),"")

Note, there cannot be any blanks in Sheet2!$A$1:$A$11

Thanks!

I'm trying to avoid being specific about how many rows are in Sheet 2 in A or B. (They will always be the same quantity as each other as it's my gobbledy gook to english table) but because I'm adding to it all the time it would be nice to have some sort of automatic row count in the formula - rather than stating specifically to search $A$1:$A$11 etc... Possible? There will never be any blanks, except obviously after the last row.

Originally Posted by FlameRetired

Have you looked into Dynamic Named Ranges?

Yes - I find information that looks so promising, but my lack of knowledge about exactly how to apply it back into the original formula on sheet 1 lets me down.

Make the data on sheet2 an actual table.
Highlight the whole range on sheet 2
Click Insert - Table

Once that is done, specify the range in the formula
It will auto adjust as rows are added

Originally Posted by jonmo1

make the data on sheet2 an actual table.
Highlight the whole range on sheet 2
click insert - table

once that is done, specify the range in the formula
it will auto adjust as rows are added

omg!!!...

Please see my latest attachment for progress.
Every single button has now been mapped in my table (this in fact is recalled from the button name in the sheet MAP). The table is complete, at 720 items/rows.

My next challenge is to correct the translation to take into account the shifted buttons.
So - any button pressed after "MACRO 2ND SHIFT" should then be called from the corresponding "MACRO 2ND SHIFT" column on the sheet TABLE, rather than the default column B.

Then just the same technique for the 3rd shift, but for a fourth shift, it is the 2nd and 3rd shift in any order following on from each other that will then affect the next button pressed afterward.
(See the sheet "4th formula test" for a formula to describe this)

I hope this makes sense.
So - is it now possible to add into the formula on the sheet LOG to say that if the button returned in the cell above is say 2nd shift, to look up that appropriate column on the table, if it is 3rd then that one,
but if the two cells above together make a fourth shift as per my description, then return that column.

Am I best to somehow be using INDEX MATCH? I have a feeling this may be a useful tool here but unsure how to implement.

Thanks!!
Paul.

Originally Posted by FlameRetired

I'm confused. There are no values in that "MACRO 2ND SHIFT" column on the sheet TABLE corresponding to MACRO 2ND SHIFT or MACRO 3RD SHIFT.

The values that are in the columns MACRO 2ND SHIFT, MACRO 3RD SHIFT and MACRO 4th SHIFT are the result of the shift button (being pressed immediately prior).

There are only 2 shift buttons. If either of these are hit, the result of the next button pushed is changed to it's shifted function. No shift button = no shift (The first column in B). 2nd shift button pushed will give the item in the column MACRO 2ND SHIFT, 3rd = 3rd, both together (although logged sequentially in any order) = 4th shift function of the next button pushed, and this function is shown in the column MACRO 4TH SHIFT.

Is that any clearer? ha!

Originally Posted by FlameRetired

No it's not. As I still understand it those additional conditions will return empty strings from TABLE. Are those the returns you expect?

By the way you have numerous Conditional Formats set to whole columns. This is slowing your workbook down enormously. They need to be set to limited ranges.

Thanks, will use a range instead. Had no idea this would cause a large overhead! Tips appreciated.

Ha! Give me a minute and I'll try again.

Allow me to start from scratch.

The vision switcher desk in question logs all button pushes, down to the millisecond.
As the 'top' layer of buttons is not enough for some functions such as macros, a fairly standard style of shift is employed. There are other items that are shifted, but Macros are the only buttons of interest to me in this regard.
While some buttons are in fact held down, then while held another button pushed, such as using shifting on buttons, they are unable to be logged thus and in fact are logged as single instances independently and consecutively, with the shift operator (e.g. MACRO 2ND SHIFT) prior to the button that needed shifting.

This also means if two buttons are pressed simultaneously they are simply logged in the order that the desk was able to identify whichever might have been milliseconds after the other. This then becomes an issue with the other levels of shift. There are four levels in total - one unshifted and 3 shifted.
This is an unfortunate limitation I need to do my best to work with, and will lead to some errors where say shift might be pressed simply to see what is underneath (the buttons are contextually labeled with an LCD screen per button) without actually then pushing any significant button afterwards, as it was not held.

So on my table I have identified the 4 possible macro levels as this:

Button/MACRO NO SHIFT________________(No shift buttons are employed)
MACRO 2ND SHIFT_____________________(The button MACRO 2ND SHIFT is held down, before the button to shift)
MACRO 3RD SHIFT_____________________(The button MACRO 3RD SHIFT is held down, before the button to shift)
MACRO 4TH SHIFT_____________________(The 2 buttons MACRO 2ND SHIFT and MACRO 3RD SHIFT are held down before the button to shift)

For the 4th level - the two shift buttons may appear in the log list in either order, as clearly fingers aren't robotic, but if they are together, chances are it is 4th. (Although in fact you could look on the 2nd level, then the third level for something, yet then push the top level; but I can only work with what I've got).

So - while my conversion works great (Sheet LOG) for all top level functions, anywhere it logs a button after 2nd, 3rd, or 2nd and 3rd in any order, it should refer to a different column in the table.
I have now filled all of these columns out if they are not macro shifted buttons, so if one pushes 2nd for a look but then does't follow up with a macro button, but something unrelated, it will still at least show that button correctly.

See latest attachment. I hope that long winded mess doesn't leave you more confused.
Thanks!
Paul

Originally Posted by FlameRetired

Paul,

When you say "So - any button pressed after "MACRO 2ND SHIFT" should then be called from the corresponding "MACRO 2ND SHIFT" column on the sheet TABLE" do you mean immediately after (I only assume so) or ever-after that event?

Ahhh! I see how that is misleading! Apologies!

So it is only the immediate single subsequent button press. Just like you might imagine what it might look like when typing on your PC - The sentence "Dear Sir Bob" might be logged as:

Please Login or Register  to view this content.

And you would therefore perhaps want to translate in your log that the single instance after the shift was used would then interpret into the next listed item into a capital letter.
Even as exactly in my scenario; that in reality the shift is being held down.

So no - in answer to your question I believe - the shift is not a locked/'hold on' feature (like say Caps Lock is).

Am I any closer? Ha, your patience is much appreciated!

I guess if it were in spoken English it might read something like this:

If the cell directly above me is "MACRO 2ND SHIFT", then return from the macro 2nd shift column in the table. If it is "MACRO 3RD SHIFT"; from that column. If the two cells above me are "MACRO 2ND SHIFT" and "MACRO 3RD SHIFT" in any order, then return value from the 4th shift column. Otherwise, return value from the first column.

Originally Posted by FlameRetired

...The workbook is still very slow...I have never used LOOKUP with 720 "find_text" sources applied to 10,000 rows...In addition the Table Filter in 'TABLE' will now work much faster....

Wow! I thought my little workplace desktop was just getting old. This has made a vast difference!
Thanks, nice work!

New streamlined version attached.

Did you build those tables by hand!? If not, how did you post them? Its been a long time since hand coding HTML tags!

Might assist me in explaining things...

Originally Posted by FDibbins

wow what on earth IS that, I know it's not the Enterprise bridge?

20110812_3ME_Plan_Elevation_ON.1920x1080.VidRes.png

Ha! Live television production switcher.
https://www.grassvalley.com/products...tion_switchers

Originally Posted by FlameRetired

...Forum Tools Add-In (by Rory A.)

http://www.mrexcel.com/forum/about-b...tachments.html

Just paste into the ‘Reply’ window. When all that code posts it presents the ‘grids’.

I found a spelling mistake that caused an error for me - clearly this should read temp not tenp

Please Login or Register  to view this content.

Anyways... Thanks! I don't know if you want to let the writer know.

Edited Post #37 for clarity, and beautiful tables...

Originally Posted by FlameRetired

Can we have the option of restoring those headers back to MACRO 2ND SHIFT, MACRO 3RD SHIFT and MACRO 4TH SHIFT?

Sure can! Doesn't bother me what they are called. I was just trying to clear confusion with the same naming convention for two different concepts.

Originally Posted by FlameRetired

Do you have room for 'helper' column(s) in 'LOG'? The final formula is likely to be unwieldy and filled with repeating calculations if we don't.
It might also make the headers in 'TABLE' a non-issue.

Absolutely!
(I will probably go on at some point to ask/explore a macro to auto auto-fill all the formulas down though. And possibly even import the log data from an external file itself - but that's all for another day!)

In summary though - yes we can add as many columns as are necessary to simplify it. I understand you probably want to avoid insanely long complicated single cell formulas.

Hmm, no - as the two cells above both need to be checked; in this case as both of the physical buttons MACRO 2ND SHIFT & MACRO 3RD SHIFT were pushed (we will assume at the same time, but as mentioned due to the limitations in the logging they simply appear one after the other in any order) therefore this makes for a 4th shift, and the output should be [FREEZE] (4th) as you can see in the cell below.


Excel 2016 32 bit

	Log Entry Keyword	top level button	2nd shift	3rd shift	4th shift
36	[Info] On Trigger SID: id=PANEL.BUTTON.1.3.1.DOWN, value=701	[UNSQ]	[DEFAULT]	[BS UN SP]	[FREEZE] (4th)

Sheet: TABLE

Below are further generic examples that show which columns the cells in red would be calling their values from (spaced out with generic non shifted cells in orange).

Excel 2016 32 bit

PP AUTO

PP MIX

MACRO 2ND SHIFT

[This cell should recall from the 2nd shift column]

PP AUTO

PP MIX

MACRO 3RD SHIFT

[This cell should recall from the 3rd shift column]

PP AUTO

PP MIX

MACRO 2ND SHIFT

MACRO 3RD SHIFT

[This cell should recall from the 4th shift column]

PP AUTO

PP MIX

MACRO 3RD SHIFT

MACRO 2ND SHIFT

[This cell should recall from the 4th shift column]

PP AUTO

PP MIX

Sheet: LOG

Originally Posted by FlameRetired

My helpers manage to handle the combinations I was aware of (1, 2 and 4th columns). They choke on

MACRO 3RD SHIFT

[This cell should recall from the 3rd shift column]

Still working on this.

Weird - wouldn't it be written exactly like the conditions for the 2nd shift?
Thanks for your work on this!!

Wow! This is looking great! I mean it's pretty full on, that's for sure.
One bug however seems to be related to the following:

Excel 2016 32 bit

MACRO 2ND SHIFT	=	still only
MACRO 2ND SHIFT	=	2nd shift
MACRO 3RD SHIFT	=	still only
MACRO 3RD SHIFT	=	3rd shift

Sheet: LOG

This is probably my fault for not explaining this well enough. I can only assume that someone might have pushed it twice with the first time being a visual look at the shift contextual labels - but two presses of 2nd or 3rd shift don't make a fourth shift.

I've modified the file with some simple test data to avoid scrolling down too far, and a repeated button push that is simply:
Excel 2016 32 bit

	A	B	C	D	E
2	[Info] On Trigger SID: id=PANEL.BUTTON.1.3.2.DOWN, value=701	[Im a top level button]	[Im a 2nd shift push]	[Im a 3rd shift push]	[Im a 4th shift push]

Sheet: TABLE

It contains a lot less log!

File attached.

Originally Posted by FlameRetired

gromitnz,

What row(s) are these on and what row are the relevant (cell below) data on? Are those shift cells (white background) the ones you are talking about?

Edit Nevermind. Found it. Thank you for the inset with the combinations drawn out.

Row\Col	G	H	I
1	Excel 2016 32 bit
2	MACRO 2ND SHIFT	=	still only
3	MACRO 2ND SHIFT	=	2nd shift
4
5
6	MACRO 3RD SHIFT	=	still only
7	MACRO 3RD SHIFT	=	3rd shift

Sorry that was me trying to diagram the principle that two adjacent 2nds or 3rds don't make for a 4th shift. Ignore exact cell positions. In fact maybe just ignore it full stop...

Here is an actual example from that last attachment:

Excel 2016 32 bit

	A	B	C
22	2016-4-23 19:27:29.172 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.3.DOWN, value=714¶	2016-4-23 19:27:29	MACRO 2ND SHIFT
23	2016-4-23 19:27:34.773 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.3.DOWN, value=714¶	2016-4-23 19:27:34	MACRO 2ND SHIFT
24	2016-4-17 12:52:54.195 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.2.DOWN, value=701¶	2016-4-17 12:52:54	THIS SHOULD BE 2ND SHIFT
25	2016-4-17 12:54:51.226 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.11.DOWN, value=20202¶	2016-4-17 12:54:51	PP MIX
26	2016-4-17 12:54:51.710 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.3.DOWN, value=109¶	2016-4-17 12:54:51	PPB B
27	2016-4-17 12:52:11.560 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.3.DOWN, value=713¶	2016-4-17 12:52:11	MACRO 3RD SHIFT
28	2016-4-17 12:52:11.560 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.3.DOWN, value=713¶	2016-4-17 12:52:11	MACRO 3RD SHIFT
29	2016-4-17 12:52:54.195 [Info] On Trigger SID: id=PANEL.BUTTON.1.3.2.DOWN, value=701¶	2016-4-17 12:52:54	THIS SHOULD BE 3RD SHIFT

Sheet: LOG

Originally Posted by FlameRetired

I have a clearer picture. Here is the helper column formula. You only need one. 

Formula:

Please Login or Register  to view this content.

Here is the redone main formula.

Formula:

Please Login or Register  to view this content.

and it is back to running faster and smoother.

Thank you so much!! You've done it!!
AMAZING. I'm very appreciate, kind stranger!

I want to develop this a little more now in other areas - will use a fresh topic for that as it relates to other questions.

Really stoked buddy. Thanks for your hard work.

Out of curiosity - now I'm going back over what you've done trying to dissect it: What is 25^25 doing?

Originally Posted by FlameRetired

gromitnz,

An afterthought. This is faster still.

It has two more helper columns. The one in column D is the same as before. In column E

Formula:

Please Login or Register  to view this content.

in column F

Formula:

Please Login or Register  to view this content.

Then the main formula in column C

Formula:

Please Login or Register  to view this content.

The file is attached.

That is very fast! But... what's it all doing!? Haha!
I have indeed adopted it.

Try to combine INDEX() and MATCH() in replace of VLookUp() to get accurate search!

Please Login or Register  to view this content.

Source.JPG
IndexMid.JPG
Hope that will help!