MACRO for =IF(ISERROR(INDEX(SMALL(ROW -> Return unique matches vertically

[mdc23]

Hi all,

I am trying to turn a formula into a macro. I have the active workbook which I am pulling data into, and the lookup workbook which I am pulling the data from.

The formula looks up the name in column A of the active workbook and finds it in column B in the lookup workbook. Once it finds the name in the lookup workbook, it returns all corresponding sports from column A of lookup workbook into column BL of the active workbook.

Note: There are 10 rows for every name in the active workbook. The formula only returns the unique values that match the name and leaves the remainder of the 10 cells blank. See screenshots attached. Thanks in advance!

Current Formula:
=IF(ISERROR(INDEX([LookupFile!$A:$B,SMALL(IF([LookupFile!$B:$B=$A6,ROW([LookupFile!$B:$B)-ROW($A$1)+1),COUNTIF(A$6:A6,A6)),1)),"",INDEX([LookupFile!$A:$B,SMALL(IF([LookupFile!$B:$B=$A6,ROW([LookupFile!$B:$B)-ROW($A$1)+1),COUNTIF(A$6:A6,A6)),1))

 

[NARAYANK991]

Hi ,

Please upload both workbooks.

Narayan

 

[mdc23]

Hi Narayan, Thanks for replying.

Uploaded one workbook for reference. You can see how the formula in column BL of destination tab is pulling data from column A of lookupfile, Leaving rows blank if there are no more corresponding values for that name. The only difference is mine will ultimately pull between two different workbooks instead of sheets. Columns will remain same but WB1 vs Wb2 instead of lookupfile sheet vs destination sheet. Hid some columns between column A and BL in destination tab for ease of view. Thanks!

 

[Nebu]

Hi:

You don't need an array formulae for this you can use the following non- array formulae as well

=IFERROR(INDEX(LookupFile!$A$2:$A$19,AGGREGATE(15,6,(ROW(LookupFile!$A$2:$A$19)-MIN(ROW(LookupFile!$A$2:$A$19))+1)/(A6=LookupFile!$B$2:$B$19),COUNTIF($A$6:A6,A6))),"")

Thanks

 

[NARAYANK991]

Hi ,

See the attached file.

I have made some changes to the data , so that the code caters to the most general case ; you can have the names in any random order , the games can repeat for a person , and across persons.

The code is commented so that in case any changes need to be made , you can do it.

There may be problems if your data set runs into thousands of rows.

Narayan