compare a value, if there is an match, copy 3 rows

**ABBOV** · 12-16-2010, 10:21 AM

Hello,

I've got the following problem, i would like to compare a value, if there is an match, copy 3 lines to another sheet. And repeat this step until te last line.

It's rather complicated for me because in "row 2" there can be the same value but that only has to be copied, not matched again.

I've created an example, please take a look.

Thanks in advance.

ABBOV

**NBVC** · 12-16-2010, 11:38 AM

See attached.

Added helper column in Blad2 with formula in J3:

=IF(AND(E3<>Blad1!$G$2,E2<>Blad1!$G$2,TRIM(E3)<>""),"",IF(OR(AND(TRIM(E2)="",E3=Blad1!$G$2),ISNUMBER(Blad2!J2)),COUNT(Blad2!$J$2:J2)+1,""))

copied down. This to tag the matching records to pull

Then in K3, formula to get count of records:

=MAX(J:J)

Then in Blad1 to get the records, in A8:

=IF(ROWS($A$1:$A1)>Blad2!$K$3,"",INDEX(Blad2!A:A,MATCH(ROWS($A$1:$A1),Blad2!$J:$J,0)))

copied down as far as you need and across the columns.

Format these columns as Custom: 0;-0;;@ to suppress the 0's for blanks retrieved.

**ABBOV** · 12-21-2010, 03:55 AM

Hello NBVC,

Thanks for the response, it set me in the right directoin, it seems to be working fine but i've got a small problem.

When the cell value is above 255 characters, the formula results in ######. Is there a quick solution for this?

Thanks in advance.

ABBOV