Checking for copies.. but not an easy one %(

[popovsky]

I have a huge problem.
I have a xls. file being created as an export from another program.
It has a number of worksheets that i have to analyse with vba-script in
excel 2002 as follows:
1. on each worksheet in column A beginning with 4 row and ending with
the last not empty row i want to check if there are strings with same
text before "-"symbol and if there is - delete that whole row.

example
sheet BEFORE:
textb
texta
texta-44

"texta-44" should be deleted. but only the cell with fewer symblos
should be deleted (not "texta" in this example)
sheet AFTER:
textb
texta

2. Then check on every two sheets in column "A" : if there is a text on
one sheet same as text on the other sheet (in any row of column A) and
if there is put that text in the msgbox along with the names of the
sheets, that share this text.

for example if we take sheets from previous example to do that it would
result in:
MSGBOX will say
"in sheet "BEFORE" and sheet "AFTER": textb, texta.
in sheet "x" and "y": abc"
where x and y are names of the sheets and abc is a text that is in
sheet x and in sheet y.
and this way process all possible pairs of sheets in the workbook.

this is all too tough for me..
probably someone of you will be able to do this challenge. i would
REALLY appreciate.

 

[popovsky]

plz, wizards, help me out with this..

 

[BrianB]

Hopefully this will help a bit.
Insert an empty column A.
Say that your column with texta-44 etc. is column B starting in B1.
In cell A1 copy/paste this formula & copy down :
=IF(ISERROR(FIND("-",B1)),"",LEFT(B1,FIND("-",B1)-1))

If there is a "-" in column B it shows the characters to the left
otherwise blank.

You could then sort on column A then check for duplicates.

Perhaps by using another column with the formula :-
=IF(A1=A2,1,0)
which puts a number 1 in duplicate row

 

[popovsky]

That's not a bad idea - i will definitely try, but most of all i'm
looking for help with a macro for second task :