Excel: Copy cell info based on string match from another sheet

0
votes

This is sort of a complex worded question, but I have created a sign-up sheet for my dorm's intramural teams, and the responses are listed on an Excel sheet with:

Sheet 1, cell B: Full Name

Sheet 1, cell C: Email Address

Sheet 1, cell E: The sports they wish to play on (there are four)

On sheet 2, I want to organize who and how many people want to play on each team. So I have a column for each sport, and under those are two columns (Full name and email address).

What I want to do is to parse through Sheet 1, cell E for each person who signed up, and if they have the instance of one of the four sports listed (Soccer, Dodgeball, Volleyball, or Bowling), add their Full name and email address under the correct column in sheet 2.

enter image description here

This is an image of sheet 2.

Is there a way/formula to be able to do this? Also, if the person signed up for multiple sports, they should be listed under each one. All the sports they want to play will be listed under a single cell (Sheet 1, cell E).

1
excelexcel-formula
To make the problem easier you would probably sign up twice (or more times)... – Maverick283
@pnuts The signup sheet is a Google form that has checkboxes for the sports. If I were to select Soccer and Dodgeball, in the sheet 1 cell E it would say "Soccer, Dodgeball". – G Boggs

1 Answers

1
votes

There is a way, with an array formula (entered with Ctrl+Shift+Enter)... In cell A3:

=IFERROR(INDEX(Sheet1!$B$1:$B$500,SMALL(IF(ISERROR(SEARCH(A$1,Sheet1!$E$1:$E$500)),9999,ROW(Sheet1!$A$1:$A$500)),ROW()-2)),"")

Let me explain from the inside out...

  • ISERROR(SEARCH(A$1,Sheet1!E10)) would give us FALSE if the value in E10 has the name of the sport (in our header cell: A$1), TRUE otherwise
  • Rather than just giving a single value, we are working on the array of cells Sheet1!$E$1:$E$500 (which could be extended as you needed) - so this gives us an array of TRUE and FALSE values
  • IF(ISERROR(...),9999,ROW(...)) means that if there was not the sport we will get the value 9999, otherwise we get the row number of the cell in the array - so this gives us an array of a mix of 9999 and row numbers
  • SMALL(...,ROW()-2) lets us pick out one of those values from the array - in this case the items in size order, and we are using ROW()-2 as our counter (i.e. in Sheet2!A3 - ROW()-2 is 1 and we get the smallest value from the array)... the -2 deals with the header rows... Effectively we are indexing through a sorted list of row numbers in Sheet1 that match our condition
  • INDEX(Sheet1!$B$1:$B$500,...) is going to give us the value from column B in Sheet1 relating to our position in the list of matching rows - i.e. the Name
  • Sometimes we have our 9999 (for all the cells that didn't match the condition - these are sorted to the end of the list with SMALL) and so the INDEX will give us an error... the index is outside the range. So we can replace it with a blank.

In cell B3 we would do the same, but with =IFERROR(INDEX(Sheet1!$C$1:$C$500,... so that we get the email... We still need to reference A$1 for the sport name. These two cells can then be copied across and down...

Hope this makes some sense! Good luck! And remember to enter array formulas with Ctrl+Shift+Enter...