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]

Imagine this is column A with the rows...

1 no
2 no
3 no
4 no
5 yesB
6 no
7 yesC
8 no
9 no
10 yesA
11 yesA
12 yesC
13 no
14 yesB
15 no

If I'm searching for the first YesA that would be row 10.
If I'm searching for the first YesB that would be row 5.
If I'm searching for the first YesC that would be row 7.

Imagine the following is column B...
1 no
2 yesA
3 yesC
4 no
5 no
6 no
7 yesC
8 yesC
9 no
10 yesB
11 yesA
12 yesC
13 no
14 yesC
15 yesA

First yesA is row 2
First yesB is row 10
First yesC is row 3

So, if a group asks for the first YesA in column A, and the first YesC in column B, then I would be copying data from rows 10 and 3 of Sheet1. The data from those rows to be copied would be cells O10:Z10 and O3:Z3, respectively. That data would be pasted on Sheet2 in cells U2-AF2 and AH2-AS2.

Note: I only included 2 of the 12 columns in the above example to try to convey the concept.

 

[mlarson]

Thanks
You want the results for "YesA", "YesB" and "YesC" all in the one sheet2, suggesting that you are trying to find the first occurrence in each column of any of the three values: YesA or YesB or YesC (i.e. rather than three separate sets of searches for YesA, YesB and YesC individually)?

I'm trying the find the occurrence of a specific text in the cell. All cells will have either "no" "yesA" "yesB" or "yesC", but I'm only interested in finding the specific text. That specific text for a column changes based on which group we're working on. If I'm trying to find "yesA" then I don't care at all about "no" "yesB" or "yesC." If I'm trying to find "yesB" then I don't care about finding "no" "yesA" or "yesC."

I hope I'm making sense and things are becoming clearer.

 

[StephenCrump]

Ahh, yes. Clear now thanks.

I had assumed that the permutations listed in the 531,441 groups were in columns A-L of Sheet1. I hadn't realised you had other random Yes/No data in Sheet1, and presumably the permutation groups are listed in columns A-l of Sheet2.

Do the permutations need to be in any particular order? Do you have a formula to generate, or do you need one?

 

[mlarson]

Ahh, yes. Clear now thanks.

Excellent!

I had assumed that the permutations listed in the 531,441 groups were in columns A-L of Sheet1. I hadn't realised you had other random Yes/No data in Sheet1, and presumably the permutation groups are listed in columns A-l of Sheet2.

I should have been more clear. The permutations aren't listed anywhere, and I don't really need a list showing the different permutations, but what I need are the results of cells O:Z for each permutation group. With that said, perhaps it's a simpler task if the permutations are listed somewhere in Sheet1 or Sheet2?

Do the permutations need to be in any particular order? Do you have a formula to generate, or do you need one?

No particular order needed.

 

[StephenCrump]

Here's a file implementing the layout and code below: https://app.box.com/s/rxaorw1m2me9gp6t782trqcil1cvvkal

Dummy data:

Data1: =Sheet1!$A$2:$L$20
Data2: =Sheet1!$O$2:$Z$20

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Data1														Data2
2	No	No	No	YesA	No	No	No	No	No	No	No	No			O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
3	No	No	No	No	No	No	No	No	No	No	No	No			O2	P2	Q2	R2	S2	T2	U2	V2	W2	X2	Y2	Z2
4	No	No	No	No	No	No	No	No	No	YesA	No	No			O3	P3	Q3	R3	S3	T3	U3	V3	W3	X3	Y3	Z3
5	No	No	YesC	No	YesB	YesC	No	No	No	No	No	No			O4	P4	Q4	R4	S4	T4	U4	V4	W4	X4	Y4	Z4
6	No	No	No	No	No	No	No	No	No	No	No	No			O5	P5	Q5	R5	S5	T5	U5	V5	W5	X5	Y5	Z5
7	No	No	No	YesC	YesC	YesB	No	No	YesC	No	YesB	YesC			O6	P6	Q6	R6	S6	T6	U6	V6	W6	X6	Y6	Z6
8	YesA	No	YesA	No	No	No	No	No	No	No	No	No			O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7
9	No	No	No	No	No	No	No	No	No	YesC	YesC	YesB			O8	P8	Q8	R8	S8	T8	U8	V8	W8	X8	Y8	Z8
10	No	No	No	YesB	YesA	No	YesA	No	YesA	No	No	No			O9	P9	Q9	R9	S9	T9	U9	V9	W9	X9	Y9	Z9
11	No	YesB	No	No	No	No	No	No	No	No	No	No			O10	P10	Q10	R10	S10	T10	U10	V10	W10	X10	Y10	Z10
12	No	YesA	YesB	No	YesA	No	No	No	No	YesB	YesA	No			O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11
13	YesB	No	No	No	No	No	No	YesB	No	No	No	No			O12	P12	Q12	R12	S12	T12	U12	V12	W12	X12	Y12	Z12
14	No	No	No	No	No	YesA	No	YesA	YesB	No	YesA	No			O13	P13	Q13	R13	S13	T13	U13	V13	W13	X13	Y13	Z13
15	No	YesC	No	No	No	No	YesB	No	No	No	No	No			O14	P14	Q14	R14	S14	T14	U14	V14	W14	X14	Y14	Z14
16	No	No	No	No	No	No	No	No	No	No	No	No			O15	P15	Q15	R15	S15	T15	U15	V15	W15	X15	Y15	Z15
17	No	No	No	No	No	No	No	YesC	No	No	No	No			O16	P16	Q16	R16	S16	T16	U16	V16	W16	X16	Y16	Z16
18	YesC	No	No	No	No	No	No	No	No	No	No	No			O17	P17	Q17	R17	S17	T17	U17	V17	W17	X17	Y17	Z17
19	No	No	No	No	No	No	No	No	No	No	No	YesA			O18	P18	Q18	R18	S18	T18	U18	V18	W18	X18	Y18	Z18
20	No	No	No	No	No	No	YesC	No	No	No	No	No			O19	P19	Q19	R19	S19	T19	U19	V19	W19	X19	Y19	Z19
Sheet1

