Data Extraction

[greeng66]

Hi,

Hope you can help.

I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:


Col. A Col. B Col. C
Smith 10 1
Jones 5 12
Green 6 5
Black 12 14
Smith 36 12
Jones 15 12
Dual 2 5
Green 12 25

I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.

Or suppose even batter would be a sysnopsis of all this information in a reportable fashion.

Any help much appreciated.

[NBVC]

Are you trying to sum the numbers or just pull the multiple numbers?

[greeng66]

Are you trying to sum the numbers or just pull the multiple numbers?

Pull the line of data for each one. Ideaaly I'd selct Green in a validation list on Row A or B,
and it would pull through all the Green data rows from the other sheet.

[NBVC]

Assuming your date is in Sheet1 starting at A2...

Then in D2 enter: =COUNTIF(A$2:A2,Sheet2!$A$1) and copy it down

In E2 enter: =Max(D:D)

you can hide these column if desired....

Then in Sheet2, assuming the selection of "Green" is made in A1,

Then in A2 enter:

=IF(ROWS($A$2:$A2)>Sheet1!$E$2,"",INDEX(Sheet1!B:B,MATCH(ROWS($A$2:$A2),Sheet1!$D:$D,0)))


and copy it across the number of columns needed to get all relevant data and then down as far as necessary to ensure all possible matches are gotten.

[greeng66]

Top man, it works, hopefully that should appease the powers that be.