Copy certain Rows dependent on cell values.

[Leeinsa]

I have a sheet with clients listed with their address's and account information. Each client has 1 entry per account type, ie if client A has 2 accounts, then his name and contact details appears on two rows (the difference between the two rows are the account details.).

What I need is to have two drop down boxes which list "client" and "account type" respectively, when a particular client is selected from drop down 1, then all rows which contain that client name in the client column, are copied to a separate sheet in a group with no blank lines between them.

Likewise If i select a particular account type from drop down 2 than all rows with that account type in the column will be copied to a separate sheet in a group with no blank lines between them.

So in effect this is what will take place:

1) select client from 1st dropdown.
2) Excel scans client column on sheet 1 for all instances that match my selected client.
3) Those rows wich match the selcted client will be copied to a seperate sheet in a group.

Same for account type, and maybe even so i can use both drop down boxes together so i can select specific account types for specific clients and copy the information to the second sheet with no gaps.

Hope this is clear, it looks very complicated but isnt really, i just can't explain it very well, Any help much appreciated.

Lee.

 

[VoG]

See if you can adapt any of this http://www.mrexcel.com/forum/showthread.php?t=396069

 

[Leeinsa]

Thanks for quick response VOG,

The routines you referenced above look a bit over my head, but nothing ventured nothing gained, gotta learn somehow so I guess if i play with them I'll learn something anyway. Thanks again,

Lee.

PS, anyone with simpler solution please post for me to check out. Thanks.

 

[jbeaucaire]

Leeinsa, I understand the attraction of a hard-won macro, but what you've described sounds like Excel's built-in AutoFilter.

Click in your date, and click Data > Filter > AutoFilter.

Now you have the "drop boxes" you've described at the top of your data. If you select any one client from the drop you get just their data, regardless of where it is in the data. The rest of the data is there, just hidden.

If you set that column back to "all", you can now filter by account "type" in that column. Very cool.

Another benefit is that if you copy all the visible data to somewhere else (another sheet, for isntance), it will only copy the visible rows.

Won't that serve you well?

 

[Leeinsa]

jbeaucaire,

Yes the filters can be used to acheive what i need but not the way i need it to happen. You see the drop down boxes need to be on a seperate sheet so the end users cannot see all the sdata on the first sheet, each end user will only be able to see the clients they are allowed to see from the drop down box bringing up that particular cleints details.

So while the filters are fine for me to use to do my work, they don't fit the needs i have for end users.

I actually though this one would be quite easy for those experts that know the right commands, look down the cells in 1 column, if the value matches a given value, then copy the whole row to another sheet and carry on looking. But it may be more complex than i thought.

Thanks very much anyway.