need help on this..

[vinu]

I have 2 sheets. In one sheet I have a series of Invoice No. in A column and company name in B column. Comp names repeated many times.

In the 2nd sheet I want a formula something like this.

In one cell I will mention company name and in other cell mention the any no*

I need to pick up the random invoices from the 1st sheet based on company and *no.


For eg: If I have 30 invoice for A company in sheet 1. In sheet 2 will mention A and number is 5. So I need to get A companies any 5 random invoice no. from that list.


*no. = it may varry every day.


Is there any formula or any way to do this.


Regards,

vinu

 

[Hui]

Vinu


On the first sheet I would add a helper column which will contain a formula =if(Company_on_This_Row = CompanyPg2, rand(),"")


Then I would have a rank Column which will Rank the Helper Column


Back to page 2, add a column with Numbers 1,2,3...X

and then use an Offset(Match()) to retrieve the x Inv. values from the first page using the Rank to match the numbers on Page 2

Example here: http://rapidshare.com/files/399299952/Vinu_100615.xls.html

 

[vinu]

Thanks a lot Hui. I have 2 questions.

1. If I want to see all the comp invoices at onece(bcz any how company name wont change, only the no. of invoices will change, so if I keep other company name in next cell, Do I need to keep helper column in the 1st sheet for all the companies.


2. From a list of data If I want to pick randomly 5 numbers, whats the forumla.


Thanks.

Vinu.

 

[Hui]

Vinu

1. You can either set a large number of rows as described in the orginal response, where X = Number of invoices or you can set the original data area as a Table and then use filters


2a. Add a Helper Column and put the =Rand() formula in, Copy and paste Random numbers as values and then sort by the Random Column, pick the first 5 Numbers, or


2b. Add 2 Helper Columns to add a Random number and then Rank them as described in the orginal response above.