Hi,

Not sure if this is possible using functions or if it requires a macro.
I have two lists, one containing all possible variations of items and one
containing a list of items. What I need to do is combine these lists to
produce a third list that contains every version of the items in the second
list.

Example:

List 1
Column A Column B
Widget Red
Widget Green
Widget Blue
Gadget Red
Gadget Yellow
Thingy Purple
Thingy Pink
DooDah Orange
Doodah Green
Doodah Turquoise

List 2
Column A
Gadget
Doodah

List 3 - Desired Output
Gadget Red
Gadget Yellow
DooDah Orange
Doodah Green
Doodah Turquoise

Any ideas? Thanks.

Assumptions:

A2:B11 contains List 1

D2:D3 contains List 2

Formulas:

F2:

=SUMPRODUCT(COUNTIF(A2:A11,D2:D3))

G2, copied down and across:

=IF(ROWS(G$2:G2)<=$F$2,INDEX(A$2:A$11,SMALL(IF(ISN UMBER(MATCH($A$2:$A$11,
$D$2:$D$3,0)),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(G$2:G2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Freeflyer wrote:

Hi,

Not sure if this is possible using functions or if it requires a macro.
I have two lists, one containing all possible variations of items and one
containing a list of items. What I need to do is combine these lists to
produce a third list that contains every version of the items in the second
list.

Example:

List 1
Column A Column B
Widget Red
Widget Green
Widget Blue
Gadget Red
Gadget Yellow
Thingy Purple
Thingy Pink
DooDah Orange
Doodah Green
Doodah Turquoise

List 2
Column A
Gadget
Doodah

List 3 - Desired Output
Gadget Red
Gadget Yellow
DooDah Orange
Doodah Green
Doodah Turquoise

Any ideas? Thanks.

Thanks very much. Just tested it using the example below and it seems perfect.

Michael

"Domenic" wrote:

Assumptions:

A2:B11 contains List 1

D2:D3 contains List 2

Formulas:

F2:

=SUMPRODUCT(COUNTIF(A2:A11,D2:D3))

G2, copied down and across:

=IF(ROWS(G$2:G2)<=$F$2,INDEX(A$2:A$11,SMALL(IF(ISN UMBER(MATCH($A$2:$A$11,
$D$2:$D$3,0)),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(G$2:G2))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Freeflyer wrote:

Hi,

Not sure if this is possible using functions or if it requires a macro.
I have two lists, one containing all possible variations of items and one
containing a list of items. What I need to do is combine these lists to
produce a third list that contains every version of the items in the second
list.

Example:

List 1
Column A Column B
Widget Red
Widget Green
Widget Blue
Gadget Red
Gadget Yellow
Thingy Purple
Thingy Pink
DooDah Orange
Doodah Green
Doodah Turquoise

List 2
Column A
Gadget
Doodah

List 3 - Desired Output
Gadget Red
Gadget Yellow
DooDah Orange
Doodah Green
Doodah Turquoise

Any ideas? Thanks.