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

colating data from a table - for bespoke report
 

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 :)

colating data from a table - for bespoke report
 

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 :)

colating data from a table - for bespoke report
 

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????

colating data from a table - for bespoke report
 

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????