find same numbers in a list in Excel

E

Ekser

Hi Excelers!

I have exported a log list to Excel, containing a large amount of data
(about 10000 rows). The logs look like this:

30.05.2005 8:08:41 Log Entry Nr. 8610333 User CW_USER starting
SAP_SalOrd_G Create.
30.05.2005 8:08:41 I01: Calling method Z_XR_SALESORDER_C1
30.05.2005 8:08:42 Attribute Name: Z_XR_SALESORDER_C1 - Value: -
AppText:
30.05.2005 8:08:42 I03: returning to connector agent via RFC channel
30.05.2005 8:08:42 Regular Order pRegular Order p0000708204 a a
*0000708204* has been saved
30.05.2005 8:08:42 Log Entry Nr. 8610333 Ending SAP_SalOrd_G Create -
Runtime: 1.204 milliseconds.

There is more than a thousand logs like this! What I need to find is
possible double doc numbers (here in bold). Is there a way to make a
search for duplicates, but keeping in mind that I need only to compare
document numbers?


Thanks in advance! ;)
 
J

Jim Rech

I think any approach would probably have to start with parsing the data
(Data, Text to Columns perhaps) so that the field of interest is in its own
column. From there you might sort by that column and then add a column of
formulas to identify matches (like =IF(A2=A1,"X","")). From there you could
use Data, Filter, AutoFilter on the "X" column to find just the "X"'s which
would be the duplicates.

--
Jim
message |
| Hi Excelers!
|
| I have exported a log list to Excel, containing a large amount of data
| (about 10000 rows). The logs look like this:
|
| 30.05.2005 8:08:41 Log Entry Nr. 8610333 User CW_USER starting
| SAP_SalOrd_G Create.
| 30.05.2005 8:08:41 I01: Calling method Z_XR_SALESORDER_C1
| 30.05.2005 8:08:42 Attribute Name: Z_XR_SALESORDER_C1 - Value: -
| AppText:
| 30.05.2005 8:08:42 I03: returning to connector agent via RFC channel
| 30.05.2005 8:08:42 Regular Order pRegular Order p0000708204 a a
| *0000708204* has been saved
| 30.05.2005 8:08:42 Log Entry Nr. 8610333 Ending SAP_SalOrd_G Create -
| Runtime: 1.204 milliseconds.
|
| There is more than a thousand logs like this! What I need to find is
| possible double doc numbers (here in bold). Is there a way to make a
| search for duplicates, but keeping in mind that I need only to compare
| document numbers?
|
|
| Thanks in advance! ;)
|
|
| --
| Ekser
| ------------------------------------------------------------------------
| Ekser's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=11400
| View this thread: http://www.excelforum.com/showthread.php?threadid=375559
|
 
S

swatsp0p

You will probably need a VBA solution to this problem. I believe you
post should be in the Programming Forum and should contain mor
information, such as:


- Is your data split into columns?
- How do you identify which numbers to search for?
- Will duplicate numbers always be in the same row/column/cell? I
other words, how wide must the search be?
- Can the same number be repeated multiple times?
- Are all numbers to search for the same length?
- Are there any duplicates that would be acceptable? How do yo
Identify -them- (if any)?
- What do you want to do with dupes when found?


This is just a start, but programmers will need to understand th
limits of the data they are working with...as well as your expectation
for the outcome.

Good Luck
 
D

digvijay21

hi
try doing this

first sort all the rows in desc order eg A10:A100; then in next column
B1 put formula =IF(A1=A2, 0,1) once formula put drag it down till the
column B100 and see if you get any 1 that means its a duplicate data.
hope this helps

regards
digvijay
 
Top