Display the value not duplicate

[emina002]

Hi somehow I am new and seems can't explain my problem very well,not well good in Excel VBA. First, please see attachment for the sample of my problem.Regarding of how can I filter or display the value on one sheet to another,Its regarding of distinct or duplicate value.On Sheet1 Column A I have ACC# and will look for exact value on Sheet2 on Column C and returns the result on Sheet1 Column C,D with Alt Acc # and Desc 2.

I highlight my problem with green.Whenever I run the code looking for the value on Sheet2 it always return the last value of Desc2 and Alt Acc #. What I need for example if I look for the "99991001" sheet 1 returns the 3 unique Desc2,in short no duplicates.Please I just need your help.Thanks for helping me.

[MarvinP]

Hi emina002,

See the attached with 2 helper columns on each sheet.
The first helper column counts the number of the account numbers in each table.
The second helper uses the account number and appends the count above to create a unique index.
Then it is a simple VLookup problem.

hope this helps. Some smart gurus can do this using Array Formulas or Index Match formulas but by moving the Helper2 column to column A, you can use VLookup.

[Haseeb Avarakkan]

Hello emina002, welcome to the forum!

If you can sort sheet2 Acc# in ascending order, can use INDEX/MATCH.

Sheet1 C2, copy down & across.

=INDEX(Sheet2!A:A,MATCH($A2,Sheet2!$C:$C,0)+MAX(0,COUNTIF($A$2:$A2,$A2)-1))

If you can't sort the Sheet2 data, try,

Sheet2 C2,

=IFERROR(INDEX(Sheet2!A:A,SMALL(IF(Sheet2!$C$2:$C$11=$A2,ROW(Sheet2!$C$2:$C$11)),COUNTIF($A$2:$A2,$A2))),"")

This is Array formula, must hit CONTROL+SHIFT+ENTER, not just ENTER. then copy down & across

[emina002]

wow thanks for the BIG help I really appreciate it.I will try this and inform you if its working or not..thanks..

[emina002]

it works now..for the suggestion