preventing duplicate values

[Reqsquirrel126]

Hi,

I'm struggling to make this formula only display the first instance of a result. Any help would be amazing.

=IFERROR(INDEX(NewName,SMALL(IF(NewBranch=$A1,IF(NewWeek=$B1,ROW(NewName)-ROW(INDEX(NewName,1))+1)),1)),0)

thanks

Martin

[Pete_UK]

Did you commit it as an array formula? In Excel you will need to use the key combination of Ctrl-Shift-Enter (CSE) to commit the formula instead of the usual Enter, and if you do this correctly you will see curly braces { } around the formula when viewed in the formula bar (though you must not type these yourself).

I see in your profile, however, that you are using Google Sheets, and if that is the case then CSE doesn't work. I think you need to use the ArrayFormula function around the formula that you quoted.

Hope this helps.

Pete

[Reqsquirrel126]

Hi,

I've tried it as an array formula and it still doesn't work

It worked perfectly when I was dealing with a week number instead of individual dates but now I've expanded the source data to have individual entries for each date it doesn't work

If you could help with this I would be very grateful

thanks

Martin

[Pete_UK]

It would help if you attached a sample Excel workbook, and the procedure for doing this is described in the yellow banner at the top of the screen.

If you missed it, though, you can do this: click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

[Reqsquirrel126]

Hi Pete,

Would it be easier to share the link with the spreadsheet with you in a DM?

[Pete_UK]

No.

If you use a free forum then you have to be prepared to share with everyone.

Pete