Comparing ranges

[Blackstone_1900]

Hi,


I am trying to compare two sets of data, and altering the data in one
sheet when I get a "hit".
The process I want to do is as following:


1. In Sheet1 I want to detect all the rows in a specified range where
column K is different from empty and store the values in column a, b and c for every row where column K is different from empty

2. I have the following table of data that I need to multiply with the
data in point 1
bb=XX dd=1
bb=YY dd=0.5
bb=ZZ dd=10

3. I need to create a new dataset where for every row I have discovered
in point 1 I create a new row using the table from point 2.
Meaning it will be on this form:
a b bb e( e=c*dd)

The example in point 2 would look something like this:

a1 b1 XX e (c*1)
a1 b1 YY e (c*0.5)
a1 b1 ZZ e (c*10)
a2 b2 XX e (c*1)
.........

4. Now I need to take the resulting dataset from 3 and compare it with
sheet 2.
If sheet2(column a) = A and Sheet2(column g) = bb then let
sheet2(column z) = e

I realize I can probably do this using for, and next loops I do suspect
that this may be an unnecessary complicated and not to
mention slow running way of doing this, any of you have any
suggestions?

Regards,

John

[Hack]

Moved to Office Development

[Webtest]

John:

1. In Sheet1 I want to detect all the rows in a specified range where column K is different from empty and store the values in column a, b and c for every row where column K is different from empty

I can't quite follow this ... which row is the source data for columns A, B, and C? You want these 3 values replicated in every row where Column K is not empty?

[Blackstone_1900]

Sorry, I will try to clarify what I meant:

In Sheet1 I have a range let's say a1 to z300. Some rows will have a value in column K, and some will not.

I want to find the rows that has a value in column K. For these rows I want to take the values in column a,b and c (for those specific rows) and store them in memory for later use.

The values I have listed in point 2 are variables (I know probably should have used regular names instead of XX YY etc.)

With the example I have put up in point 2 with three different bb variable vaules, I would take those rows I found in sheet 1 and make one record for
XX, one for YY and one for ZZ, the only difference being the value I multiply the value from column C with. Note however that Every row I find in sheet 1 would be made into three records as above, thus if I find three rows in Sheet 1, I would end up with nine records.

These nine records would then be compared with the values in Sheet 2 as mentioned below (I pasted the original description)

4. Now I need to take the resulting dataset from above and compare it with
sheet 2.
If sheet2(column a) = A and Sheet2(column g) = bb then let
sheet2(column z) = e

I hope this clarifies what I meant, I am familiar with FOR and NEXT loops but I am already suspecting that those may result in a very slow working macro (these sheets may potentially contain thousands of rows, and unfortunately I need to have this done in Excel).

But please ask if I still haven't made myself understood

[DKenny]

As a start, here's some code that will populate all your variables from Cols A-C into an array. I'm still confused what you want to do with them - but this should satisfy your requirement to

o take the values in column a,b and c (for those specific rows) and store them in memory for later use.

Sub BlackStone()
Dim vaValues() As Variant
Dim rngData As Range
Dim lRowNum As Long
Dim lVarNum As Long
 
    'Initialize the array
    ReDim vaValues(0)
    
    'Data Table reference
    Set rngData = ThisWorkbook.Worksheets(1).UsedRange
        
    'Loop through the rows
    For lRowNum = 2 To rngData.Rows.Count
        With rngData
            'Looking for values in Col K
            If .Cells(lRowNum, 11).Value <> "" Then
                'Add cols A to C to the array
                For lVarNum = 1 To 3
                    'Add the value to the last record in the array
                    vaValues(UBound(vaValues)) = .Cells(lRowNum, lVarNum).Value
                    'Increase the size of the array
                    ReDim Preserve vaValues(UBound(vaValues) + 1)
                Next lVarNum
            End If
        End With
    Next lRowNum
    
    'Remove empty element for array
    ReDim Preserve vaValues(UBound(vaValues) - 1)
End Sub

[Webtest]

Does the position of the rows on Sheet 2 correspond exactly to the rows on Sheet 1?

Excel is pretty fast if don't use "selection" and you make sure that you turn off the display:

Application.ScreenUpdating = False

And then turn it back on (= True) after the end of the processing.

[Blackstone_1900]

DKenny: Thanks for replying and for the code, that does indeed look very useful for the first part of my question.

Now what I wanted to do for part 2:

I have a small table in Sheet 3 of indentifiers and numbers I have pre-calculated elsewhere, lets say it is listed in Sheet3 range a1 to b4

Now, in sheet2 I have several rows of data, column B in this sheet contains data that matches one of the column A values in Sheet 3.

Basically what I want to do then, is to loop though Sheet 2, and for every row check whether the column B value you populated into the array in step 1, matches column A in sheet 2.

If it does I want to check Sheet 3 column A to find what row matches the value in Sheet2 Column B, I then want to take the corresponding column B value from Sheet 3, and multiply this with the value from column C that you populated into the array in step 1.

I then want to paste the result of this calculation into column Z in Sheet 2 (still at the same row that you found a match), and also taking the column A value that was populated into the array in step 1 and placing this in Sheet 2 column F.

I will try to visualize it for you (have placed the values I lookup in bold):

Sheet 1

ColumnA Column B COlumn C Column K ......

GenericName1 GenIdent1 1000 1
GenericName2 GenIdent2 10000
GenericName3 GenIdent3 500 100

Sheet 3

ColumnA Column B

Ident1 1
Ident2 10

Sheet2

ColumnA ColumnB ...... Column F........Column Z

GenIdent10 Ident1 GenericName2 0
GenIdent3 Ident2 GenericName3 5000 (500*10)
GenIdent5 Ident2 GenericName1 0

I don't know if this outline makes the array obsolete, but I had thought of using an array based upon what seemed easiest to me, but I am more than open for other suggestions. The purpose of this routine is basically to update multiple posts with changes I have done in Sheet1, but still make some individual adjustment (as in sheet 3).

Thanks for all the help you have already given me.