Copy part of data base

  • Thread starter Thread starter King George John
  • Start date Start date
K

King George John

I have on one sheet of my workbook a table of some 1000 spelling words.
The words are divided into arbitrary categories (tests, if you will).

I would like to extract a portion of those words into a table in another
sheet.

The "test #" will be a variable (duh!) and the number of entries have that
"test #" will differ for different "tests."

Thanks, in advance.
 
Maybe something along these lines ..

Assume the source list of words
is in Sheet1, col A, A1 down, e.g.:

text1
text2
text3
text4
text11
text22
text33
text44
etc

Click Insert > Name > Define
Put in "Names in workbook": Text
Put in "Refers to": =Sheet2!$A$1
Click OK

Put in B1: =IF(ISNUMBER(SEARCH(Text,A1)),ROW(),"")
Copy B1 down to B1000 to cover the list in col A

In Sheet2
------------
Cell A1 will be reserved for input of the variable, say: xt1

Put in A2:
=IF(ISERROR(SMALL(Sheet1!B:B,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!B:B,ROWS($A$1:A1)),Sheet1!B:B,0)))

Copy A2 down to A1001
(cover the same range size as in Sheet1)

For the sample data in Sheet1
and the variable input in Sheet2's A1, i.e. : xt1
you'll get:

text1
text11
(rest are "blank" rows)

Change the input in Sheet2's A1 to: xt2
and you'll get:

text2
text22
(rest are "blank" rows)
 
Back
Top