find same numbers in a list in Excel

[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!

[swatsp0p]

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

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

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

Good Luck.

[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

[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
"Ekser" <[email protected]> wrote in
message news:[email protected]...
|
| 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...o&userid=11400
| View this thread: http://www.excelforum.com/showthread...hreadid=375559
|