CODEWITHSUNDEEP
excel
Scooter Daraf
Scooter Daraf

Reputation: 535

Find matched records and not duplicate it

I have a formula that works good to bring matched values which have a value B so i select that value 'Lista!A4:A182' .

 =IFERROR(INDEX(Lista!A4:A182;MATCH("B";Lista!D4:D182;0));"")

But the problem that it brings duplicates when the matched value is located on nth row , so all the precedent rows becomes duplicated till that matched row .

oBS : i have no duplicated rows .

exempel :

  Name       class
 aaaa        B
 DDDD        B
 vvvvv       A
 wwwww       A
 xxxxx       B

the result it comes like this :

 Name        
 aaaaa
 DDDD
 xxxx
 xxxx
 xxxx

But i want it to be

  Name        
  aaaaa
  DDDD
  xxxx

Any help pls ?

Upvotes: 0

Views: 48

Answers (1)

Axel Richter
Axel Richter

Reputation: 61870

Why not simply using Excel's filter approaches? https://support.office.com/en-us/article/Quick-start-Filter-data-by-using-an-AutoFilter-08647e19-11d1-42f6-b376-27b932e186e0?ui=en-US&rs=en-US&ad=US In your case simply set AutoFillter in sheet Lista and filter in column D class = "B".

With a formula this is only possible with using an array formula:

{=IFERROR(INDEX(Lista!$A$4:$A$1000,SMALL(IF(Lista!$D$4:$D$1000="B",ROW(Lista!$D$4:$D$1000)-ROW(Lista!$D$3)),ROW(1:1))),"")}

This is an array formula. Input it into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets will then appear automatically.

But array formulas are of bad performance. So the filter approach will be better practice with Excel.

Upvotes: 3

Related Questions