To avoid duplication of data extraction in array formula

I actually try to do a worksheet which able to help me to track the customer and also the stock inventory in daily basis

however when i try to make a summary list, it seems that i do not know how to avoid duplication of data extraction from the master list

Capture.PNG


i hope the uploaded picture and the excel file can explain better than myself

hope that someone can help me

thanks

Hi,

You can use an INDEX MATCH COUNTIF to produce your list, as shown on this website:

http://stackoverflow.com/questions/1...alues-in-excel

Personally I like to use a Dynamic Named range with something like this. It will automatically adjust to the number of lines of data to capture all of your list.

Info can be found here:

http://www.excel-easy.com/examples/d...med-range.html

In the attached version of your file I created a Dynamic Named range and used it in the new formula to produce the list you are looking for.
The formula is an array formula which needs to be entered with a SHIFT CTRL ENTER.
The "PeopleList" is the named range.

Please Login or Register  to view this content.

Hope this is helpful.

Cheers

Thanks
This thing is really help me
totally solve my problem....

but i wondering why your input formula is totally different than me

yours is

=IFERROR(INDEX(PeopleList,MATCH(0,COUNTIF($A$3:A3,PeopleList),0)),"")

mine is

=IFERROR(INDEX(STOCK!B$2:B$2000,SMALL(IF(STOCK!$A$2:$A$2000=CLOSING!$A$3,ROW(STOCK!B$2:B$2000)-ROW(STOCK!B$2)+1),ROWS(STOCK!B$2:B2))),"")

how come mine is so difficult and complicated compare to yours

(I actually use this formula because i get it from other websites)

:

In trying to figure out how to explain the difference between your formula and the one I came up with I discovered a mistake I made.
I neglected to take into account that you wanted your list to only be unique for the date that is in A3.

Here is the corrected formula:

Please Login or Register  to view this content.

It is using an additional Named Range for the dates on the STOCK sheet: "DateList".

The difference between our formulas is the COUNTIF. It looks at the entries above in order to not duplicate an entry.

Hope this is helpful.

Cheers