I have an excel sheet, in my sheet I have field force data like this
S#NameDesignationTerritoryGroup
1Razi uddin AhmedA.MKarachiL+P
2Zishan BadarS.P.OKarachiGeneral
3VacantS.P.OKarachiPoultry Nawan
4Iftikhar HussainA.M.HyderabadL+P
5M. Tariq Khan S.P.OHyderabadPoultry Nawan
6Dr. Chandar Kumar S.P.OHyderabadGeneral
7Waheed Aslam ChistiA.MMultanL+P
8Mohammad Shahid S.P.OMultanPoultry Nawan
9Mohammad ZahidS.P.OKhanewalPoultry Nawan
10Hafiz Samiullah S.P.OR.Y.KhanPoultry Nawan
11Mohammad Rashid S.P.OMultanPoultry Pameer
12VacantAM
13Mohammad BilalS.P.O (Group-B)MultanGroup-B Livestock
14Mohammad Mosa RazaS.P.O (Group-A)MultanGroup-A Livestock
15Farooq AhmedS.P.OD.G KhanGeneral
16Khalid MehmoodS.P.OVehariGeneral
17Mohammad Muneer S.P.OBhawalpur General
18Ghulam Mohyy uddinS.P.OR.Y.KhanGeneral
19Shehzad ButtTMLahorePoultry Nawan
20Mohammad Amin S.P.OLahorePoultry Nawan
21Mohammad Junaid ArshadS.P.OGujranwalaPoultry Nawan
22Aftab Bodhla S.P.OLahorePoultry Pameer
23Nisar AhmedAMGujranwalaLivestock
24Waheed AnwarS.P.OKasurGeneral
25Mahmood ul Hasan S.P.O (Group-A)S.PuraGroup-A Livestock
26Rizwan ul Haq S.P.O (Group-B)S.PuraGroup-B Livestock
27Muhib AliS.P.OGujranwala + HafizabadGeneral
28M. Nasir YaminS.P.ONarowalGeneral
29Dr Hasib AhmedRSML+P
30VacantAM
31Naveed Ahsan ShahS.P.OFaisalabadPoultry Nawan
32Farrukh Zafar S.P.OSargodhaPoultry Nawan
33Hafiz Tahir Iqbal S.P.OSahiwalPoultry Nawan
34Kh. M NaeemS.P.OFaisalabadPoultry Pameer
35Kashif Memood AlviS.P.O (Group-B)FaisalabadGroup-B Livestock
36Ghulam MurtazaS.P.O (Group-A)JaranwalaGroup-A Livestock
37Saqib Nazir S.P.OSGD + Khushab + MainwaliGeneral
38Khalid Naseer S.P.OSahiwalGeneral
39Hafiz Numan AshrafS.P.OJhangGeneral
40Ghous Baksh NadirS.P.OBhakkarGeneral
41Asif AzizA.MRawalpindiL+P
42Mohammad Asim S.P.ORwp + Gujrat Poultry Nawan
43Naeem ur RehmanS.P.OTLG+ChakwalGeneral
44Haroon ur RasheedS.P.ORawalpindiPoultry Pameer
45Syed Mohammad HussainS.P.ORawalpindiGeneral
46Mohammad Ijaz AnsariS.P.OGujratGeneral
47Mujahid AkbarA.MPeshawarL+P
48Masood ShahS.P.OPeshawarPoultry Nawan
49Shah Faisal S.P.OPeshawarPoultry Pameer
50Mr. Abdur RehmanS.P.OPeshawarGeneral
51Tauseef AmedS.P.OD.I KhanGeneral
52Mohammad Naeem S.P.OSwatGeneral
is it possible that in sheet 2 when I Type group Like General in cell a1 the detail of SPOs’ of General Group will automatically entered from range a4, and when I type territory like Lahore in cell b1 the detail of SPOs’ of Lahore will automatically entered from range a4, or when i type Designation Like S.P.O in cell c1 the detail of SPO will automatically entered from range a4. Please tell me how to do this with excel or vba
Try this first and then let us know if you still want exactly what you asked for.
In Excel 2007_________________
On Sheet 1 (0r a copy of Sheet 1in Sheet2, if you wish) select all five columns that contain data and then select Data > Filter
Squares with small triangles in them will appear at the right edge of the Row 1 cells. Click on the one in Column E (the cell should say Group) and (among some other things) a group of check boxes will appear.
The first check box is labeled (Select All) the rest are labeled with each unique value in the column. Check the boxes for the values you want to see and select OK. Try different combinations to see what happens.
You can do this with any column. Just realize that any column filter just considers what has not been eliminated by selections in other columns. So you will usually want to Select All on most columns.
Selecting Data > Filter again will remove all filters and display all of the data.
For Excel versions before 2007____________________
Read trough the Excel 2007 section first and then proceed.
Select all of the data columns and then select Data > Filter > Auto Filter. Everything will be the same as in Excel 2007 except your choices of what to display will be limited to All or any one value at a time. Selecting Data > Filter > Auto Filter a second time will remove filtering.
____________________________
If this doesn’t meet your needs, a less flexible macro could be written. Just expand the question to indicate that is what you prefer and a little about why so that the macro is more likely to meet your needs.
Gary E | Jan 01, 2009
Ideally, the fields in the data being imported to Excel will be delimited by a tab or some other character. If it isn’t, the fields should be fixed length. Once the data has been imported, you can use Excel’s data filtering so the program will only display records having specified values in a given column.
If the input data is not delimited or fixed length, the task of getting the data into separate worksheet cells is almost impossible, even with VBA. So make sure the data is provided in a delimited format before importing.
rknoblock | Dec 31, 2008
