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
0
Utf
4/26/2010 3:37:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
594 Views

Similar Articles

[PageSpeed] 38

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,ROWS($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 :)
0
Utf
4/26/2010 11:32:01 PM
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,ROWS($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 :)
0
Utf
4/27/2010 5:20:01 PM
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????

0
Utf
4/27/2010 11:16:01 PM
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????
> 
0
Utf
4/29/2010 3:25:01 PM
Reply:

Similar Artilces: