![]() ![]() Select the range A2 through the last value in column A.Follow these steps, again assuming that your values start in cell A1: =IF(A2=A1,"Duplicate",IF(A2A1+1,"Gap",""))Īnother approach is to use conditional formatting on the cells in column A. If you prefer to know a bit more about the error, you could use a more detailed formula: Assuming your numbers start in cell A1, I put this into cell B2:Ĭopy the formula down as many cells as necessary, and you'll easily see the word "Error" next to any value that isn't consecutive to the value just above it. The first is one I use quite often: I add a helper column next to column A. There are a number of ways you can go about figuring out where there are missing numbers. Examining the list manually is both tedious and error-prone, so Marcya wonders if there is a way to somehow highlight any "missed numbers" (those that aren't consecutive with the one before) or to compile a list of numbers that were missed in the list. These numbers are supposedly consecutive, but she doesn't know if that is true. >but I want to make sure it has broader applications.Marcya has a long list of sorted numbers in column A of a worksheet. >I hope you understand that I like your approach, Consequently, I prefer the amended "Biff formula". > characters shorter, but I'd much rather have the missing values list in ![]() > Note: I also had to tweak my formula to make it work. > After more play, I came up with this array formula: > as missing, stopping at 65,656 of course. > As it stands, your original formula would try to list from 1 through 198765 > I hope you understand that I like your approach, but I want to make sure it My amended array formula seems to handle that situation: It can fail if there are blank cells in the Col_A range. Now, I've noticed another problem, though: To make my last "Biff Formula" reactive to the number of items in Col_A I Latest in a series of final adjustments :\ > Since the sequence you're testing is 1:19 you just need to change the ROW() It ended up a fewĬharacters shorter, but I'd much rather have the missing values list inĪscending order. Note: I also had to tweak my formula to make it work. I hope you understand that I like your approach, but I want to make sure itĪs it stands, your original formula would try to list from 1 through 198765Īs missing, stopping at 65,656 of course.Īfter more play, I came up with this array formula: > Hello, I'm looking for a way to quickly find what numbers are missing > This one is limited to number sequences from 1 to 65536. > This amended version got it back on track: > larger than the maximum referenced row number. > Evidently, it only works as long as the maximum number in the sequence and the formula only identifed missing items: 1, 2, 3, 4, 7 and 8, > I entered the below series in cells A1:A9 > Always eager to adopt a better solution, I experimented with the formula ![]() Since the sequence you're testing is 1:19 you just need to change the ROW() I can sort them ascending, but how do I find if there are > Hello, I'm looking for a way to quickly find what numbers are missing in This amended version got it back on track: Larger than the maximum referenced row number. and the formula only identifed missing items: 1, 2, 3, 4, 7 and 8,Įvidently, it only works as long as the maximum number in the sequence isn't I entered the below series in cells A1:A9 Always eager to adopt a better solution, I experimented with the formula you ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |