Splitting Data Table into 4 parts based off of number of unique values

Help
,
1

Hi,

I am trying to split an excel file into 4 equal parts based on the number of Unique Tickets. I need to include all instances of each unique value and all of the data contained in the rows. The file size and number of unique tickets will change, so the solution needs to be more dynamic.

For example, the excel file is set up similar to this:

Ticket, Name, State, Number
713, SUSAN, OH, 1
714, CAY, OK, 2
714, CAY, OK, 2
715, LYAN, NH, 3
716, PAM, TN, 4
716, PAM, TN, 4
718, ANTWAN, IN, 5
719, CAMILA, NC, 6
719, CAMILA, NC, 6
720, ELON, TN, 7
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
723, JENNIFER, OH, 10
723, JENNIFER, OH, 10
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
725, AAROHI, MI, 12
726, MAXWELL, MD, 13
726, MAXWELL, MD, 13
726, MAXWELL, MD, 13
727, BOB, NE, 14
727, BOB, NE, 14
727, BOB, NE, 14
728, SHARON, OH, 15

I Need it split like this:
Ticket, Name, State, Number
713, SUSAN, OH, 1
714, CAY, OK, 2
714, CAY, OK, 2
715, LYAN, NH, 3
716, PAM, TN, 4
716, PAM, TN, 4

Ticket, Name, State, Number
718, ANTWAN, IN, 5
719, CAMILA, NC, 6
719, CAMILA, NC, 6
720, ELON, TN, 7
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8
721, SUZANNE, MI, 8

Ticket, Name, State, Number
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
722, JAYDEN, NC, 9
723, JENNIFER, OH, 10
723, JENNIFER, OH, 10
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
724, CHRISTINA, MI, 11
725, AAROHI, MI, 12

Ticket, Name, State, Number
726, MAXWELL, MD, 13
726, MAXWELL, MD, 13
726, MAXWELL, MD, 13
727, BOB, NE, 14
727, BOB, NE, 14
727, BOB, NE, 14
728, SHARON, OH, 15

I have found solutions to split each unique set of ticket numbers to a sepperate sheet or data table, but have not been able to find a solution to my issue.

Any help will be greatly appreciated, thank you.

2

Loop the records check for the threshold number(4 for example ) to stop and create a datatable

Once it reaches the threshold copy it a datatable and clear the count and reiterate

it will work i guess .

3

this will need some work, but do the easy way… sort the source by number column, loop the rows and build a data table with same schema and add rows until you find the 5th number then create a new one and add the rows again until the end…

4

Thank you for your response.

I am still fairly new to this. Would you be able to post an example?

5

ok, is your data table already sorted by number?

6

Yes, the data table is already sorted by number.

1 Like
7

see how i would do:
Sequence3.xaml (14.4 KB)