Articles by Client

Hi Everyone,

I have a long sheet (sheet 1), with collunns with Nš Client, Name Client, Nš Article, Name Article, Price, Discount, Final Price.

What I like to know is how to make in (sheet 2) when I put the number of the Cliente, give me all the articles of this Client in collunns "E" to "I".

Thanks for your help and sorry for my bad english!

Folha2

E10=IFERROR(INDEX(INDEX(Folha1!$A$2:$G$12,,MATCH(Folha2!E$9,Folha1!$A$1:$G$1,0)),AGGREGATE(15,6,ROW(Folha1!$A$2:$A$12)-ROW(Folha1!$A$2)+1/(Folha1!$A$2:$A$12=Folha2!$F$4),ROWS(Folha1!$A$2:A2))),"")

Copy down and across

Your English is far better than my Portuguese, so no need to apologise !!

I always like to take a two-pronged approach to these types of problems, i.e. to identify the records which match the criteria, and then to bring those records across to the other file. To do this, I've used column I in the first sheet as a helper column, with this formula in I2:

=IF(B2=Folha2!$F$5,MAX(I$1:I1)+1,"-")

This can then be copied down as far as you need it, in order to accommodate new data being added.

Then you can use this formula in cell E10 of the other sheet:

=IF(ROWS($1:1)>MAX(Folha1!$I:$I),"",INDEX(Folha1!C:C,MATCH(ROWS($1:1),Folha1!$I:$I,0)))

which can then be copied across to I10. The cells G10:I10 can then be formatted as currency, and then all 5 of the formulae copied down as required.

I've set this up in the attached file, so you can easily see how the formulae get translated into your own language, according to your regional settings.

Hope this helps.

Pete