Reoccurring number deletion problem

[JimiPockets]

Basically I need a formula that deletes any row with a reoccurring number. For example

1 1 1 1
1 2 3 4
2 3 4 5
3 3 4 5

Would become
- - - - -
1 2 3 4
2 3 4 5
- - - - -

And no two rows can have more than one matching column in the same slot

1 2 3 4
1 2 4 3

Here the 1's are fine but the 2's match so the whole second line must be deleted

Thanks in advance for the help =]

[rylo]

Hi

Here's something for the first part.

Please Login or Register  to view this content.

Is it possible to sort the data before processing the second part?

rylo

[JimiPockets]

Well the problem is that I want to be able to run this with different numbers, it's to create group numbers and tables for events but we never know the number of people and hence the table sizes till just before so I'm working with 4 columns but I want to be able to change the number. I'm attaching an example with 8 (so the columns are powers to 8 hence creating every combination and then leaving me with deleting the repetitions (so as not to send someone to to meet the same person twice). It's a bit of a mind bender.

http://www.sendspace.com/file/ujeata

[rylo]

Hi

Still only 4 columns used, but see if this brings back the expected result from the example file in post #3. If not, then can you put up what the result SHOULD be.

Please Login or Register  to view this content.

rylo

[JimiPockets]

It only left two rows! I think I failed to explain. The results should leave much more.
Each number shouldn't repeat more than 6 times in each column (so when working with 8 that would be 48) this is to create 8 groups of 6 people.
So in the first column all the 1s 2s etc go together. (the four numbers are the 4 groups people will go to)
Then everybody changes groups (indicated by the second column) where they cant meet anyone they've already met.
This happens twice more, each time further limiting who they can meet by who they've already met.

I am trying to create these numbers by filtering every combination of the numbers (the 4096 rows) to just the ones that allow 6 groups to be created per column each time.
Think of it as very complicated speed dating!

[rylo]

Hi

If you have 8 people, and you are trying to break them up into groups of 4, where everyone has to meet completely new people in each group, then I'm not sure how you are going to do it. Taking a simple grouping
1,2,3,4 - everyone new. If you then continue with person 1, then their second group would have to be
1,5,6,7.

The only person left to meet for person 1 is person 8. So the 3rd group for person 1 would have to be
1,8 ??? Then you run into trouble as all the other people have been met by person 1.

So for 8 people, exactly what are the groups that can be created??

rylo

[JimiPockets]

Ok, this is hard to explain!
6 people per group
8 groups per session
4 session
The columns are the sessions
The rows are the people and the table numbers to which they will go.
Everyone has to meet 5 new people in each group (6 minus themselves)

This is also a bit simplified because in reality we won't know the number of groups until everyone's RSVPd so if we get 60 there will be 10 groups (my above example is 48 people). So the 1-8 should be flexible i.e. 1-n where n is the number of people divided by 6.
This also gets more complex as if the number is indivisible like 52 that would be 8 groups with 4 left over. Hence there would be 8 groups of 6 and one of 4.

I need all this to work automatically and it's doing my head in

If you can think of a better way than eliminating duplications in every option like I started doing then I would be very grateful and If you could produce the spread sheet I would pay you for your troubles.
Thanks!

[rylo]

Hi

Think smaller for a moment and build an example output for each of the scenarios where you have 6 people, then 12 people, then maybe 18 people. Just provide the final result you want. Call the people A,B,C,D,E and F for the first round. Explain how you work out the number of people per group, groups per session and the number of sessions. I want to see the final result only.

rylo