colating data from a table - for bespoke report

  • Thread starter Thread starter UKMAN
  • Start date Start date
U

UKMAN

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

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

sorry for slow reply but away yesterday

works a dream, I amednded the cell ranges etc. :):)

You are a star

UKMAN
 
Back
Top