|
|
 |
|
Joined: 2/14/2012
Posts: 56
|
|
|
Can anyone please filter me the following pattern from the attached file here
1. V+P
2. V+PRON+P
3. V+PN+P
Look at these manually filtered patterns
1. V+P
|
LOCATION
|
FORM
|
TAG
|
FEATURES
|
|
(2:13:2:1)
|
qiyla
|
V
|
STEM|POS:V|PERF|PASS|LEM:qaAla|ROOT:qwl|3MS
|
|
(2:13:3:1)
|
la
|
P
|
PREFIX|l:P+
|
2. V+PRON+P
|
LOCATION
|
FORM
|
TAG
|
FEATURES
|
|
(2:14:8:1)
|
xala
|
V
|
STEM|POS:V|PERF|LEM:xalaA|ROOT:xlw|3MP
|
|
(2:14:8:2)
|
woA@
|
PRON
|
SUFFIX|PRON:3MP
|
|
(2:14:9:1)
|
<ilaY`
|
P
|
STEM|POS:P|LEM:<ilaY`
|
3. V+PN+P
|
LOCATION
|
FORM
|
TAG
|
FEATURES
|
|
(2:17:10:1)
|
*ahaba
|
V
|
STEM|POS:V|PERF|LEM:*ahaba|ROOT:*hb|3MS
|
|
(2:17:11:1)
|
{ll~ahu
|
PN
|
STEM|POS:PN|LEM:{ll~ah|ROOT:Alh|NOM
|
|
(2:17:12:1)
|
bi
|
P
|
PREFIX|bi+
|
1. Can anyone help me do this job shown above?
|
|
|
|
|
 | |
|
|
|
Hi,
Your question is not at all clear. What are you trying to do? What is the criteria for filtering?
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
I want to filter the rows based on Column C "TAG".
Firstly, in a separate sheet, I Want Excel filter V first, then P. In other words, in our filtered sheet, in the first row , we will see V, then in the second P. So in the C column, we will see a V,P; V,P ...pattern. I have shown this in the first example:
Pattern 2 and 3 will also be filtered accordingly in separate sheets.
I hope you got my point.
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
For pattern 1, the filtered sheet looks like this
| LOCATION |
FORM |
TAG |
FEATURES |
| (2:4:4:1) |
>unzila |
V |
STEM|POS:V|PERF|PASS|(IV)|LEM:>anzala|ROOT:nzl|3MS |
| (2:4:5:1) |
<ilayo |
P |
STEM|POS:P|LEM:<ilaY` |
| (2:4:7:1) |
>unzila |
V |
STEM|POS:V|PERF|PASS|(IV)|LEM:>anzala|ROOT:nzl|3MS |
| (2:4:8:1) |
min |
P |
STEM|POS:P|LEM:min |
| (2:11:2:1) |
qiyla |
V |
STEM|POS:V|PERF|PASS|LEM:qaAla|ROOT:qwl|3MS |
| (2:11:3:1) |
la |
P |
PREFIX|l:P+ |
For pattern 2 we will see after filter
| LOCATION |
FORM |
TAG |
FEATURES |
| (1:7:3:1) |
>anoEamo |
V |
STEM|POS:V|PERF|(IV)|LEM:>anoEama|ROOT:nEm|2MS |
| (1:7:3:2) |
ta |
PRON |
SUFFIX|PRON:2MS |
| (1:7:4:1) |
Ealayo |
P |
STEM|POS:P|LEM:EalaY` |
| (2:3:2:1) |
yu&ominu |
V |
STEM|POS:V|IMPF|(IV)|LEM:'aAmana|ROOT:Amn|3MP |
| (2:3:2:2) |
wna |
PRON |
SUFFIX|PRON:3MP |
| (2:3:3:1) |
bi |
P |
PREFIX|bi+ |
| (2:8:5:1) |
'aAma |
V |
STEM|POS:V|PERF|(IV)|LEM:'aAmana|ROOT:Amn|1P |
| (2:8:5:2) |
n~aA |
PRON |
SUFFIX|PRON:1P |
| (2:8:6:1) |
bi |
P |
PREFIX|bi+ |
.....and so on
I hope it is clear now
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
It is so difficult a problem that none can solve it.
|
|
|
|
| |
|
|
|
Hi,
You may refer to my solution in the attached file.
Instructions are very clearly mentioned in sheet 2, 3 and 4.
Do let me know how this works.
|
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
Fantastic Solution................... well done
Thank you very much.
I am working in a project for analyzing corpus data, and I am badly in need of your cooperation.
I will surely mention your name in the acknowledgement section.
Would you please tell me how it was made possible (explanation of the logic).
With Regards
FH
|
|
|
|
| |
|
|
|
Hi,
You are most welcome. I will explain the logic to you in some time. Please send me a test mail on ask(at)ashishmathur(dot)com
|
|
|
|
| |
|
|
|
Hi,
The logic is as follows:
On the V+P sheets, there are two conditions for extracting data:
a. Current cell is "V" and next cell is "P"; OR
b. Previous cell is "V" and current cell is "P"
Similar logic has been used for other two sheets as well.
Hope this clarifies.
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
Thank you very much.
Now I can extract other patterns like V+N using your formula.
But if I want to add more items like V+PRON+DET+N, how this will affect the codes for the criteria (Pls write the full codes for the 4 cells).
Waiting for reply
|
|
|
|
| |
|
|
|
Hi,
You may refer to my solution in the attached file. Please note that headings are now starting from row 3 of the Data sheet.
Also, because of inserting one more row in the Data sheet, i have had to change the criteria cell reference for the previous three sheet as well.
Hope this helps.
|
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
There is only one combination left, that is
V+PRON+P+DET+N
This is very important, because this is the highest possible pattern consisting of 5 items.
How does the addition of more items affect the formula?
Thanks in advance
|
|
|
|
| |
|
|
|
Hi,
You may review my solution in the attached file. Let me know how this works.
|
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
This means that if I want to add one more item, I have to add a blank row at the beginning of "Data" sheet.
Thanks again
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
This means that if I want to add one more item, I have to add a blank row at the beginning of "Data" sheet.
Thanks again
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
This means that if I want to add one more item, I have to add a blank row at the beginning of "Data" sheet.
Thanks again
|
|
|
|
| |
|
|
|
Hi,
yes, add one more blank row (at the top) in the Data sheet and you will also have to change the formulas for criteria in the individual sheet.
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
|
|
Hi,
One thing I noticed that you make things more simple than others.
When I was analyzing the verb pattern with 5 elements, my eyes got puzzled; because there was no border around.
Can we filter the pattern with a format ie set a border after every 5 rows with each 5 an alternative color scheme.
Examples are shown in the file attached.
|
|
|
|
|
| |
|
|
|
Hi,
Press Alt+F11 > Insert > Module and paste the following code in the right hand side window
Sub colour_pattern()
Dim criteria_rows As Integer
Dim data_rows As Integer
For Z = 2 To ActiveWorkbook.Worksheets.Count
r = 9
criteria_rows = Worksheets(Z).Range("C1").CurrentRegion.Rows.Count - 1
data_rows = Worksheets(Z).Range("A8").CurrentRegion.Rows.Count - 1
For n = 1 To data_rows / criteria_rows
If Application.WorksheetFunction.IsEven(n) = False Then
Worksheets(Z).Range("A" & r).Resize(criteria_rows, 4).Interior.Color = RGB(197, 217, 241)
Else
Worksheets(Z).Range("A" & r).Resize(criteria_rows, 4).Interior.Color = RGB(221, 217, 196)
End If
Worksheets(Z).Range("A" & r).Resize(criteria_rows, 4).BorderAround Weight:=xlThin
r = r + criteria_rows
Next n
Next Z
End Sub
Click on File > Close and Return to MS Excel
Now press Alt+F8, select colour_pattern and click on Run
Hope this helps.
|
|
|
|
| |
|
|
|
|
Joined: 2/14/2012
Posts: 56
|
| |