Extracting a set of random values with no duplicates and one condition

**iLikeTurtlesVmuch** · 10-23-2021, 01:32 PM

Hey Forum,

Nice to meet you all! I have a column for shipment IDs and another column for agents. What I'm trying to achieve is, generate 4 random shipment IDs that correspond to each agent. Through some extensive googling and browsing various threads in this forum, I have managed to achieve that but now I notice the values are not unique. For example, for someone like Agent 4 who only has 5 shipment IDs, you often see E and F columns having the same value. What I want is that, all 4 randomly selected shipments IDs for each agent would be unique, with no duplicates. I guess this would be the case of adding an additional IF statement and updating the end of the formula to COUNTIFS to check the previous Shipment ID columns but for the life of me I can't figure it out.

Thanks in advance for any tips!

**JohnTopley** · 10-23-2021, 02:52 PM

Using a helper column

in C

=rand()

copy down

in E2

=INDEX($A$1:$A$43,MATCH(LARGE(OFFSET($C$1,MATCH($D2,$B$1:$B$43,0),,COUNTIF($B:$B,$D2)),COLUMNS($A$1:A$1)),OFFSET($C$1,MATCH($D2,$B$1:$B$43,0),,COUNTIF($B:$B,$D2)),0)+MATCH($D2,$B$1:$B$43,0))

copy across and down

**Fluff13** · 10-23-2021, 03:05 PM

Hi & welcome to the board.
Another option in E2 dragged down only

Formula:

Please Login or Register  to view this content.

**iLikeTurtlesVmuch** · 10-24-2021, 05:16 AM

Thank you so much both!

**Fluff13** · 10-24-2021, 08:58 AM

Glad to help & thanks for the feedback.

**iLikeTurtlesVmuch** · 10-27-2021, 09:35 AM

Thanks again! However it looks like the formula doesn't return the correct results if an agent has 1 shipment ID only. For example, it returns Agent 3's first shipment ID for Agent 2 here:

**Fluff13** · 10-27-2021, 09:50 AM

Did you try the formula I suggested?

**JohnTopley** · 10-27-2021, 09:59 AM

Try

=IFERROR(INDEX($A$1:$A$50,MATCH(LARGE(OFFSET($C$1,MATCH($D2,$B$1:$B$50,0)-1,0,COUNTIF($B:$B,$D2)),COLUMNS($A$1:A$1)),OFFSET($C$1,MATCH($D2,$B$1:$B$50,0)-1,,COUNTIF($B:$B,$D2)),0)+MATCH($D2,$B$1:$B$50,0)-1),"")

Extracting a set of random values with no duplicates and one condition

LinkBack

Thread Tools

Rate This Thread

Display

Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Re: Extracting a set of random values with no duplicates and one condition

Thread Information

Users Browsing this Thread

Similar Threads

Random Numbers assigned to static values multiple times no duplicates....?

Extracting random length data from a random length string

[SOLVED] Ordering random values and texts for a chance-random based TV gameshow.

[SOLVED] Extracting multiple values based on the condition...Pls help (Urgnt)

Managing Duplicates: Extracting a list of duplicates from a column and creating a list.

Extracting all components of name when name field content is random

Extracting random values in a list

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions