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!

 

[StephenCrump]

Why copy 500,000 rows of data to another worksheet, and spread it across multiple columns? You can identify the data subsets in the original worksheet, perhaps something like:

M3: =IFERROR(CHAR(64+MATCH(M$1,$A3:$L3,)),"")
B14: =SUMIF($M$3:$M$10,$A14,P$3:P$10)
F14: =SUMIF($N$3:$N$10,$A14,P$3:P$10)
J14: =SUMIF($O$3:$O$10,$A14,P$3:P$10)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1													YesA	YesB	YesC
2													Column?	Column?	Column?	Value 1	Value 2	Value 3	etc .
3	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	A			1	7	20
4	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesB	A	L		7	3	8
5	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesC	A		L	6	16	3
6	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesB	YesA	A	K		8	3	7
7	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesC	YesA	A		K	4	2	3
8	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	YesA	A			7	10	16
9
10	YesC	YesC	YesC	YesC	YesC	YesC	YesC	YesC	YesC	YesC	YesC	YesC			A	19	13	4
11
12	Totals:	YesA				YesB				YesC
13		Value 1	Value 2	Value 3		Value 1	Value 2	Value 3		Value 1	Value 2	Value 3
14	A	33	41	57						19	13	4
15	B
16	C
17	D
18	E
19	F
20	G
21	H
22	I
23	J
24	K					8	3	7		4	2	3
25	L					7	3	8		6	16	3
1

 

[mlarson]

Why copy 500,000 rows of data to another worksheet, and spread it across multiple columns?

Because I'm interested in the data found in cells O-Z on "Sheet1." I'm not interested in the count of how many times YesA, YesB, or YesC appears.

 

[StephenCrump]

My cut-down example shows data in columns P to R (rather than your O to Z).

The results shown are sums, e.g. B14 is the sum of the values in column P where YesA appears first in Column A. But this can be generalised to summarise the data any way you like.

You can also filter on columns M, N or O to look at the underlying raw data.

Hence my question about the need to create a copy of the data ...

But I think the code below does what you're looking for:

Sub Test()

    Dim vIn As Variant, vOut As Variant
    Dim sToFind As String
    Dim lCol() As Long, i As Long, j As Long, k As Long
    Const YES_COLS = 12
    Const DATA_COLS = 12
    Const START_COL = 15
    
    sToFind = "YesB"    'For example
    vIn = Worksheets("Sheet1").Range("A2:Z531441").Value2
    ReDim lCol(1 To UBound(vIn))
    
    For i = 1 To UBound(vIn)
        For j = 1 To YES_COLS
            If vIn(i, j) = sToFind Then
                lCol(i) = j
                Exit For
            End If
        Next j
    Next i

    For i = 1 To YES_COLS
        ReDim vOut(1 To UBound(vIn), 1 To DATA_COLS)
        For j = 1 To UBound(vIn)
            If lCol(j) = i Then
                For k = 1 To DATA_COLS
                    vOut(j, k) = vIn(j, START_COL + k - 1)
                Next k
            End If
        Next j
        Worksheets("Sheet2").Range("H2").Offset(, 13 * (i - 1)).Resize(UBound(vIn), DATA_COLS).Value = vOut
    Next i

End Sub

 

[mlarson]

Stephen, thank you for that code. Do you mind adding the "YesA" and "YesC" components as well? I'm a novice and would probably mess up the code trying to add them in there.

 

[StephenCrump]

The quick (but admittedly not very elegant) way would be to run the code three times. On each run, you'll need to change the code in just two places, as indicated:

sToFind = "[COLOR=#ff0000][B]YesB[/B][/COLOR]"    'YesA, YesB or YesC
    
'....

Worksheets("[COLOR=#ff0000][B]Sheet2[/B][/COLOR]").Range("H2").Offset(, 13 * (i - 1)).Resize(UBound(vIn), DATA_COLS).Value = vOut 'the sheet where you want the output

 

[mlarson]

I'd like all the output on "Sheet2" with each group (12 results of some combination of YesA, YesB, and/or YesC) making up one row each. Is there a way to have that happen in the code?

 

[StephenCrump]

I'd like all the output on "Sheet2" with each group (12 results of some combination of YesA, YesB, and/or YesC) making up one row each. Is there a way to have that happen in the code?

I wan't sure whether I had interpreted your original post correctly. Now I'm certain! ... that I haven't understood what you're after.

For each of the seven groups (i.e. 1 to 6, and 531,441) you listed in Post #1 , the data from Sheet1!O:Z should be copied how many times, and to where in Sheet2?

 

[mlarson]

Thanks for your reply. Here are the answers to your questions…

For each of the seven groups (i.e. 1 to 6, and 531,441)

I should have been more clear. I am looking for 531,441 groups, not just 7. The 7 shown were to convey the concept of what I'm trying to do. I'm looking for groups 1 through 531,441.

the data from Sheet1!O:Z should be copied how many times

Data should be copied 12 times for each group. So, 531,441 x 12 = 6,377,292 times the data is copied. Remember, it’s not the same data being copied. It’s data from the same columns, but not the same rows. The row is determined by wherever the first desired cell (yesA, yesB, or yesC) is located within a given column.

, and to where in Sheet2?

Group 1 column O:Z data from Sheet1 copied 12 times.
Group 1 output goes to sheet2 in cells U2-AF2, AH2-AS2, AU2-BF2, BH2-BS2, BU2-CF2, CH2-CS2, CU2-DF2, DH2-DS2, DU2-EF2, EH2-ES2, EU2-FF2, FH2-FS2.

Group 2 column O:Z data from Sheet1 copied 12 times.
Group 2 output goes to sheet2 in cells U3-AF3, AH3-AS3, AU3-BF3, BH3-BS3, BU3-CF3, CH3-CS3, CU3-DF3, DH3-DS3, DU3-EF3, EH3-ES3, EU3-FF3, FH3-FS3.

Same concept with Group 3, 4, 5, 6, 7, 8, 9, 10, all the way through Group 531,441.

Group 531441 column O:Z data from Sheet1 copied 12 times.
Group 531441 output goes to sheet2 in cells U531442-AF531442, AH531442-AS531442, AU531442-BF531442, BH531442-BS531442, BU531442-CF531442, CH531442-CS531442, CU531442-DF531442, DH531442-DS531442, DU531442-EF531442, EH531442-ES531442, EU531442-FF531442, FH531442-FS531442.

The end result is 531,441 rows (from row 2 to 531,442) of data in Sheet2 with each row containing data in cells U-AF, AH-AS, AU-BF, BH-BS, BU-CF, CH-CS, CU-DF, DH-DS, DU-EF, EH-ES, EU-FF, FH-FS.

 

[StephenCrump]

Thanks

I understand the 531,441 groups, and the copying 12 times, but I'm still stuck on the concept of "firsts" ..

-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).

The row is determined by wherever the first desired cell (yesA, yesB, or yesC) is located within a given column.

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)? So won't the first occurrence always be in Group 1, meaning that you copy the Group1 data 531,441 times?

Or if you are searching for YesA, say, individually, won't that similarly mean copying the Group1 data (which contains YesA in every column) 531,441 times?