It sounds like you want a simple autofilter. Simply filter on the
'Market' column for "A" and it will show you all rows with "A" in that
column.

Check out: http://www.contextures.com/xlautofilter01.html

If you now wanted to copy them to a new sheet, simply highlight the
visible rows and paste them into a new sheet.

For a VBA solution check out: http://www.mrexcel.com/archive2/1800/2071.htm

or
http://www.rondebruin.nl/copy5.htm


HTH,
JP

On Jan 28, 10:45*am, Jacey wrote:
Hi,

I have a workbook that consists of Sheet 1 and Sheet 2 as example below.

Sheet 1
Market|Name|Salary
A|Andy|1000
A|Janice|500
B|Fong|800
C|Lisa|890

Sheet 2
Market|State|Revenue Jan|Revenue Feb
A|AB|9000|9780
A|AC|8000|7000
D|DF|9000|8000

First, I would like to identify unique value for Market from both sheets. It
should returns A from Sheet 1 and 2, B and C from Sheet 1 and also D from
Sheet 2.

Then I will make a new workbook for each unique value in which this new
workbook will consist 2 Sheet per format in Sheet and 2 as above.

Hence Workbook A is looks like this:
Sheet 1
Market|Name|Salary
A|Andy|1000
A|Janice|500

Sheet 2
A|AB|9000|9780
A|AC|8000|7000

While workbook B is:
Sheet 1:
Market|Name|Salary
B|Fong|800

and no Sheet 2 as B value is not found is data source.

Please assist. Thanks and cheers