Creating a sublist based on a second column in excel

Question

I have two columns, the first column will have the name of a object, the second is who it belongs to. I want a new sheet for each person to list what they had assigned to them. here is a example:

dog F
cat F
bell S
whistle 
bird F

So Fred has a dog, cat, and a bird; Scott has a bell; and no one has a whistle on their page. Now doing a simple IF() i can get it to look like this for Fred's page

TOP OF ROW
dog
cat


bird

And Scott's page will look like

TOP OF ROW


bell

however I want Fred's to look like

TOP OF ROW
dog
cat
bird

and Scott to be the same.
My current train of thought is to use =VLOOKUP($C$1,Items!A2:C1000,3) in a hidden column in D to tell me which row my data is in, (where Column C on Items is a hidden column with the row number of the row and C1 is the search parameter (S or F)), then =IFERROR(CELL("contents",INDIRECT(ADDRESS($D2,2,1,TRUE,"Items"))),"") , however I other than changing my row index of my search array to 1+ the last found item (which i have not figured out how to do) I can not figure out how to continue searching for the next item. I know C++ and C# but never have coded in VBA before and I rely heavily on the MSDN and to my knowelge there is no MSDN section dedicated to the Excel API.

Answer 1

There is a good way of doing this with functions in excel.

Essentially you need to create a running countif

So in C2 you would have =COUNTIF($B$2:$B2,"F") Obviously the "F" could also be a reference to another cell. If you fill this formula down the range it will expand the range. Eg. in C3 it will say =COUNTIF($B$2:$B3,"F")

This will give you a running total in column C in your example this would mean:

dog F 1

cat F 2

bell S 2

whistle 2

bird F 3

The fact that you have 3 2s doesn't matter because a vlookup will always match to the first match it finds.

This technique has a lot of different applications. And depending on the data you may find it easier to put this on the left of the data so the VLOOKUP will be easier.