how do find the specific column

[kamakshi]

Thanks for your great help.

i have the sheet1
5 columns deptcode, userid, username, group, dept
how do caputre userid deptcode and dept vise in sheet2.
for example

deptcode userid username group dept
xxxyz mno xxxxx k2b KMM
xxxkz kpg yyyyy sb2 YMM
xkbzy ykg uuuuu ki1 KMM
xxxkz kty zzzzz kb3 YMM
xxxyz KPZ xxxxx k2b KMM




CRITERIA DEPTCODE, DEPT VISE
KMM
MNO
KPZ

YMM
kpg
kty

[Spoo]

Kamakshi

You nicely indented in your OP, but the spaces
unfortunately were lost. If I wrap the text with
the Code wrapper, it becomes a little easier to
read ...

Sheet 1

deptcode   userid username group  dept
xxxyz      mno     xxxxx   k2b    KMM
xxxkz      kpg     yyyyy   sb2    YMM
xkbzy      ykg     uuuuu   ki1    KMM
xxxkz      kty     zzzzz   kb3    YMM
xxxyz      KPZ     xxxxx   k2b    KMM

Sheet 2

CRITERIA DEPTCODE, DEPT VISE
KMM
MNO
KPZ

YMM
kpg
kty

Ok, that being done, I am confused by your needs

how do caputre userid deptcode and dept vise in sheet2

To the best of my understanding ...

• userid deptcode are two cols with data, on Sheet 1.
• dept vise is on Sheet 2, but there seems to be no data in this col.

Could you explain in a little more detail what you
would like to accomplish?

Spoo

[kamakshi]

Spoo
thank you very much
in sheet 1 all columns displayed
filter deptcode, dept capture userid in sheet2.
thanks once again

[Spoo]

Kamakshi

I'm sorry, but I have no idea what you mean by this

filter deptcode, dept capture userid in sheet2

Spoo

[kamakshi]

thanks spoo
if i select particular deptcode and dept will capture the corresponding userid in sheet2
for example
if i select deptcode xxxyz and dept kmm will show userid
MNO
KPZ

[Spoo]

Kamakshi

Ah, now I get it

if i select deptcode xxxyz and dept kmm will show userid
MNO
KPZ

    A          B      C        D      E    ..   std reference style
    1          2      3        4      5    ..   R1C1 reference style
    deptcode   userid username group  dept
    ========   ====== ======== =====  ====
1   xxxyz      mno     xxxxx   k2b    KMM
2   xxxkz      kpg     yyyyy   sb2    YMM
3   xkbzy      ykg     uuuuu   ki1    KMM
4   xxxkz      kty     zzzzz   kb3    YMM
5   xxxyz      KPZ     xxxxx   k2b    KMM

Maybe something along these lines ...

deptcode = "xxxyz"
dept = "KMM"
nn = 0
For rr = 1 to 5
    If Sheets(1).Cells(rr, 1).Value = deptcode And Sheets(1).Cells(rr, 5).Value = dept Then
        nn = nn + 1
        userid = Sheets(1).Cells(rr, 2)
        Sheets(2).Cells(nn, 1).Value = userid
    End If
Next rr

I have not tested this, but hopefully it will give you some ideas.

Spoo

[cholland3403]

Another alternative using autofilter. Assumes table starts in column A

    Range("A1").Select
    ActiveSheet.AutoFilterMode = False
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="xxxyz" ''replace with a variable
    Selection.AutoFilter Field:=5, Criteria1:="KMM"   ''replace with a variable
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste