-2

Specifically I am trying to build a room roster for a conference from the registration data.

I need to search a column of room numbers (Registration!K:K), and when that exact room number shows up (i.e. 111), I need to have the persons name listed in the cell next to it (registration!a23 for instance) to be pasted into one of 6 cells on another sheet that are designated for room 111. There will be 4-6 instances of each room number showing up on the Registration sheet, so I need to have 6 cells on the Room Roster sheet (RoomRoster!b2;b7) that will be filled with the (different) names of those who have been placed in room 111. I want to make sure I don't have the same 6 names, but that it finds each instance that room 111 is listed next to a name without duplicating.

I can then repeat this formula for each additional room number that people are placed in.

ANY help would be appreciated! I have a countif formula setup to tell me how many are in each room, but it would be AMAZING if I could get the actual names listed as well. And if my request is unclear, please feel free to ask me to explain.

Thanks!

Tyler

Tyler
  • 1

1 Answers1

0

You'll need to assign a counter to each person for each room. You can use a quick if formula to make this. Check out the table below, the last row shows the formula: enter image description here

Next, to get these values over into a crosstab/pivot type table, you can use INDEX and Sumproduct. Again, check out the table below. I've put the formula into the last row again. enter image description here

That last formula is nasty. Here's how it works: The Sumproduct formula checks multiple conditions on a range of rows. In our case Rows 2:14. First it checks that C2:C14, which holds our counter is equal to F2, then it checks in each row that A2:A14 hold the room number listed in E3 of our table. IF both conditions are true, it grabs the row for A2:A14 where the condition is true. The *1 is just so that it returns a number to us. SumProduct is crazy powerful this way. The Index formula just takes the ROW from sumproduct and returns the value in B2:B14 that corresponds to that row (minus 1 since the B2:B14 starts on Row 2).

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • That is SUPER helpful! So a follow-up question. One follow up question is in regards to the room number column. You have them listed in order, however they will be assigned as they show up, so there may be 19 different room numbers that are listed in the Room column, however not in descending order... but instead randomly as the registration people fill the rooms (girls with girls, guys with guys, etc) So how would the counter work in that case? Once that's figured out, it looks like this will perfectly fit my needs! Thanks JNevill for your help! – Tyler Sep 23 '14 at 02:24
  • Ok, so I have figured out that it doesn't matter what order the room numbers are placed in. My new problem is that the Index/Sumproduct formula is working, but I can't drag it from the first cell down to the 100th room... meaning I need to manual adjust the formula in each cell for 6 people in a room over the course of 100 rooms! Is there a trick to copying this into multiple cells that I don't understand? (I am needing to do this in Google Spreadsheets... which I know isn't ideal, but necessary for the application) Any help would be appreciate! – Tyler Sep 25 '14 at 04:01
  • ACtually, the sorting of the first list is important since it establishes a unique number per person per room. I'm not sure if there is a good work-around for that. As for the sumproduct copying across rows and columns, make sure that you are locking the cell references (with a $ sign) so that when you copy the ranges to which you are referencing don't move `$A$2:$A$14` for instance, should be locked for both rows and columns since that range needs to remain static. `F$2`, however, only needs the row locked as the Column must change as you copy across. – JNevill Sep 25 '14 at 13:19