Excel, eliminating sequence of data and finding first and last one [closed]
up vote
-1
down vote
favorite
I have a data sequence like;
XXX-AA-01
XXX-BB-01
XXX-CC-01
YYY-AA-01
YYY-BB-01
...
I want to find the rows of first and last cell of XXX sequence (or eliminate in between ones) and for YYY sequence and so on.
Can you help me find my path?
excel vba excel-vba
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
closed as too broad by Ron Rosenfeld, Owen Pauling, greg-449, Umair, GhostCat Nov 19 at 13:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
up vote
-1
down vote
favorite
I have a data sequence like;
XXX-AA-01
XXX-BB-01
XXX-CC-01
YYY-AA-01
YYY-BB-01
...
I want to find the rows of first and last cell of XXX sequence (or eliminate in between ones) and for YYY sequence and so on.
Can you help me find my path?
excel vba excel-vba
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
closed as too broad by Ron Rosenfeld, Owen Pauling, greg-449, Umair, GhostCat Nov 19 at 13:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I have a data sequence like;
XXX-AA-01
XXX-BB-01
XXX-CC-01
YYY-AA-01
YYY-BB-01
...
I want to find the rows of first and last cell of XXX sequence (or eliminate in between ones) and for YYY sequence and so on.
Can you help me find my path?
excel vba excel-vba
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
I have a data sequence like;
XXX-AA-01
XXX-BB-01
XXX-CC-01
YYY-AA-01
YYY-BB-01
...
I want to find the rows of first and last cell of XXX sequence (or eliminate in between ones) and for YYY sequence and so on.
Can you help me find my path?
excel vba excel-vba
excel vba excel-vba
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
edited Nov 19 at 7:55
Pᴇʜ
19.4k42650
19.4k42650
asked Nov 18 at 16:24
maddoxbey
1
asked Nov 18 at 16:24
maddoxbey
1
asked Nov 18 at 16:24
maddoxbey
1
1
closed as too broad by Ron Rosenfeld, Owen Pauling, greg-449, Umair, GhostCat Nov 19 at 13:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by Ron Rosenfeld, Owen Pauling, greg-449, Umair, GhostCat Nov 19 at 13:06
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18
add a comment |
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18
add a comment |
2 Answers
2
active
oldest
votes
up vote
0
down vote
With data in column A, in B2 enter:
=IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"X","")
and copy downward:
Then filter on column B and remove all the "X" rows.
answered Nov 18 at 17:24
Gary's Student
71.5k93460
add a comment |
up vote
0
down vote
Assuming you want to reference a set of keywords (XXX, YYY, QPZ), you can adjust the formula to look at an array of the keywords (i.e. Column "Keywords" in the image/example) and provide the indicator for the first and last observations.
Formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"","X"),"")
answered Nov 18 at 19:19
Dale Kube
415313
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
With data in column A, in B2 enter:
=IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"X","")
and copy downward:
Then filter on column B and remove all the "X" rows.
answered Nov 18 at 17:24
Gary's Student
71.5k93460
add a comment |
up vote
0
down vote
With data in column A, in B2 enter:
=IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"X","")
and copy downward:
Then filter on column B and remove all the "X" rows.
answered Nov 18 at 17:24
Gary's Student
71.5k93460
add a comment |
up vote
0
down vote
up vote
0
down vote
With data in column A, in B2 enter:
=IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"X","")
and copy downward:
Then filter on column B and remove all the "X" rows.
answered Nov 18 at 17:24
Gary's Student
71.5k93460
With data in column A, in B2 enter:
=IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"X","")
and copy downward:
Then filter on column B and remove all the "X" rows.
answered Nov 18 at 17:24
Gary's Student
71.5k93460
answered Nov 18 at 17:24
Gary's Student
71.5k93460
answered Nov 18 at 17:24
Gary's Student
71.5k93460
answered Nov 18 at 17:24
Gary's Student
71.5k93460
71.5k93460
add a comment |
add a comment |
up vote
0
down vote
Assuming you want to reference a set of keywords (XXX, YYY, QPZ), you can adjust the formula to look at an array of the keywords (i.e. Column "Keywords" in the image/example) and provide the indicator for the first and last observations.
Formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"","X"),"")
answered Nov 18 at 19:19
Dale Kube
415313
add a comment |
up vote
0
down vote
Assuming you want to reference a set of keywords (XXX, YYY, QPZ), you can adjust the formula to look at an array of the keywords (i.e. Column "Keywords" in the image/example) and provide the indicator for the first and last observations.
Formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"","X"),"")
answered Nov 18 at 19:19
Dale Kube
415313
add a comment |
up vote
0
down vote
up vote
0
down vote
Assuming you want to reference a set of keywords (XXX, YYY, QPZ), you can adjust the formula to look at an array of the keywords (i.e. Column "Keywords" in the image/example) and provide the indicator for the first and last observations.
Formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"","X"),"")
answered Nov 18 at 19:19
Dale Kube
415313
Assuming you want to reference a set of keywords (XXX, YYY, QPZ), you can adjust the formula to look at an array of the keywords (i.e. Column "Keywords" in the image/example) and provide the indicator for the first and last observations.
Formula: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,IF(AND(LEFT(A2,3)=LEFT(A1,3),LEFT(A2,3)=LEFT(A3,3)),"","X"),"")
answered Nov 18 at 19:19
Dale Kube
415313
answered Nov 18 at 19:19
Dale Kube
415313
answered Nov 18 at 19:19
Dale Kube
415313
answered Nov 18 at 19:19
Dale Kube
415313
415313
add a comment |
add a comment |
Is the data in a single column?
– Dale Kube
Nov 18 at 16:27
yes it's in a one single column.
– maddoxbey
Nov 18 at 16:59
Are there any ordering rules that need to be considered? For example, should the solution pick out the first record with XXX which is first alphabetically based on the second component of each value ("-AA-" vs. "-BB-")?
– Dale Kube
Nov 18 at 17:01
the first section XXX or YYY could be change. there are no rules or order in first section but the second section is in alphabetical order. i want the find every sequences' fisrt and last row whether it's XXX, YYY or QPZ which they could be anything.
– maddoxbey
Nov 18 at 17:18