Invoice Queue Sequence

**Delta729** · 11-10-2017, 11:34 PM

I work in an Accounts Payables department. I get a daily report where certain invoices appear due to some type of issue. I then have to combine these reports so I can determine for how long (how many days) they appeared in this queue.

In column E, I have inserted the invoice number and what I'll call sequence they were in the report. Meaning, if an invoice was on the report from 1/5/17 thru 1/8/17, they would be in sequence A or 1. (I'll be adding in another formula for the min and max date for the sequence, so I don't need help with that part). I'm trying to add in a formula so this won't be a manual process. The formula will be in column C, which I don't know how to make it work and so I input the intended output in Column E. I only added up to three sequences of events for one of the invoice, but it could actually have more, so the -A could actually be -1 and then -2 and so on. I kind of color coded this to maybe make more sense of it

Does any of this make any sense where anyone could offer any assistant? If not, (which it probably doesn't) I'll do my best to try to answer any of your questions.

**Logit** · 11-11-2017, 12:14 AM

.
I believe the main question will be ...

....how many days does an invoice need to remain in que to be classified as "A"

....how many days does an invoice need to remain in que to be classified as "B"

....how many days does an invoice need to remain in que to be classified as "C"

And how many classifications are there - what are the total days in que to be classified as such for each?

Or .. did I totally misunderstand your classification system ?

**Delta729** · 11-11-2017, 12:27 AM

Sorry, I don't know how to explain it, which is why I attached the file and what my first goal is. To get to the total counts will be the next step.

For now, I'm just trying to get the formula in column C to be like column E.

P.S. - instead of having the invoice and then a dash A and then dash B, I'm guessing it would be actually easier to put something in the formula to make the output with a -1, then -2, then -3, etc... Does that make any sense?

**Logit** · 11-11-2017, 09:23 AM

.

For now, I'm just trying to get the formula in column C to be like column E.

That's easy enough. In E2 enter this formula :

=C2

>> copies data from C to E <<
Or if you want to copy from E to C ,In C2 enter this formula :

=E2

Then drag that down the column as far as needed.

However, that doesn't really have any logic behind it. Why do you attach an A, B or C to the end of invoice number ? What do the individual letters represent ?
If all that occurs is to copy the data from C column to E column (=C2) the only thing that is happening is avoiding the effort of manually typing in the data.
You are not fully using the power of EXCEL to accomplish your goal.

**Delta729** · 11-11-2017, 02:19 PM

Obviously I did a terrible job trying to explain what I trying to do, so I start from scratch. I changed the A,B,C to a count to try to make more sense of it.

The same invoice number could appear on the list for several days, and then get back on the list. In the example the file contains three invoice numbers.
In the example, invoice # "1235" appears on the list on three consecutive date ranges. 1/5/17 - 1/6/17, which would get put in the queue for first time a -1 after the invoice #. Then on 1/11/17 it would be back in the queue as -2. And then again for 1/21/17 - 1/22/17 it would get a -3.

Column E is intended to just illustrate the values of what column C should be. (There won't actually be a column E queue)

PS... the formula I have in column C is clearly wrong, it was just my attempt at trying to get something to work.

**Delta729** · 11-11-2017, 06:54 PM

Does anyone have any suggestions or questions?

**JeteMc** · 11-12-2017, 11:08 PM

This proposed solution employs a helper column (G) which may be moved and/or hidden for aesthetic purposes.
The formula that populates the helper column is:

Formula:

=IF(B2<>B1,1,IF(SUM(A1,1)=A2,G1,G1+1))

The formula that populates column C is: =B2&"-"&G2
Let us know if you have any questions.

**alansidman** · 11-12-2017, 11:16 PM

crosspost: https://www.mrexcel.com/forum/excel-...nce-queue.html
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Invoice Queue Sequence

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Re: Invoice Queue Sequence

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Create Unique Sequence Number in Cell for invoice

[SOLVED] Check the Queue Number from column H in Sheet1 and change it to Queue Name

Priority Queue

Excel 2007 : generates the next invoice # in sequence

[SOLVED] Navigating a Queue

TRACK TIME THEN INVOICE WITH SEQUENCE

EXCEL queue VBA

Bookmarks

Bookmarks

Posting Permissions