Consistency check of data

Question

I have data stored in three columns of Excel.

Column A: Product no, 
Column B: Production site 
Column C: Sales code

I need to check the consistency of the first 6 digits in the Sales Code for each product no.

So for example, for all products with product no. 1, I need to check if the first 6 digits in the sales codes are equal. If all sales codes for product no. 1 are equal, the program must write Y for Yes in Column D. If the sales codes are different, the program must write N for No in Column D.

Product;Site;Sales code

1;A;86451001    
1;B;864510.3    
1;C;86451004    
1;D;86451001    
1;E;864510.3    
1;F;86451004    
1;G;86451001    
1;H;864510.3    
1;I;86451004    
1;J;86451001    
1;K;874507.3    
1;L;87450704    
1;M;87450701    
1;N;885656.3    
1;O;88565604    
2;A;86451001    
2;B;864510.3    
2;C;86451004    
2;D;86451001    
2;E;864510.3    
2;F;88565604    
2;G;88565601    
2;H;864510.3    
2;I;86451004    
2;J;86451001    
2;K;874507.3    
2;L;87450704    
2;M;87450701    
2;N;885656.3    
3;A;88565604    
3;B;86451001    
3;C;864510.3    
3;D;86451004    
3;E;87450704

I need this check of the consistency because my data set is huge. I’m a beginner with VBA, so I have no clue of how to do this.

Do you have any tips?

Does the Y or N go in every row, the first row with the 1, or the last? If each row will need its own Y/N Value, do you want us to assume the first entry is always valid? or the most common value? Could you add an expected output and how you'd like it to look and explain why the values are as they? — user2140261, Jun 04 '13 at 20:32

score 1 · Answer 1 · answered Jun 05 '13 at 10:55

1

We will need a helper column, D1=Product_SaleCode6

D2=A2&"_"&LEFT(C2,6)

Then, column E will be your test column, E1=Test

E2=IF(COUNTIF($A$2:$A$35,A2)=COUNTIF($D$2:$D$35,D2),"Y","N")

Fill in above D2, E2 formulas for all rows.

What I am trying to do is, check if count of products is same as count of 6 digits of sales code for that product group.

answered Jun 05 '13 at 10:55

zx8754

52,746
12
114
209

Good try but ;) ... it will fail with a simple input like "1\t123456\n2\t123456\n" because if another group has the same codes the count won't match even if the group is good; in the same manner if a group is incorrect but another has the complement for the count it will believe it's OK. If the OP has strong hypotheses on the input this is worth a try but otherwise it's risky ;) Moreover if you want a fully automated solution you won't cut it: you need some VBA plumbing :) – Pragmateek Jun 05 '13 at 15:28
Did you test my formula? Just tried with your example and it works. – zx8754 Jun 05 '13 at 17:23
Yes I've tested it, here is a Google docs that shows the issue: https://docs.google.com/spreadsheet/ccc?key=0AhpapcT0riBSdHFsdXljM3JBRG84Sk03QU41YlJaN0E&usp=sharing The first countif will be 1 for each row but the second will be 2 so each row will be considered invalid whereas all is fine and should display "Y". Maybe I'm missing something... :) – Pragmateek Jun 05 '13 at 19:32
I am comparing column A countifs(how many in each Product) with helper column D countifs. Helper column `D2=A2&"_"&LEFT(C2,6)` means "Take Product name add first 6 characters of SalesCode". In your googledocs you are comparing column A countifs with column B countifs, presumably column B is first 6 characters of SalesCode. Please copy&paste my formula and it should work. – zx8754 Jun 05 '13 at 21:09

score 0 · Answer 2 · answered Jun 04 '13 at 22:50

Here is what you could do:

scan the rows one by one
when you identify a new group store its product number and sales short code (the first 6 characters) along with the range it starts at
check each subsequent row of the group to see if the code are consistent
if not mark the group as corrupted and continue
at the end of the group look back to the first row of the group and write the group flag, "Y" or "N", for each row of the group
after marking the rows check if the current row is empty, it yes stop scanning
otherwise reset the values for the next group and continue scanning

And here is a quick and not too dirty implementation (tested with your small data set but of course do your due diligence before using it ;)) :

Sub check()
Dim sh As Worksheet
Set sh = Sheets(1)

Dim r As Range
Set r = sh.Range("A1")

Dim currentProduct As Integer
Dim currentProductSalesCode As String
Dim currentProductStart As Range
Dim ok As Boolean
currentProduct = -1
Do
    ' Are we changing of product group?
    If r.Value2 <> currentProduct Then
        ' Check that this is not the beginning
        If currentProduct <> -1 Then
            Dim i As Integer
            i = 0
            ' Apply the flag to all the rows in the current group
            Do
                If currentProductStart.Offset(i, 0) <> currentProduct Then
                    Exit Do
                End If

                Dim flagOutput As Range
                Set flagOutput = currentProductStart.Offset(i, 3)

                If ok Then
                    flagOutput = "Y"
                Else
                    flagOutput = "N"
                End If
                i = i + 1
            Loop

            If IsEmpty(r) Then
                Exit Do
            End If
        End If
        'Reset the values for the current group
        currentProduct = r.Value2
        currentProductSalesCode = Left(r.Offset(0, 2).Text, 6)
        Set currentProductStart = r
        ok = True
    Else
        ' If the current row code is not equal to the first row of the group code
        If Left(r.Offset(0, 2).Text, 6) <> currentProductSalesCode Then
            ok = False
        End If
    End If
    Set r = r.Offset(1, 0)
Loop
End Sub

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

0

This is a very suitable application for PivotTables. For example, adding a column ("Sales ") with a formula =LEFT(C2,6) and a pivot table layout as below immediately identifies that in your sample Y would apply to all (count is 1 in every case) (assuming sites may vary):

SO16926030 example

Other validations could be made with different formulae, to suit, in different columns.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 04 '13 at 22:51

pnuts

58,317
11
87
139

Consistency check of data

3 Answers3