Extracting data from multiple rows

T

thermometer

I have a list of 40 records in one column, each record about 20 rows
long, with
different data, but with the same field headings (100, 245, 260, etc.)
that looks something like this:
100 Joe Smith
245 Book of home repair
260 Simon and Shuster
440 Language tape
500 Audio File
655 Audiobook
856 Bibliographic record URL
994 Location designation

100 Mary Jones
245 Cooking light
260 Random House
etc.


If I only want to view certain fields, like 100 and 245, how can I
extract that data from all the records and create a new table with that
information?
Like this:
100 Joe Smith
245 Book of home repair
500 Audio File

100 Mary Jones
245 Cooking light
500 Large print

Thanks for any assistance.
 
T

thermometer

Hello. thaks for the assistance. I did respond previously. Here's wht
I wrote:

Maybe I wasn't completely clear. What I need as results should be each

record with the specific fields such as:
100 Joe Smith
245 Book of home repair
500 Audio File

100 Mary Jones
245 Cooking light
500 Large Print

100 Mary Carson
245 Math made easy
500 Paperback

etc.
 
M

Max

Here's one play using non-array formulas ..

Assume the source data is in col A

Assume the desired view fields, eg: 100, 245
will be listed within C1:C10

Put in D1:
=IF(ISERROR(SMALL(E:E,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0­)))


Put in E1:
=IF(A1="","",IF(SUMPRODUCT(ISNUMBER(SEARCH($C$1:$C$10,A1))*($C$1:$C$10<>"")­)>0,ROW(),""))


Select D1:E1, fill down to the last row of source data in col A

Col D will auto-extract the required lines from col A,
all neatly bunched at the top

For the sample data in col A, we'd get in col D:

100 Joe Smith
245 Book of home repair
100 Mary Jones
245 Cooking light


---
thermometer said:
Tough problem. I have a list of 40 records in one column, each with
different data, but with the same field headings (100, 245, 260, etc.)
that looks something like this:
100 Joe Smith
245 Book of home repair
260 Simon and Shuster
440 Language tape
500 Audio File
655 Audiobook
856 Bibliographic record URL
994 Location designation
 
T

thermometer

Interesting approach Max. How does the program know to only choose the
100 and 245 rows?
 
M

Max

thermometer said:
Interesting approach Max. How does the program know to only choose the
100 and 245 rows?

Via the formulae in the criteria col E, viz in E1 copied down:
=IF(A1="","",IF(SUMPRODUCT(ISNUMBER(SEARCH($C$1:$C$10,A1))*($C$1:$C$10<>""))>0,ROW(),""))

The part: ISNUMBER(SEARCH($C$1:$C$10,A1))*($C$1:$C$10<>"")
searches col A's text with inputs made within C1:C10, excluding empty
cells within C1:C10. Because C1:C10 may not be completely filled with
inputs (eg: we enter only 2 inputs: 100 and 245, so there'll be 8 other
empty cells within C1:C10), we need to exclude these empty cells from
the search.

The exclusion is achieved via multiplying the ISNUMBER(SEARCH(...))
with the condition that: ($C$1:$C$10<>""). This results in an array of
0's / 1's, with 1's indicating satisfaction of criteria. SUMPRODUCT is
then used to SUM the array and if the result is > zero (that means
criteria is satisfied), an arb row number will be assigned to that
line, via ROW().

The formulas in col D, D1 copied down:
=IF(ISERROR(SMALL(E:E,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(E:E,ROW(A1)),E:E,0)))

then reads the arb row numbers assigned in col E, and pulls up the
corresponding items in col A. Items in col A will be extracted neatly
in ascending order by the arb row numbers in col E.

---
 
T

thermometer

Max - don't worry about it. I figured out the Advanced Filter method
and it works fine. Thanks for all your assistance.
 
A

aaron.kempf

you don't EXTRACT this what you do is this.

a) keep your DATA in a DATABASE
b) uninstall excel and spit on anyone that tells you otherwise
c) write a query-- instead of copying data around-- render the results
that you want.
 
M

Max

thermometer said:
Max - don't worry about it. I figured out the Advanced Filter method
and it works fine. Thanks for all your assistance.

You're welcome !
Glad to hear you found a resolution.

---
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top