LinkBack URL
About LinkBacks
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.
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.
Last edited by kswapnadevi; 06-26-2018 at 01:29 AM.
Before asking new, respond and finalize your other threads first.
https://www.excelforum.com/excel-pro...el-sheets.html
https://www.excelforum.com/excel-pro...at-number.html
Those threads are solved sir
Please give the solution for this thread sir
Upload a workbook with your exact desired result.
to 69000 ??Originally Posted by kswapnadevi
i see a cell containing:
310571079
Not only like that.
Contains the values like BBxxxx, Total-xxxx.
OP should learn how to ask question first.
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)
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.
please help me sir
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.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
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.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Ben Van Johnson
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.
@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
Register To Reply
Bookmarks