Sheet2 Results (cut down for illustration purposes)

MyList: =Sheet2!$B$1:$B$3
StartHere: =Sheet2!$B$12
Firsts: =Sheet2!$B$6:$M$8
Helper Cells B6 etc: =IFERROR(MATCH($A6,INDEX(Data1,,COLUMN()-COLUMN($B6)+1),),0)

 

[StephenCrump]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS
1	MyList	YesA
2		YesB
3		YesC
4
5	Firsts
6	YesA	7	11	7	1	9	13	9	13	9	3	11	18
7	YesB	12	10	11	9	4	6	14	12	13	11	6	8
8	YesC	17	14	4	6	6	4	19	16	6	8	8	6
9
10		Permutations
11		(1)	(2)	(3)	(4)	(5)	(6)	(7)	(8)	(9)	(10)	(11)	(12)
12		1	1	1	1	1	1	1	1	1	1	1	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
13		1	1	1	1	1	1	1	1	1	1	1	2								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
14		1	1	1	1	1	1	1	1	1	1	1	3								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
15		1	1	1	1	1	1	1	1	1	1	2	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
16		1	1	1	1	1	1	1	1	1	1	2	2								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
17		1	1	1	1	1	1	1	1	1	1	2	3								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
18		1	1	1	1	1	1	1	1	1	1	3	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
19		1	1	1	1	1	1	1	1	1	1	3	2								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
20		1	1	1	1	1	1	1	1	1	1	3	3								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
21		1	1	1	1	1	1	1	1	1	2	1	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
22		. etc
23		1	1	1	1	1	2	1	3	2	2	2	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
24		1	1	1	1	1	2	1	3	2	2	2	2								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
25		1	1	1	1	1	2	1	3	2	2	2	3								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
26		. etc
27		1	1	2	1	1	3	1	1	2	2	2	3								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O1	P1	Q1	R1	S1	T1	U1	V1	W1	X1	Y1	Z1
28		. etc
29		1	1	3	2	2	3	2	3	1	3	2	1								O7	P7	Q7	R7	S7	T7	U7	V7	W7	X7	Y7	Z7		O11	P11	Q11	R11	S11	T11	U11	V11	W11	X11	Y11	Z11		O4	P4	Q4	R4	S4	T4	U4	V4	W4	X4	Y4	Z4		O9	P9	Q9	R9	S9	T9	U9	V9	W9	X9	Y9	Z9
Sheet2

 

[StephenCrump]

I have shown the permuations as numeric 1,2,3 rather than string YesA, YesB and YesC to cut down memory usage.

Sub Test()

    Dim vData As Variant, vOut As Variant, vFirsts As Variant, vList As Variant
    Dim lPermutations() As Long
    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
    N = UBound(vList)
    lStartRow = Range("StartHere").Row
    lNoRows = N ^ NO_COLS
    ReDim lPermutations(1 To lNoRows, 1 To NO_COLS)
    Application.ScreenUpdating = False
    Range(Range("StartHere"), Range("StartHere").End(xlDown)).EntireRow.ClearContents
    
    For i = 1 To NO_COLS
        ReDim vOut(1 To lNoRows, 1 To NO_COLS)
        For j = 1 To lNoRows
            lPermutationNo = Int((j - 1) / N ^ (NO_COLS - i)) Mod N + 1
            lPermutations(j, i) = lPermutationNo
            lRowNo = vFirsts(lPermutationNo, 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
    
    Range("StartHere").Resize(lNoRows, NO_COLS).Value = lPermutations
    Application.ScreenUpdating = True

End Sub

 

[mlarson]

Thanks for that! Sheet one has data in about 600 rows. Do I need to expand the range in the code or on the sheet somehow for Data1 and Data2? For example, right now the Dummy data is
Data1: =Sheet1!$A$2:$L$20
Data2: =Sheet1!$O$2:$Z$20

 

[StephenCrump]

Yes, probably easiest if you just adjust the range names in Excel, changing 20 to the last row of your data.

Data1: =Sheet1!$A$2:$L$20
Data2: =Sheet1!$O$2:$Z$20

The code automatically picks up the entire Data2 range as it is defined in Excel.

 

[mlarson]

Holy canoli, I think it works! I will run some more tests tomorrow morning and keep you posted. Thank you!