Filter

[atuljadhav]

Sir

I have file with two sheet
1st sheet having data with company name (A column) and invoice no. (column B)

but every company have more than two invoice details like as below
now what i want, in sheet 2 when i selected company name it automatically display invoice in respective row.

Vllokup is not working because it takes only fist invoice, i want all the invoice details after entering the company name.

<table border="0" cellpadding="0" cellspacing="0" width="347"><col style="width: 132pt;" width="176"> <col style="width: 128pt;" width="171"> <tbody><tr style="height: 25.5pt;" height="34"> <td class="xl22" style="height: 25.5pt; width: 132pt;" width="176" height="34">Company Name</td> <td class="xl22" style="border-left: medium none; width: 128pt;" width="171">Ref. No.</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">A.A. Enginering</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/GJ/674</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/MH/0774</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">Desigho Aluminium </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/0077</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/0137</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/351</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">Enercon (India) </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/BRD/0295</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">RETAIL/GJ/260</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">Farmson Analgesics</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/VD/1000</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">MTAX 220</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">Growel Fabs</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/KA/0327</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/KA/0336</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/KA/0397</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17">SYSTEM PVT. LTD.</td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/ND/0050</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/ND/0066</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAX/K/0269</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt; border-top: medium none;" height="17"> </td> <td class="xl24" style="border-top: medium none; border-left: medium none;">TAXB 03/138</td> </tr> </tbody></table>

 

[MickG]

Place this in sheet (2).
The code assumes you have a dropDown list in sheet (2) "A1", or that cell has the name your looking for in the list in sheet (1).
Results start Sheet(2) "B1".

[COLOR=navy]Sub[/COLOR] MG15Sep24
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
[COLOR=navy]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Dn = Range("A1") [COLOR=navy]Then[/COLOR]
       .Range(.Cells(Dn.Row, 1), .Cells(Dn.Row, 1).End(xlDown).Offset(-1)).Offset(, 1).Copy Range("B1")
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[atuljadhav]

i don'w know how to use this code
please explain where and how use this code

 

[Barbara Excel]

I would use Advanced filter for this. If you select the company name all of the invoices associated with that company will be filtered out.

Regards
Barbara Excel

 

[MickG]

Open sheet (2).
Right click the sheet tab, Select "View Code", (Vb window appears).
Paste entire code below into Blank window.
Close VB window.
Select Another sheet, then select Sheet(2) again.
In cell "A1" you should now have a "Drop down list" of the Company Names in sheet (1).
Select a name from the List and the related (Sheet(1), Column "B") data should appear in Column "B" of your active sheet.
NB:- This code uses Column "Z" of sheet2 as a holding column.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Activate()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
With Sheets("Sheet1") '[COLOR="Green"][B]sht1[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]Set[/COLOR] Rng = Rng.SpecialCells(xlCellTypeConstants)
    Rng.Copy Range("Z1")
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("Z1"), Range("Z" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
Range("A1").Select
    [COLOR="Navy"]With[/COLOR] Selection.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=" & Rng.Address & ""
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
With Sheets("Sheet1") '[COLOR="Green"][B]sht1[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn = Range("A1") [COLOR="Navy"]Then[/COLOR]
            Columns("B:B").ClearContents
            .Range(.Cells(Dn.Row, 1), .Cells(Dn.Row, 1).End(xlDown).Offset(-1)).Offset(, 1).Copy Range("B1")
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[atuljadhav]

now it's working
every this is possible in excel
tnx expert