Formula for queue that reset once completed an order

studentlearner

New Member
Joined
Oct 7, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
So currently am working for a queue system that which reset the queue to number 1 once fulfilled the value

This is what i'm working with:

Username IDBuyers IDBuyers Total Order
T039902
T019803
T029704

Once data entry is completed, there will be a button that triggers a formula and should look something like the bottom table:

Queue ID
Q99-T03-01
Q99-T03-02
Q98-T01-01
Q98-T01-02
Q98-T01-03
Q97-T02-01
Q97-T02-02
Q97-T02-03
Q97-T02-04

Question is if there is a formula which can have the queue extract the first table data onto something like the second table.
Any help would be great thank you!!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Could we use a helper column (it could be hidden)?
I have assumed that the values in the Buyers Total Order column are actually text values since they have leading zeros.

21 10 15.xlsm
ABCDEF
1Username IDBuyers IDBuyers Total Order0Queue ID
2T0399023Q99-T03-02
3T0198036Q99-T03-02
4T02970410Q98-T01-03
5Q98-T01-03
6Q98-T01-03
7Q97-T02-04
8Q97-T02-04
9Q97-T02-04
10Q97-T02-04
11 
12 
Q
Cell Formulas
RangeFormula
D2:D4D2=SUM(--C$2:C2)+1
F2:F12F2=IF(ROWS(F$2:F2)>=MAX(D:D),"","Q"&TEXTJOIN("-",,INDEX(A:C,MATCH(ROWS(F$2:F2),D:D)+1,{2,1,3})))
 
Upvote 0
Yeh i'll be using a helpers column, however i still need the Queue ID to reset once the buyer orders has been fulfilled
 
Upvote 0
Instead of having the last digits with a fixed value
Queue ID
Q99-T03-02
Q99-T03-02
Q98-T01-03
Q98-T01-03
Q98-T01-03
Q97-T02-04
Q97-T02-04
Q97-T02-04
Q97-T02-04

It should be more like in a queue and resetting it once fulfilled particularly the last digits:

Queue ID
Q99-T03-01
Q99-T03-02
Q98-T01-01
Q98-T01-02
Q98-T01-03
Q97-T02-01
Q97-T02-02
Q97-T02-03
Q97-T02-04
 
Upvote 0
Upvote 0
Solution

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top