problem comparing rows and copying result to another sheet

[mehdoush]

hey guys
i have a problem with macro that compares rows here's how it should work
if row starts with t76 then comapre row t76 with other rows (t75, t74), i want the macro to compare like follows :

if Connector1 and Connector2 from t76 are the same Connector1 and Connector2 from other families (t75,t76) then then copy these rows to sheet "Result".

thanks in advance.
do see the attached file

[GTO]

Well, I imagine I'll take a friendly bash on this one, but I sure get different results.

In short - there are bunches of non-printable characters after some of the "Connector" numbers. I did not check for asc or if theres the same amount/number of characters row to row.

Stripping these (rather inefficiently) gave me markedly different results.
In a throwaway copy of your workbook, try:

[VBA]Sub exx()

Dim _
wksData As Worksheet, _
wksResults As Worksheet, _
rData As Range, _
rCell As Range, _
lLRow_Dat As Long

Set wksData = ThisWorkbook.Worksheets("Sheet1")
Set wksResults = ThisWorkbook.Worksheets("Result")

With wksData


lLRow_Dat = .Cells(Rows.Count, 1).End(xlUp).Row
.Columns("A:B").Insert Shift:=xlToRight
Set rData = .Range("B2:B" & lLRow_Dat)

For Each rCell In rData
rCell.Value = Trim(Left(rCell.Offset(, 1).Value, 1)) & _
Trim(rCell.Offset(, 3).Value) & _
Trim(rCell.Offset(, 4).Value)
rCell.Offset(, -1).Formula = "=COUNTIF($B$2:$B$24,INDIRECT(""B"" & ROW()))"
Next

.UsedRange.AutoFilter Field:=1, Criteria1:=">1"
Set rData = rData.Offset(, 1).Resize(, 4).SpecialCells(xlCellTypeVisible)
rData.Replace " ", ""
rData.Copy wksResults.Range("A3")
.UsedRange.AutoFilter
.Columns("A:B").Delete
End With
End Sub[/VBA]

Hope thsi helps?

Mark

[mehdoush]

thanks a lot GTO

it didn't get the exact result i wanted, but thanks for being interested

regards

[mdmackillop]

mehdoush
Why does row 5 not appear in your results?

[mehdoush]

hey mdmackillop

thanks for noticing this, it's an example, i forgot to mention it.

your help will be appreciated
regards

[GTO]

Greetings mehdoush,

Given your response to Malcom, could you elaborate/explain? Why wouldn't rows 6 & 7 (etc) be included?

row 6: t74 162994_00 168209_00
row 7: t76 162994_00 168209_00

Mark

[mehdoush]

hey again GTO

actually they are included, i just forgot to highlite them

regards