Repeat one column number of times there is text in another

Hi,
im formatting some AD-data in excel and my problem is the following. My data is as follows:

Group 1	Member 1	Member 2
Group 2	Member 4	Member 5	Member 6
Group 3	Member 2	Member 4	Member 6

And the prefered result would be:

Member 1	Group 1
Member 2	Group 1
Member 6	Group 2
Member 6	Group 3

In other words it would be just two columns and the member name repeats the amount of time it belongs to groups and of course the group name repeats just as many times.

Hi and welcome to the forum,

Where have Members 4 & 5 gone in your desired results?

Regards

Please try this file.

Or, if you prefer the Members to be listed in order, then, assuming that the data you give is in the range A1:D3, enter these array (important!) formulas:

For your Members list (enter in cell I1 and copy down as required):

=IFERROR("Member "&SMALL(IF($B$1:$D$3<>"",--TRIM(SUBSTITUTE($B$1:$D$3,"Member",""))),ROWS($1:1)),"")

For your Group list (enter in cell J1 and copy down as required):

=IFERROR(INDEX($A$1:$A$3,SMALL(IF(($B$1:$D$3=I1)*($B$1:$D$3<>""),ROW($B$1:$D$3)-MIN(ROW($B$1:$D$3))+1),COUNTIF($I$1:$I1,I1))),"")

Regards

Hi Mainman,

Please modify the range. I will work.

Rgds

"I tried using this but it gave me an error at the first "" ($B$1:$D$3<>"")."

I believe your version of Excel may use a slightly different format for the quotation mark ". Try replacing those in my formula with the version generated in your Excel.

Regards