CODEWITHSUNDEEP
excel-formulaexcelsearchexcel-2007vba
Scott Chamberlain
Scott Chamberlain

Reputation: 127593

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.

Upvotes: 0

Views: 5690

Answers (2)

Henrik K
Henrik K

Reputation: 1061

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!

Upvotes: 1

Alex Andronov
Alex Andronov

Reputation: 1675

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.

Upvotes: 1

Related Questions