LinkBack URL
About LinkBacks
I need to find what numbers are missing in a column of consecutively-numbered barcodes, then end up with a list of those numbers (col. A on the Carts sheet attached). Is there any way to do this without VBA, like using VLookup and something else?
I need to find what numbers are missing in a column of consecutively-numbered barcodes, then end up with a list of those numbers (col. A on the Carts sheet attached). Is there any way to do this without VBA, like using VLookup and something else?
You can solve this with the SMALL-function. See attachment.
Formula:
Please Login or Register to view this content.
Column K contains a number from 1 to x depending on the number of missing numbers.
Trade-off when using a formula is that you need to copy the formula yourself.
Also the formula doesn't stop when it has a result. It will always do all the lookups.
Cheers!
Tsjallie
--------
If your problem is solved, pls mark the thread SOLVED (see Thread Tools in the menu above). Thank you!
If you think design is an expensive waste of time, try doing without ...
I tried it this way
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
Here's an alternative approach - Array-enter the formula below and fill down beyond what's necessary:
=IFERROR(SMALL(IF(ISNA(MATCH(ROW(OFFSET(Carts!$A$1,Carts!$A$3-1,0,Carts!$A$783-Carts!$A$3,1)),VALUE(Carts!$A$3:$A$783),0)),ROW(OFFSET(Carts!$A$1,Carts!$A$3-1,0,Carts!$A$783-Carts!$A$3,1))),ROW(1:1)),"")
This version avoids the helper column and should auto-adjust to whatever numbers you put in "carts", but the trade-off is that it's volatile, so it will calculate a bit more slowly. Take a look at the attachment to see if it'll do:
If your problem has been solved, please use "Thread Tools" to mark the thread as "Solved".
If you're grateful for this site's existence and would like to contribute to the community, please consider posting something hilarious in our joke thread:
https://www.excelforum.com/the-water...ke-thread.html
Hi trstew,
See if this makes more sense to you. This is just another format to show what I think you want. You will need to have your data sorted from small to large in column A.
Missing Carts.xlsx
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Another way. Try array entering this formula in J3 and fill down to about row 230 or until you get blanks.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 10-05-2017 at 08:52 PM.
Dave
WOW, it's great to see all the suggestions and contributions.
Especially the OP,haven't see if he/she even likes it
Still haven't had time to look at all these in-depth... Great to have so many replies - Thanks!!Originally Posted by Keebellah
WOW, it's great to see all the suggestions and contributions.
Especially the OP,
Just 'holler' if you need help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks