Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel Formula Help?
Hello , i stuck with some formula in Excel. I upload my work in website, can somebody help me
I have a list in section 1 names , how many ppl and table no's.
I want to write a formul which is automatically fills the tables.
For example if someone in the list has a reservation on table 1 , formul automatically put it to table 1 . Thanks
Here is the file .
2 Answers
- Sarah MLv 51 decade agoFavorite Answer
You need to use array formulas for this:
Copy this into cell B17:
=IF( COUNTIF($C$2:$C$13, $D$17)>=1, INDEX( $A$2:$C$13, SMALL(IF( $C$2:$C$13= $D$17, ROW($C$2:$C$13)- ROW($C$2)+1, ROW($C$13)+1), 1),0), "")
Instead of just pressing ENTER to confirm it, press CTRL+SHIFT+ENTER at the same time. Excel will surround it in {curly brackets} if you do it correctly. Do this whenever you edit/enter an array formula.
For B18, use the same formula but change two of the "1"'s into "2"s. The first one: >=1 near the beginning, and the second one: ,1),0,"") at the end, like so:
=IF( COUNTIF($C$2:$C$13, $D$17)>=2, INDEX( $A$2:$C$13, SMALL(IF( $C$2:$C$13= $D$17, ROW($C$2:$C$13)- ROW($C$2)+1, ROW($C$13)+1), 2),0), "")
For B19, change those into 3's. And so on for each item in your first table.
Now, once you have the first column of your first table set up, copy the formulas from B17:B23 and paste into cells C17:C23. Do a find and replace:
FIND: ),0,"")
REPLACE WITH: ),2,"")
This will populate the second column of your table with the "how many people" data instead of the name info.
Then, select B17:C23, and replace all the $D$17 with D17.
Now you can copy B17:C23 and paste it into your 9 other tables.
Hope my explanation works for you.
Source(s): Spreadsheet user since the DOS days.