How to automate searching certain values and moving them to another sheet?

[filido]

Hi all,

I have in one column values (red, white) and in another column values (Jack, Marie David etc.) There are several hundreds of rows so that there are different names once but for each name a color (red or white) is assigned. So for example:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]Marie[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]Ashley[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]Matt[/TD]
[/TR]
</tbody>[/TABLE]


And I am trying to search all names with color white and make a list of names to Sheet 2 like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Sheet 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Color[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]David[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]Matt[/TD]
[/TR]
</tbody>[/TABLE]







I would like to do this either with Excel functions and formulas (to automate this) or with VBA but I have no idea how. And the reason I can't use filter and then COPY+PASTE because the process must be automatic so that the user just inserts the data once into Sheet 1 and then Sheet 2 automatically gets updated.

I really hope someone can help me with this!

 

[vds1]

First format the sheet1 data as Table. You can then Create pivot table for sheet1 Table in every new sheet and filter by specific color name. This way you can refresh the data everytime new data set is added.