SAP Notes - Quality Assurance - Makro for Excel required to speed up QA

[gesine_stanienda]

Dear Colleagues,

I am the service owner for SAP Notes translation. In order to mass-process translation QA, I have the following requirement:

I have an excel sheet with 2 columns filled out. In one column, you have the source text of the note, in the second the relevant translation.

In each cell, there is text and numbers.

Some text should be identical in adjacent cells. I want to test if these text strings are identical in both adjacent cells.

The texts to be checked take this format:

FLV_1_IIU_WOEMEM

And the like, they are usually function modules and consist of capitals and underscores.

Can this be done?

[script_man · Accepted solution]

Hi Gesine,

If I understand it correctly, it is less here about SAP and more about Excel. For the comparison of two texts one can apply the following formula:

=EXACT(A2;B2)

Regards,

ScriptMan

[gesine_stanienda · Accepted solution]

Hi ScriptMan,

thanks. This does half the job 🐵

The problem is not to compare the cells, but the fact that I need to get rid of all the cell contents that cannot be compared because they are different.

Example:

Column 1 Column 2

Öffnen Sie Transaktion RGU_23_SV Use transacion RGU_23_SV.

I need to get rid of everything except "RGU_23_SV". Otherwise, I will always get the result that the two cells are not identical (of course).

So, what I need is a script that does the following:

a) Eliminate ALL cell content that does not fulfill these criteria:

1) Is all in CAPS

2) May contain numbers

3) May contain underscores

b) Compare the cells after "a" has been done.

Cheers Gesine

[script_man · Accepted solution]

Hi Gesine,

To ensure that your job is more than halfway done, I present to you at Christmas following VBA code.

Sub Makro1()
  Dim Par(1 To 2) As String
   
  For i = 2 To ActiveCell.SpecialCells(xlLastCell).Row
   For m = 1 To 2
   Par(m) = Cells(i, m)
   Cells(i, m + 2) = ""
   TA = ""
    For j = 1 To Len(Par(m))
      If Right(Left(Par(m), j), 1) = UCase(Right(Left(Par(m), j), 1)) And Asc(Right(Left(Par(m), j), 1)) >= 65 Then
         k = j
         j = j + 1
         If Right(Left(Par(m), j), 1) = UCase(Right(Left(Par(m), j), 1)) Then
            Do
             If Right(Left(Par(m), j), 1) = " " Or j >= Len(Par(m)) Then Exit Do
             j = j + 1
            Loop
            l = j
            TA = Right(Left(Par(m), l), l - k + 1)
         Else
            Do
             If Right(Left(Par(m), j), 1) = " " Or j >= Len(Par(m)) Then Exit Do
             j = j + 1
            Loop
         End If
      Else
         Do
          If Right(Left(Par(m), j), 1) = " " Or j >= Len(Par(m)) Then Exit Do
          j = j + 1
         Loop
      End If
    Next j
    Cells(i, m + 2) = RTrim(TA)
   Next m
   If Cells(i, 3) = Cells(i, 4) Then
      Cells(i, 5) = 1
   Else
      Cells(i, 5) = ""
   End If
  Next i
End Sub

Suppose your source excel sheet is as follows:

Column 1		Column 2
Öffnen Sie Transaktion RGU_23_SV		Use transacion RGU_23_SV
Öffnen Sie Transaktion RGU_22_SV		Use transacion RGU_23_SV
Öffnen Sie Transaktion R.GU_23_SV Xyz	Use transacion R.GU_23_SV 1
Öffnen Sie Transaktion R/GU_23_SV		Use transacion R/GU_23_SV 1
Öffnen Sie Transaktion R-GU_23_SV		Use transacion R-GU_23_SV Abc
Öffnen Sie Transaktion R_GU_23_SV		Use transacion R_GU_23_SV

The result of the above program is as follows:

TA 1		TA 2	Compare
RGU_23_SV		RGU_23_SV	                     1
RGU_22_SV		RGU_23_SV	
R.GU_23_SV	R.GU_23_SV	1
R/GU_23_SV	R/GU_23_SV	1
R-GU_23_SV	R-GU_23_SV	1
R_GU_23_SV	R_GU_23_SV	1

I wish you a Merry Christmas and happy New Year.

ScriptMan

Edited by: ScriptMan on Dec 23, 2010 8:46 AM

[gesine_stanienda · Accepted solution]

Dear ScriptMan!

This is a great Christmas present for me and my team. It will enable us to mass-check translations and find and correct errors at unbelievable speed.

I would like to thank you!!!

Hope you'll have a good 2011 and I wish you all the best, keep up the good work.

Gesine