colating data from a table - for bespoke report

Hi

I have a simple 1000 row table and I am interested in only the data in the
columns shown below.

Proj Code Name

PC01 colin
PC01 fred jones


the project Code column data can change and a name is shown against ONLY if
associated with that proj code.

I am producing a report that has a lookup cell to select the name and then
automatically it will return all the Proj Codes that name is shown against.

I can get it to select the first match but not go down all the rows


Many thanks for any and all help with this..

Cheers

UKMAN

One way to set it up to deliver the required functionality ..
Your source data is assumed running in A2:B2 down (project codes - names)
Assume D2 is where you will input the name
In E2: =IF(D$2="","",IF(D$2=B2,ROW(),""))
In F2: =IF(ROWS($1:1)COUNT(E:E),"",INDEX(A:A,SMALL(E:E,R OWS($1:1))))
Copy E2:F2 down to cover the max expected extent of source data, eg down to
F100. Hide/minimize col E. Col F returns the desired results (ie the project
codes associated with the name input in D2), all neatly packed at the top.
Inspiring? hit the YES below
--
Max
Singapore
---
"UKMAN" wrote:
I have a simple 1000 row table and I am interested in only the data in the
columns shown below.

Proj Code Name

PC01 colin
PC01 fred jones


the project Code column data can change and a name is shown against ONLY if
associated with that proj code.

I am producing a report that has a lookup cell to select the name and then
automatically it will return all the Proj Codes that name is shown against.

I can get it to select the first match but not go down all the rows

Max,

many thanks and I got your verison to work tso to understand the formulas BUT

your "E" I changed to "=IF($DW$5="","",IF($DW$5=DO5,ROW(),""))"
Your "F" I changed to
"=IF(ROWS($1:1)COUNT($DV:$DV),"",INDEX($DN$5:$DN$ 1020,SMALL($DV:$DV,ROWS($1:1))))"

this is to reflect the layout I get a "10" in "DV10" and "#N/A" in "DW10" ??

what have I done wrong please????

UKMAN

"Max" wrote:

One way to set it up to deliver the required functionality ..
Your source data is assumed running in A2:B2 down (project codes - names)
Assume D2 is where you will input the name
In E2: =IF(D$2="","",IF(D$2=B2,ROW(),""))
In F2: =IF(ROWS($1:1)COUNT(E:E),"",INDEX(A:A,SMALL(E:E,R OWS($1:1))))
Copy E2:F2 down to cover the max expected extent of source data, eg down to
F100. Hide/minimize col E. Col F returns the desired results (ie the project
codes associated with the name input in D2), all neatly packed at the top.
Inspiring? hit the YES below
--
Max
Singapore
---
"UKMAN" wrote:
I have a simple 1000 row table and I am interested in only the data in the
columns shown below.

Proj Code Name

PC01 colin
PC01 fred jones


the project Code column data can change and a name is shown against ONLY if
associated with that proj code.

I am producing a report that has a lookup cell to select the name and then
automatically it will return all the Proj Codes that name is shown against.

I can get it to select the first match but not go down all the rows

The ranges used have to be the same size: DN$5:$DN$1020 vs $DV:$DV
And it's better to use ROWS($1:1) to replace the row sensitive ROW()

This set using (your) explicit ranges should work fine for you
Input for the name = DW5
Criteria
In DV5: =IF($DW$5="","",IF($DW$5=DO5,ROWS($1:1),""))

Extract & Float-up Results:
In say, DQ5:
=IF(ROWS($1:1)COUNT($DV$5:$DV$1020),"",INDEX($DN$ 5:$DN$1020,SMALL($DV$5:$DV$1020,ROWS($1:1))))
Copy DV5 and DQ5 down to row 1020. Joy? hit the YES below
--
Max
Singapore
---
"UKMAN" wrote:
Max,

many thanks and I got your verison to work tso to understand the formulas BUT

your "E" I changed to "=IF($DW$5="","",IF($DW$5=DO5,ROW(),""))"
Your "F" I changed to
"=IF(ROWS($1:1)COUNT($DV:$DV),"",INDEX($DN$5:$DN$ 1020,SMALL($DV:$DV,ROWS($1:1))))"

this is to reflect the layout I get a "10" in "DV10" and "#N/A" in "DW10" ??

what have I done wrong please????

Max,

sorry for slow reply but away yesterday

works a dream, I amednded the cell ranges etc.

You are a star

UKMAN

"Max" wrote:

The ranges used have to be the same size: DN$5:$DN$1020 vs $DV:$DV
And it's better to use ROWS($1:1) to replace the row sensitive ROW()

This set using (your) explicit ranges should work fine for you
Input for the name = DW5
Criteria
In DV5: =IF($DW$5="","",IF($DW$5=DO5,ROWS($1:1),""))

Extract & Float-up Results:
In say, DQ5:
=IF(ROWS($1:1)COUNT($DV$5:$DV$1020),"",INDEX($DN$ 5:$DN$1020,SMALL($DV$5:$DV$1020,ROWS($1:1))))
Copy DV5 and DQ5 down to row 1020. Joy? hit the YES below
--
Max
Singapore
---
"UKMAN" wrote:
Max,

many thanks and I got your verison to work tso to understand the formulas BUT

your "E" I changed to "=IF($DW$5="","",IF($DW$5=DO5,ROW(),""))"
Your "F" I changed to
"=IF(ROWS($1:1)COUNT($DV:$DV),"",INDEX($DN$5:$DN$ 1020,SMALL($DV:$DV,ROWS($1:1))))"

this is to reflect the layout I get a "10" in "DV10" and "#N/A" in "DW10" ??

what have I done wrong please????