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]

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.

Stephen, thank you! This is awesome! I owe you a beer (or two or three) if we ever meet in person.