Selecting groups of three

[jpoferreira]

I have a dataset that looks like this:

Loc 1	Loc 2	Loc 3	Value
a	b	c	1000
d	e	f	900
a	g	h	700
g	h	f	600
o	g	h	500
i	i	j	400
i	j	o	300

Imagine that I have teams and I need to assign places for them to go. Each team can go to a max of 3 locations and two teams can't be assigned to the same location.

So I want to find the combinations that are unique in its three locations

The rows are combinations of locations that already match the criteria required.

The value is just because I want to select the ones with the highest value ,so I sorted the combinations of three by value

In this example, the result would be:

Loc 1	Loc 2	Loc 3	Value
a	b	c	1000
d	e	f	900
o	g	h	500
i	i	j	400

See that combinations may have only two locations (i,i,j)

So basically, for every row I need to look at the rows above and see if any of the locations in the current row is in any of the locations in the rows above. If it is, delete the row, if not keep it, then go to the next row. I am trying to use batch macro but I am new to it.

Can someone help me out?

[andyuttley · Accepted solution]

Hi  @jpoferreira

Yes, you can do that with a macro. I've dropped a very quick working example in the attachment below (using your example file). The workflow appends the field [KeepRow] (with a 1 or 0)  so you can then see which rows you're removing at the end. The macro essentially searches for repeat value in any of the rows above (by transposing). I've done it as an iterative macro as we don't then want to compare to any rows above that we have since deleted (i.e. since changed to 0). 

Let me know if there's any issues or it's not what you were after. Feel free to tidy the macro up; I'm sure there are a couple of ways to do this! 

Thanks,

Andy 

[jpoferreira]

Perfect!!

Thanks a lot!