Finding a group of cells, copying data from those rows onto another sheet...

[mlarson]

Hello, I would like to find a code for the following. Thanks in advance for any help you can provide!

-Find the first “YesA,” “YesB,” or “YesC” in a given column on “Sheet1.”

-I would like to find one of these firsts in column A, one first in column B, etc. all the way to column L (so 12 columns).

-Permutations/combinations for all 3 possibilities (“YesA,” “YesB,” or “YesC”) across 12 columns (A-L). So, 3^12 = 531,441 groups. See example below.

-For group 1, when a first is found in column A of “Sheet1” I’d like to copy the data in cells O-Z from that same row to “Sheet2” cells H2-S2. When a first is found in column B copy data in cells O-Z to “Sheet2” cells U2-AF2. And a first in column C copy data from O-Z to “Sheet2” cells AH2-AS2… same idea through column L.

-For group 2, when a first is found in column A of “Sheet1” I’d like to copy the data in cells O-Z from that same row to “Sheet2” cells H3-S3. When a first is found in column B copy data in cells O-Z to “Sheet2” cells U3-AF3. And a first in column C copy data from O-Z to “Sheet2” cells AH3-AS3… same idea through column L.

-Group 3 data will go in row 4 of “Sheet2,” group 4 data will go in row 5 of “Sheet2,” etc. all the way through Group 531,441.

-Results will show 531,441 rows of data on “Sheet2.”

Examples of permutations/combinations for each group…
ColA ColB ColC ColD ColE ColF ColG ColH ColI COlJ ColK ColL
Group 1: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA
Group 2: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesB
Group 3: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesC
Group 4: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesB YesA
Group 5: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesA YesC YesA
Group 6: YesA YesA YesA YesA YesA YesA YesA YesA YesA YesB YesA YesA
…
Grp 531,441: YesC YesC YesC YesC YesC YesC YesC YesC YesC YesC YesC YesC


Thank you for your help!

 

[mlarson]

I ran some more tests this morning and your code works very well! Thank you so much!

