| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
extract duplicate name
I have a table (sheet2), which in column `A` have surnames and column `B`
first names, column `C` a date. What I`m after is to enter a surname in cell M4(sheet1), this produces a list of all clients with that surname and first names and date. I was after a vb code for this procedure. Can anybody help. -- George |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
use data filter advanced filter. It is easier than a macro and pretty
powerful. sheet2 (data) Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 sheet3 (criteria) Surname FirstName Date Tyson sheet4 (where you will put the results) Surname FirstName Date now do this - data filter advanced filter - copy to another location - list range (select your data on sheet2) - criteria range (select sheet3 A1:C2) - copy to (select sheet4 A1:C1) ok -- Allllen "George" wrote: I have a table (sheet2), which in column `A` have surnames and column `B` first names, column `C` a date. What I`m after is to enter a surname in cell M4(sheet1), this produces a list of all clients with that surname and first names and date. I was after a vb code for this procedure. Can anybody help. -- George |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
It`s not working. I keep getting a message "Reference is not valid". Is it
possible to just have two sheets, rather then three? -- George "Allllen" wrote: use data filter advanced filter. It is easier than a macro and pretty powerful. sheet2 (data) Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 sheet3 (criteria) Surname FirstName Date Tyson sheet4 (where you will put the results) Surname FirstName Date now do this - data filter advanced filter - copy to another location - list range (select your data on sheet2) - criteria range (select sheet3 A1:C2) - copy to (select sheet4 A1:C1) ok -- Allllen "George" wrote: I have a table (sheet2), which in column `A` have surnames and column `B` first names, column `C` a date. What I`m after is to enter a surname in cell M4(sheet1), this produces a list of all clients with that surname and first names and date. I was after a vb code for this procedure. Can anybody help. -- George |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
One non-array formulas play which could deliver the required results ..
Assuming source data below is in Sheet2, cols A to C, from row2 down Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 etc In Sheet1, the surname of interest will be input in M4 (Labels placed in O3:Q3 : Surname, FirstName, Date) Put in N4: =IF($M$4="","",IF($M$4=Sheet2!A2,ROW()-2,"")) (Leave N1:N3 blank) Put in O4: =IF(ROW(A1)COUNT($N:$N),"",INDEX(Sheet2!A:A,SMALL ($N:$N,ROW(A1)))) Copy O4 to Q4. Select N4:Q4, fill down to cover the max expected extent of source data in Sheet2, say down to Q200. Hide away col N. Format col Q as date. Cols O to Q will return the required results for the surname input in M4. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote: It`s not working. I keep getting a message "Reference is not valid". Is it possible to just have two sheets, rather then three? -- George "Allllen" wrote: use data filter advanced filter. It is easier than a macro and pretty powerful. sheet2 (data) Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 sheet3 (criteria) Surname FirstName Date Tyson sheet4 (where you will put the results) Surname FirstName Date now do this - data filter advanced filter - copy to another location - list range (select your data on sheet2) - criteria range (select sheet3 A1:C2) - copy to (select sheet4 A1:C1) ok -- Allllen "George" wrote: I have a table (sheet2), which in column `A` have surnames and column `B` first names, column `C` a date. What I`m after is to enter a surname in cell M4(sheet1), this produces a list of all clients with that surname and first names and date. I was after a vb code for this procedure. Can anybody help. -- George |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
It now works fine. Thank you very much.
-- George "Max" wrote: One non-array formulas play which could deliver the required results .. Assuming source data below is in Sheet2, cols A to C, from row2 down Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 etc In Sheet1, the surname of interest will be input in M4 (Labels placed in O3:Q3 : Surname, FirstName, Date) Put in N4: =IF($M$4="","",IF($M$4=Sheet2!A2,ROW()-2,"")) (Leave N1:N3 blank) Put in O4: =IF(ROW(A1)COUNT($N:$N),"",INDEX(Sheet2!A:A,SMALL ($N:$N,ROW(A1)))) Copy O4 to Q4. Select N4:Q4, fill down to cover the max expected extent of source data in Sheet2, say down to Q200. Hide away col N. Format col Q as date. Cols O to Q will return the required results for the surname input in M4. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote: It`s not working. I keep getting a message "Reference is not valid". Is it possible to just have two sheets, rather then three? -- George "Allllen" wrote: use data filter advanced filter. It is easier than a macro and pretty powerful. sheet2 (data) Surname FirstName Date Tyson Michael 04/05/99 Nelson Horatio 06/11/94 Jones Michael 12/08/65 Tyson Butch 07/31/84 sheet3 (criteria) Surname FirstName Date Tyson sheet4 (where you will put the results) Surname FirstName Date now do this - data filter advanced filter - copy to another location - list range (select your data on sheet2) - criteria range (select sheet3 A1:C2) - copy to (select sheet4 A1:C1) ok -- Allllen "George" wrote: I have a table (sheet2), which in column `A` have surnames and column `B` first names, column `C` a date. What I`m after is to enter a surname in cell M4(sheet1), this produces a list of all clients with that surname and first names and date. I was after a vb code for this procedure. Can anybody help. -- George |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
Glad to hear that, George !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote in message ... It now works fine. Thank you very much. -- George |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
Max,
Once again thanks for that last formula, but that has brought up another question. Having extracted the list of names, how can I add and save additional information in the remarks column. -- George "Max" wrote: Glad to hear that, George ! Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote in message ... It now works fine. Thank you very much. -- George |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
George,
Pl put in your new query as a new posting. Suggest you try posting in excel.programming. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote in message ... Max, Once again thanks for that last formula, but that has brought up another question. Having extracted the list of names, how can I add and save additional information in the remarks column. -- George |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
extract duplicate name
Ok I will. Once again thanks very much
-- George "Max" wrote: George, Pl put in your new query as a new posting. Suggest you try posting in excel.programming. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "George" wrote in message ... Max, Once again thanks for that last formula, but that has brought up another question. Having extracted the list of names, how can I add and save additional information in the remarks column. -- George |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract duplicate rows to another worksheet | Excel Worksheet Functions | |||
| Find duplicate records in Excel 2003 | Excel Discussion (Misc queries) | |||
| Trendline Extract | Charts and Charting in Excel | |||
| Extract Unique Values, Then Extract Again to Remove Suffixes | Excel Discussion (Misc queries) | |||
| Extract specific data into its own workbook via macro? | Excel Discussion (Misc queries) | |||
Linear Mode
