Lookup and Vlookup

[ahs004]

Hi All,

I have a spreadsheet with 2 tabs. first has all contact details for m
customers with name, address and so on in a single row. i also have
several columns which tells me the group they belong to with a ye
against their row.

I want to be able to use the tab 2 to pull out the customers who belon
to specific groups.

for eg: if Mr Smith has a yes against their group RAC, i want him t
appear on the second tab of my spreadsheet.

each month the groups will change so instead of copying and pasting
want to use a system where it should automatically update the second ta
accordingly.

Vlookup may do the trick but i am not sure how to use this with havin
so many Yes's?

Your help would be appreciated.

I can use filter but the groups change on a daily basis and i would lik
to automate this if possible.

I have attached a template with deleting 100s of thousands of custome
details

+-------------------------------------------------------------------
|Filename: H5000.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=480
+-------------------------------------------------------------------

 

[ahs004]

Hi there,

Any help with this will be much appreciated.

thanks

ahme

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[zvkmpw]

I have a spreadsheet with 2 tabs. first has all contact details for my

customers with name, address and so on in a single row. i also have a
several columns which tells me the group they belong to with a yes
against their row.

I want to be able to use the tab 2 to pull out the customers who belong
to specific groups.

for eg: if Mr Smith has a yes against their group RAC, i want him to
appear on the second tab of my spreadsheet.

each month the groups will change so instead of copying and pasting i
want to use a system where it should automatically update the second tab
accordingly.

In my example, the group names start in Sheet1!M1 and extend to the right. Customer names start in Sheet1!A2 and extend downward.

The desired result is in Sheet3.

Sheet2 holds intermediate calculations.

In Sheet2!A1, put
=IF(Sheet1!M1="","",Sheet1!M1)
and copy rightward as far as could be needed.

Do the same in Sheet3.

In Sheet2!A2 put
=IF(Sheet1!M2="yes",MAX(A$1:A1)+1,"")
and copy rightward and downward as far as could be needed.

In Sheet3!A2 put
=IF(ROW()-1>MAX(Sheet2!A:A),"",
OFFSET(Sheet1!$A$1,MATCH(ROW()-1,Sheet2!A:A,0)-1,0))
and copy rightward and downward as far as could be needed.