finding a set of numbers and displaying results

[steveg666]

Help ! i have a spread sheet with 800 diferent delivery note numbers listed in a colum each delivery note number has a virious ammount of parts ordered on that number.( there may be up to 10 diffrent rows with the same delivery note number) can i use a form and vba to 1. input the delivery note number 2. search for that number 3. display the results found for all parts 4. print a report.

First time user and not to sure how to go about it any help would be thanked.

[etaf]

please see the attached

I have used an array formula - which means you need to use Control+shift+enter keys to get a {} brackets around the formula

the data is in column A and B

in Cell E2 you enter the delivery note number
and then in cells
E4 to F22 retuens the values for column A and B that match the delivery note number

the formula used in E4
=INDEX($A$2:$B$23, SMALL(IF($E$2=$A$2:$B$23, ROW($A$2:$B$23)-MIN(ROW($A$2:$B$23))+1, ""), ROW(A1)),COLUMN(A1))
and in F4
=INDEX($A$2:$B$23, SMALL(IF($E$2=$A$2:$B$23, ROW($A$2:$B$23)-MIN(ROW($A$2:$B$23))+1, ""), ROW(B1)),COLUMN(B1))
with {} around

see attached

IF that works OK - we can sort out the errors etc

[steveg666]

hello etaf

thank you for your help its great. would it be to much trouble to also add a part number and qty as well ( column b as part number and c for qty ordered). *the delivery note number is perfect and works fantastic just the job.* then just needs putting into a form so the user can type in a delivery note number and that info opens in a new sheet.

[etaf]

i have modified to show 4 columns
so you should see the changes needed

But no idea really about adding to forms - last time i played with forms was in office 97 i think

[steveg666]

hello etaf

You my friend are a superstar its helped me no end and i would just like to say a big thank you for your help.

[etaf]

your welcome