Identification of duplicate numbers and missing numbers in multiple columns

1. Identification of duplicate numbers and missing numbers in multiple columns

I have a excel file (attached) consists of multiple columns with numbers from 1 to 69000. Duplication of numbers and missing numbers are to be identified and to be displayed in a new excel sheet.

2. Re: Identification of duplicate numbers and missing numbers in multiple columns

https://www.excelforum.com/excel-pro...el-sheets.html
https://www.excelforum.com/excel-pro...at-number.html

6. Re: Identification of duplicate numbers and missing numbers in multiple columns

I have a excel file (attached) consists of multiple columns with numbers from 1 to 69000.
to 69000 ??
i see a cell containing:
310571079

7. Re: Identification of duplicate numbers and missing numbers in multiple columns

Not only like that.

Contains the values like BBxxxx, Total-xxxx.

OP should learn how to ask question first.

8. Re: Identification of duplicate numbers and missing numbers in multiple columns

if you can list the permitted values
in one column,
then COUNTIF (in the next column)
will tell you how many times each value is found
you can then distinguish
0 (missing),
1,
and 2-or-more (duplicates)

9. Re: Identification of duplicate numbers and missing numbers in multiple columns

I uploaded the excel file. Duplicate numbers in that file are to be find out and to be copied into another sheet. Missing numbers in that file between 1 to 100000 are to be find out and copied into another sheet.

10. Re: Identification of duplicate numbers and missing numbers in multiple columns

Here is a small example of Pat's suggestion.
The list of all possible values is in column XFC of the Total consolidated sheet.
Column XFD of the Total consolidated sheet is populated using: =COUNTIFS(A\$1:J\$10,XFC1)
The array entered formula* to find duplicates is:
Formula:
`Please Login or Register  to view this content.`

The array entered formula* to find missing values is:
Formula:
`Please Login or Register  to view this content.`

Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

11. Re: Identification of duplicate numbers and missing numbers in multiple columns

Y V SURYANARAYANA,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

12. Re: Identification of duplicate numbers and missing numbers in multiple columns

Y V SURYANARAYANA
Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore requests by Administrators, Moderators and senior forum members regarding forum rules.

If you are unclear about the request or instruction then send a private message to them asking for clarification.

All participants:
Please do not post a reply in a thread where a moderator has requested an action that has not yet been complied with e.g Title change or Code tags...etc. Thanks.

13. re: Y V SURYANARAYANA

@protonLeah
are you referring to some post which has been deleted?
(i see no indication of a deleted post)

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1