I am trying to combine rows into as few groups as possible. Up to 7 rows can become part of the same group of frequencies 1-7, but only within the same product type (column A). Each group does not need to have a complete sequence of 1-7, but no digit in column B can be repeated within a single group.
Example: A12345 can be grouped with A6 and A7, but it can’t be grouped with B67 or A56.
My input is only columns 1 and 2. My desired output is columns 3 and 4 appended to the original table, with column 3 as the Group # and column 4 as the Product+Group ID.
Sample data:
Product | Frequency | Group | ID |
A | 1234567 | 1 | A1 |
A | 123457 | 2 | A2 |
A | 12345 | 3 | A3 |
A | 6 | 2 | A2 |
A | 6 | 3 | A3 |
A | 6 | 4 | A4 |
A | 7 | 3 | A3 |
B | 1234 | 1 | B1 |
B | 67 | 1 | B1 |
B | 6 | 2 | B2 |
Any suggestions are appreciated! Please let me know if I can clarify anything.