One follow-up question. If at some point I wanted to add a 4th "yes" (let's call it "yesD"), would I simply add YesD under MyList and expand the range for MyList? I would also add the additional permutations to include 4 under Permutations (but I can copy and paste those). Note: I'm aware the number of permutations would eventually exceed the allotted 1,048,576 rows but just want to understand the concept here.

Thanks!

 

[Sudhirdeshmukh40]

Hello Friends,
Here I am New
I want to invoice wise add and less other sheet (stock sheet) more and more data
i want VBA formula Please give me....

 

[mlarson]

Hello Friends,
Here I am New
I want to invoice wise add and less other sheet (stock sheet) more and more data
i want VBA formula Please give me....

Sudhirdeshmukh40, you should start a new thread instead of posting on an existing thread.

 

[mlarson]

deleted

 

[mlarson]

StephenCrump, is it possible to change the data under "Permutations"? The combinations at the link below number 73,920 instead of 531,000+. If possible, I'd like to use the combinations in the link below in your previous code as it will be less work for the Excel program to run. Thank you!

https://www.dropbox.com/s/pwhi9cr8tb5m2xn/MrExcel StephenCrump.xlsx?dl=0

 

[StephenCrump]

One follow-up question. If at some point I wanted to add a 4th "yes" (let's call it "yesD"), would I simply add YesD under MyList and expand the range for MyList? I would also add the additional permutations to include 4 under Permutations (but I can copy and paste those). Note: I'm aware the number of permutations would eventually exceed the allotted 1,048,576 rows but just want to understand the concept here.

Give it a try! A great way to test your understanding of someone else's code is to start adapting it.

You'll also need to expand the helper formulae in range "Firsts".

The number of permutations calculated is driven by this line:

lNoRows = N ^ NO_COLS

You may want to set this to some arbitrary number, 100 or 1,000 say, for testing purposes.

 

[StephenCrump]

is it possible to change the data under "Permutations"? The combinations at the link below number 73,920 instead of 531,000+

If you copy these in to Sheet2!B13, i.e. range StartHere, and define the range name "MyPermutations", then this amended code should work:

Sub Test()

    Dim vData As Variant, vOut As Variant, vFirsts As Variant, vList As Variant, vPermutations As Variant
    Dim lStartRow As Long, lNoRows As Long, lPermutationNo As Long, lRowNo As Long, N As Long, i As Long, j As Long, k As Long
    Const NO_COLS = 12
    
    vData = Range("Data2").Value2
    vFirsts = Range("Firsts").Value2
    vList = Range("MyList").Value2
    vPermutations = Range("MyPermutations").Value2
    N = UBound(vList)
    lStartRow = Range("StartHere").Row
    lNoRows = UBound(vPermutations)
    
    Application.ScreenUpdating = False
        
    For i = 1 To NO_COLS
        ReDim vOut(1 To lNoRows, 1 To NO_COLS)
        For j = 1 To lNoRows
            lRowNo = vFirsts(vPermutations(j, i), i)
            For k = 1 To NO_COLS
                If lRowNo Then
                    vOut(j, k) = vData(lRowNo, k)
                Else
                    vOut(j, k) = "N/A"
                End If
            Next k
        Next j
        Worksheets("Sheet2").Range("U" & lStartRow).Offset(, (NO_COLS + 1) * (i - 1)).Resize(lNoRows, NO_COLS).Value = vOut
    Next i
    
    Application.ScreenUpdating = True

End Sub

If you're going to work with a relatively small number of permutations, it may be possible to simplify the coding approach, i.e. to fill the output ranges with a relatively simple Index/Match formula, and then convert to values.

I actually started coding it this way but it wasn't practical with millions of cells, hence I took all the calculations into VBA.

 

[mlarson]

If you copy these in to Sheet2!B13, i.e. range StartHere, and define the range name "MyPermutations", then this amended code should work:

I'm not entirely sure what I'm doing wrong. I spent the past 90 minutes implementing this, trying and retrying, and the original 531,000 rows seems to override the changes. Perhaps your suggestion below would be best since I can use 73,920 instead? Do you mind posting the Index/Match formula I should use?

If you're going to work with a relatively small number of permutations, it may be possible to simplify the coding approach, i.e. to fill the output ranges with a relatively simple Index/Match formula, and then convert to values.

I actually started coding it this way but it wasn't practical with millions of cells, hence I took all the calculations into VBA.

 

[StephenCrump]

Here's the first method. To keep the file small, I've included just a couple of hundred lines of your permutations. Just expand the range name "MyPermutations" to the required number of rows.

https://app.box.com/s/girjlm9ylyudbipe02stosn4sdn9x00d

I'll look at the Index/Match approach a bit later.

 

[StephenCrump]

Just to illustrate the Excel formula approach, here's some alternative code which will work on the file attached to the previous Post.

Sub Test()

    Dim lStartRow As Long, lNoRows As Long, i As Long, j As Long
    Const NO_COLS = 12
    
    lStartRow = Range("StartHere").Row
    Application.ScreenUpdating = False
    
    With Worksheets("Sheet2")
        lNoRows = .Cells(Rows.Count, Range("StartHere").Column).End(xlUp).Row - lStartRow + 1
        For i = 1 To NO_COLS
            For j = 1 To NO_COLS
                With .Range("U" & lStartRow).Resize(lNoRows).Offset(, (i - 1) * (NO_COLS + 1) + j - 1)
                    .Formula = "=INDEX(Data2,INDEX(Firsts,INDEX(" & Range("StartHere").Resize(, NO_COLS).Address(0, 1) & "," & i & ")," & i & "),COLUMNS($U1:U1))"
                    .Value = .Value
                End With
            Next j
        Next i
    End With
    
    Application.ScreenUpdating = True

End Sub

It's still too computationally heavy to be used even on a reduced number of 70,000 permutations. If I was going to pursue an Excel formula approach I'd look at ways of simplifying this, e.g. to avoid the use of Index() three times, and perhaps also look at looping through 12x12 columns individually, rather than 12 blocks of 12 columns.