I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1
or 2 keywords on them??

If I look at a typical row, are the keywords all in the same cell or does
each keyword have its own cell??
--
Gary''s Student - gsnu200812


"Chops" wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1
or 2 keywords on them??

If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way:

Autofilter the list, then in the column C autofilter dropdown,
choose "Show Blanks"

If the words are in separate cells, and the columns may be randomly
filled or blank, in E1 enter

=COUNTA(A1:D1)

and filter on column E <= 2

If the words are in one cell per row (e.g., in column A), separated by
commas, put this in B1:

=COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Filter column B on B <= 2


In article ,
Chops wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1
or 2 keywords on them??

The keywords are in the same cell, so if you look at the excel sheet it would
go something like this

xxxxx
xxxxx yyyyy
xxxx
xxxx yyyy wwww
yyyy

meaning some rows have one word some have 2 or 3 or even more. So basically
I have rows with single keywords and then some with long tailed keywords.
But I want to make a new sheet just showing the rows that have one or two
keywords in them.

I hope I am making sense

Thanx
Carolin

"Gary''s Student" wrote:

If I look at a typical row, are the keywords all in the same cell or does
each keyword have its own cell??
--
Gary''s Student - gsnu200812


"Chops" wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1
or 2 keywords on them??

they are in the sme row, but not seperated by commas there is just a space
between the words. Does the second formula still work.

Thanx
Carolin

"JE McGimpsey" wrote:

If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way:

Autofilter the list, then in the column C autofilter dropdown,
choose "Show Blanks"

If the words are in separate cells, and the columns may be randomly
filled or blank, in E1 enter

=COUNTA(A1:D1)

and filter on column E <= 2

If the words are in one cell per row (e.g., in column A), separated by
commas, put this in B1:

=COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Filter column B on B <= 2


In article ,
Chops wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up to
4 words per line. How do I sort it so I can only see the lines that have 1
or 2 keywords on them??

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

In article ,
Chops wrote:

they are in the sme row, but not seperated by commas there is just a space
between the words. Does the second formula still work.

Thanx
Carolin

"JE McGimpsey" wrote:

If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way:

Autofilter the list, then in the column C autofilter dropdown,
choose "Show Blanks"

If the words are in separate cells, and the columns may be randomly
filled or blank, in E1 enter

=COUNTA(A1:D1)

and filter on column E <= 2

If the words are in one cell per row (e.g., in column A), separated by
commas, put this in B1:

=COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Filter column B on B <= 2


In article ,
Chops wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up
to
4 words per line. How do I sort it so I can only see the lines that have
1
or 2 keywords on them??

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

In article ,
Chops wrote:

they are in the sme row, but not seperated by commas there is just a space
between the words. Does the second formula still work.

Thanx
Carolin

"JE McGimpsey" wrote:

If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way:

Autofilter the list, then in the column C autofilter dropdown,
choose "Show Blanks"

If the words are in separate cells, and the columns may be randomly
filled or blank, in E1 enter

=COUNTA(A1:D1)

and filter on column E <= 2

If the words are in one cell per row (e.g., in column A), separated by
commas, put this in B1:

=COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Filter column B on B <= 2


In article ,
Chops wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up
to
4 words per line. How do I sort it so I can only see the lines that have
1
or 2 keywords on them??

Hi,

in a dummy column enter this formula and then autofilter on the number 1.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
--
Thanks,
Shane Devenshire


"Chops" wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

In article ,
Chops wrote:

they are in the sme row, but not seperated by commas there is just a space
between the words. Does the second formula still work.

Thanx
Carolin

"JE McGimpsey" wrote:

If the words are in separate cells, (e.g., columns A:D), and A is filled
first, then B,C,D, then one way:

Autofilter the list, then in the column C autofilter dropdown,
choose "Show Blanks"

If the words are in separate cells, and the columns may be randomly
filled or blank, in E1 enter

=COUNTA(A1:D1)

and filter on column E <= 2

If the words are in one cell per row (e.g., in column A), separated by
commas, put this in B1:

=COUNTA(A1)+LEN(A1)-LEN(SUBSTITUTE(A1,",",""))

Filter column B on B <= 2


In article ,
Chops wrote:

I have an excel file with 18000 keywords in it. Some of the lines are up
to
4 words per line. How do I sort it so I can only see the lines that have
1
or 2 keywords on them??

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

Just a caution. This (a) assumes that there are no extraneous spaces
between, before, or after the words (which may well be a good
assumption), and (b) won't find the rows with 1 keyword, which was in
the original specification.


In article ,
ShaneDevenshire wrote:

in a dummy column enter this formula and then autofilter on the number 1.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over
18000 rows I am going thru.

Thanx
Carolin

"JE McGimpsey" wrote:

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

Say your formula is in B1, and you need to copy it down to B18000.
Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu
Edit--Fill--Down, and voila.
--
** John C **

"Chops" wrote:

This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over
18000 rows I am going thru.

Thanx
Carolin

"JE McGimpsey" wrote:

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

Put the formula in B1
Hit F5 (edit|goto)
and type:
B1:B18000
(change that 18000 to the real last row)

Now that range is selected.
Hit F2 (to edit the formula in B1)
but hit ctrl-enter to fill the selected range with the (adjusted for row)
formula.

Chops wrote:

This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over
18000 rows I am going thru.

Thanx
Carolin

"JE McGimpsey" wrote:

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))



--

Dave Peterson

Thanx

Like I sd I am new to this but I knew I could get the help. You all have
been great now I am off to try it

Again
Thanx
Carolin

"John C" wrote:

Say your formula is in B1, and you need to copy it down to B18000.
Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu
Edit--Fill--Down, and voila.
--
** John C **

"Chops" wrote:

This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over
18000 rows I am going thru.

Thanx
Carolin

"JE McGimpsey" wrote:

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))

Be sure to press the YES button below to indicate your question has been
answered (specifically on JE McGimpsey's post that solved most of your
question).
--
** John C **

"Chops" wrote:

Thanx

Like I sd I am new to this but I knew I could get the help. You all have
been great now I am off to try it

Again
Thanx
Carolin

"John C" wrote:

Say your formula is in B1, and you need to copy it down to B18000.
Press CTRL+G (GoTo), type in the box B1:B18000, then go to menu
Edit--Fill--Down, and voila.
--
** John C **

"Chops" wrote:

This is perfect I just have one other question when I am doing the dragging
is there an easier way then just dragging to the bottom because I have over
18000 rows I am going thru.

Thanx
Carolin

"JE McGimpsey" wrote:

Assuming the values are in column A.

1) Click on B1
2) Enter the formula below
3) Grab the fill-handle of B1 (lower right corner), and drag down as far
as required
4) Select columns A:B
5) Choose Data/Autofilter
6) In the B1 dropdown, choose Custom Filter
7) Using the dropdown and combobox, choose "is less than or equal to" 2
8) Click OK


In article ,
Chops wrote:

Ok I will try this, so do I click on column B and then what do I do. Sorry I
am so new to this.

Thanx
Carolin

"JE McGimpsey" wrote:

No, but a modification of the third one will:

=COUNTA(A1)+LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))