Search code examples
excel-formulaexcelsearchexcel-2007vba

Creating a sublist based on a second column in excel

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.

Solution

  • One way of achieving the list that you are looking for without any VBA code is to make use of the advanced filter.

    On sheet 1 the input list is entered as follows

    alt text

    on sheet2 enter the filter criteria (this criteria means contains F in Owned by column)

    alt text

    and finally on sheet3, call the advanced filter function like this

    alt text

    make sure to select copy to another location

    select the sheet1 input list as the list range

    select the sheet2 filter criteria as the criteria range

    and select somewhere in sheet3 as the output range (the copy to entry)

    If you prefer to go down the route of excel VBA programming a good first step is to try out the macro recorder in excel (tools - macros - record macro)

    good luck!