how do find the specific column

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

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

Code:

---

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

Code:

---

CRITERIA DEPTCODE, DEPT VISE
KMM
MNO
KPZ

YMM
kpg
kty

---

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

Quote:

---

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

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

Kamakshi

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

Quote:

---

filter deptcode, dept capture userid in sheet2

---

Spoo

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

Kamakshi

Ah, now I get it

Quote:

---

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

---

Code:

---

    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 ...

Code:

---

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

Another alternative using autofilter. Assumes table starts in column A

Code:

---

    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

---