Copying Cells From One Sheet To Another Based On Certain Criteria

[mlarson]

Hi there, I hope you can help me with this question. I'm not sure how to post an Excel spreadsheet but it may not be needed for this question anyway. Thanks in advance for your help!

The spreadsheet would show Column A cells with either the word "yes" or "no." Same with Column B, C, D...M. Each column goes down 350 rows.

Here are the actions I'd like to be able to accomplish...

First Group:
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B1-T1.
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B2-T2.
Repeat for columns C, D, E...M (first "yes" in each column, so Column M results will go to B13-T13).

Second Group:
On "Sheet1", find the second "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the second "yes" appears.
Paste those cells on "Sheet2" in B14-T14.
On "Sheet1", find the first "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the first "yes" appears.
Paste those cells on "Sheet2" in B26-T26.
Repeat for columns C, D, E...M with the third "yes" for each.

Third Group:
On "Sheet1", find the fourth "yes" that appears (going from top to bottom) in Column A.
Copy cells from columns P-AH on that same row the fourth "yes" appears.
Paste those cells on "Sheet2" in B27-T27.
On "Sheet1", find the fifth "yes" that appears (going from top to bottom) in Column B.
Copy cells from columns P-AH on that same row the fifth "yes" appears.
Paste those cells on "Sheet2" in B39-T39.
Repeat for columns C, D, E...M with the second "yes" for each.

In total there will be 39 rows of data entered on "Sheet2."

Thanks again!

 

[mlarson]

Will do later today or tomorrow as I want to make sure I word it correctly. My apologies as I'm not very experienced with this stuff! Thank you again!

 

[mlarson]

Hi Fluffy, just to follow-up, I posted a much more detailed question at the following link if you're so inclined to take a look: https://www.mrexcel.com/forum/excel...ts-large-number-combinations.html#post5002524

Many thanks!

 

[mlarson]

Hi Fluff,

On second thought, I think your original answer will work perfectly if I can condense the number of rows of data on the output sheet ("Sheet2").

Do you mind taking a minute to have the original output location of Group 1, for example, as B1-T1, B2-T2...B13-T13 to be changed to B2-T2, V2-AN2, AP2-BH2, etc? That would condense the number of rows greatly.

Hope I'm not being a pain. Thanks.

 

[Fluff]

How about

Sub copyData()

   Dim Cl As Range
   Dim Col As Long
   Dim Cnt As Long
   Dim Rw As Long
   Dim Ary As Variant
   Dim Clm As Long
   
   With Sheets("MA (2)")
      Rw = 2
      For Col = 1 To 13
         Cnt = 0
         Clm = 2
         If Col = 1 Then
            Ary = Array(1, 2, 4)
         ElseIf Col = 2 Then
            Ary = Array(1, 1, 5)
         Else
            Ary = Array(1, 3, 2)
         End If
         .Columns(Col).AutoFilter 1, "Yes"
         For Each Cl In .Range(.Cells(2, Col), .Cells(Rows.Count, Col).End(xlUp)).SpecialCells(xlVisible)
            Cnt = Cnt + 1
            If Cnt = Ary(0) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Clm = Clm + 20
            End If
            If Cnt = Ary(1) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Clm = Clm + 20
            End If
            If Cnt = Ary(2) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Clm = Clm + 20
            End If
            If Cnt = WorksheetFunction.Max(Ary) Then Exit For
         Next Cl
         Rw = Rw + 1
         .AutoFilterMode = False
      Next Col
   End With
   
End Sub

 

[mlarson]

Hi Fluff, that does look better but I was hoping to have each group's output on the same row.

The latest code produces groups starting on the same row (2) and different columns but going down to row 14.

What I'd like is each group's data to all be contained on the same row... so one row per group.

Thanks.

 

[Fluff]

How about

Sub copyData()

   Dim Cl As Range
   Dim Col As Long
   Dim Cnt As Long
   Dim Rw As Long
   Dim Ary As Variant
   Dim Clm As Long
   
   With Sheets("Active")
         Clm = 2
      For Col = 1 To 13
         Rw = 2
         Cnt = 0
         If Col = 1 Then
            Ary = Array(1, 2, 4)
         ElseIf Col = 2 Then
            Ary = Array(1, 1, 5)
         Else
            Ary = Array(1, 3, 2)
         End If
         .Columns(Col).AutoFilter 1, "Yes"
         For Each Cl In .Range(.Cells(2, Col), .Cells(Rows.Count, Col).End(xlUp)).SpecialCells(xlVisible)
            Cnt = Cnt + 1
            If Cnt = Ary(0) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Rw = Rw + 1
            End If
            If Cnt = Ary(1) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Rw = Rw + 1
            End If
            If Cnt = Ary(2) Then
               .Range("P" & Cl.Row).Resize(, 19).Copy Sheets("Sheet2").Cells(Rw, Clm)
               Rw = Rw + 1
            End If
            If Cnt = WorksheetFunction.Max(Ary) Then Exit For
         Next Cl
         Clm = Clm + 20
         .AutoFilterMode = False
      Next Col
   End With
   
End Sub

 

[mlarson]

The first group/array output is still showing in multiple rows (2, 3, etc.) on Sheet2… can we have all the data for the first group (first array) show in row 2 (part 1 of array 1 in B2-T2, part 2 of array 1 in V2-AN2, etc.)? Thank you.

 

[Fluff]

In your op you described group 1 as being the 1st yes in each column. The code in post#16 is now putting that "group" in row 2, with the 2nd group in row3, etc.
If that is not what you want, then you will need to explain exactly what your requirements are, in the same manner as you did in your op.

 

[mlarson]

OK. Do the first, first, first (group 1), second, first, third (group 2), and fourth, fifth, second (group 3) of my original post correspond with the array section of the code below? Or am I looking in the wrong place? Thanks.

How about

Sub copyData()
         If Col = 1 Then
            Ary = Array(1, 2, 4)
         ElseIf Col = 2 Then
            Ary = Array(1, 1, 5)
         Else
            Ary = Array(1, 3, 2)
         End If

 

[Fluff]

Yes they do.
so what you should get is the 1st yes in Cols A:M in row 2
The the 2nd yes in col A the 1st in col B & the 3rd in cols C:M in row 3
Lastly the 4th yes in col A, 5th in col B & 2nd in cols C:M in row 4.
What are you getting? Or is that not what you want?