Extract data - Help wanted!!!

M

mohitmahajan

:mad: On sheet 1 of the wrkbook I have data under various headings lik
user id, user name, client name, error 1, error 2, error 3, etc. On th
2nd tab I have the user names. Now I want to extract the rows with dat
from 1st tab having the names listed in 2nd tab in the 3rd tab whic
the macro shud create itself.

This is to be done since the data is of several teams and takes quit
some time in copying data using filter option
 
A

Arvi Laanemets

Hi

Maybe you design a report sheet? An example:

Add a sheet Users, with columns UserID, UserName (p.e. headers in A1:B1)
Define dynamic named ranges
UserID=OFFSET(Users!$A$2,,,COUNTIF(Users!$A:$A,"<>")-1,1)
UsersTbl=OFFSET(Users!$A$2,,,COUNTIF(Users!$A:$A,"<>")-1,2)

Add a sheet UserRep
Format cell B1 as combo using data validation list with source
=UserID
Into cell B2 enter the formula
=VLOOKUP($B$1;UsersTbl;2;0)

On Sheet1, insert a column to left of your table (It's now column A), let's
it's heading be RepRow.
I assume the row 1 contains column headings
RepRow, UserID, UserName, ClientName, Error1, Error2, etc.

Into cell A2 on Sheet1 enter the formula
=IF($B2=UserRep!$B$1,COUNTIF($B$2:$B2,$B2),"")
and copy it down at least until end of your table (but you can have some
amount of rows ready).

Define named range p.e.
MyTable=OFFSET(Sheet1!$A$2;;;COUNTIF(Sheet1!$B:$B;"<>")-1;10)
(instead 10 set the last parameter acording the number of columns in your
table, with RepRow included)

On UserRep sheet, into row 4 enter column headers, like
ClientName, Error1, Error2, etc.

Into cell A5, enter the formula
=IF(ISERROR(VLOOKUP(ROW()-4,MyTable,4,0)),"",VLOOKUP(ROW()-4,MyTable,4,0))
Into cell B5, enter the formula
=IF($A5="","";VLOOKUP(ROW()-4,MyTable,5,0))
Into cell C5, enter the formula
=IF($A5="","";VLOOKUP(ROW()-4,MyTable,6,0))
etc. for all columns

Copy formulas in row 5 down for max possible number of rows you think you'll
need. When you want, you can hide the RepNum column on Sheet1.
It's all!
 
Top