Row data to column, with two columns of data to repeat

Hello TanyaMc,

Welcome to the forum

Can I ask you to clarify a few details? What is the maximum number members a team can have? And do you prefer an approach with just formulas, or are you also fine with VBA?

In that case, do you want like, giant formulas, or you are fine with helper columns? In case you are, how many would you tolerate?

Also will the data gets huge, like thousands of lines?

Please give this sample file a try.

Simply drag the rows with formula on the helper list down until you see a load of blank values, and #N/A value on the Member Data sheet.

To be honest, this is a pain to use if you only want formulas, do you want to try a VBA approach?

In that case, give this sample file a try.

Simply click the "Generate Members' Data" button in the first worksheet, and it will run in less than a blink of an eye.

Another way ... formula wise.

Excel normally "thinks" left to right. Due to number of team members driving the rest of the row outputs this is a little "back-wards". I put header helpers in Members Data sheet that more

closely match those in Team members.

And the blank fields aren't blank when exported. They have non-printing characters.

The first formula takes care of that. It is array-entered in B3 and filled down until you get blanks.


=IFERROR(INDEX('Team Members'!$A$2:$M$150,SMALL(IF(TRIM(CLEAN(SUBSTITUTE('Team Members'!$B$2:$K$150,CHAR(160),"")))<>"",ROW('Team Members'!$B$2:$K$150)-MIN(ROW('Team Members'!$B$2:$K$150))+1),ROWS($3:3)),MATCH('Members Data'!B$1&"*",'Team Members'!$A$1:$M$1,0)),"")

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

These next two are entered regularly.

In C3

=IFERROR(INDEX('Team Members'!$A$2:$M$150,MATCH(B3,'Team Members'!$A$2:$A$150,0),MATCH('Members Data'!C$1,'Team Members'!$A$1:$M$1,0)),"")

in A3

=IFERROR(INDEX('Team Members'!$B$2:$K$150,MATCH(B3,'Team Members'!$A$2:$A$150,0),MOD(COUNTIF($B$3:B3,B3)-1,COUNTIF($B:$B,B3))+1),"")

Great! Thank you!!

I was working with transposing and someone suggested lookup tables but I couldn't work it out. I thought there should be a way to use the team table to assign the state/team name based on leader but couldn't seem to work that out either!

The surveys always put questions in columns and responses in rows. The master calculation file someone else made is using lookup tables to manipulate the response data.

TanyaMc,

With as many rows as you anticipate for output you may want to consider changing that first formula I posted from

=IFERROR(INDEX('Team Members'!$A$2:$M$150,SMALL(IF(TRIM(CLEAN(SUBSTITUTE('Team Members'!$B$2:$K$150,CHAR(160),"")))<>"",ROW('Team Members'!$B$2:$K$150)-MIN(ROW('Team Members'!$B$2:$K$150))+1),ROWS($3:3)),MATCH('Members Data'!B$1&"*",'Team Members'!$A$1:$M$1,0)),"")

to:

=IFERROR(INDEX('Team Members'!$A$2:$M$150,SMALL(IF(TRIM(CLEAN(SUBSTITUTE('Team Members'!$B$2:$K$150,CHAR(160),"")))<>"",ROW('Team Members'!$B$2:$K$150)-MIN(ROW('Team Members'!$B$2:$K$150))+1),ROW()-ROW($3:$3)+1),MATCH('Members Data'!B$1&"*",'Team Members'!$A$1:$M$1,0)),"")


I have been experimenting lately with that construction in the K value of SMALL. It consistently outperforms the former noticeably. May not be noticeable now, but as rows of output increase you will likely note a significant performance drag with the original formula.

I'm not clear on what you mean. Are you adding more team members to some teams? Can you upload another workbook showing what you propose?

It just needs some ranges extended.

In B3 of 'Members Data' array enter this formula and fill down. (Same as before ... commit from edit mode with Ctrl + Shift + Enter)

Formula:

Please Login or Register  to view this content.

Then in A3 and filled down this regular formula.

Formula:

Please Login or Register  to view this content.

Then in C3 this regular formula filled down and across column D.

Formula:

Please Login or Register  to view this content.

The modified headers are the same as my previous with the addition of the Eligibility column.