| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a list of job numbers, missing out irrelevant items
I have a table with the headings; job number, title, and option (A/E).
I want to pull in the jobs which are only option A into a new worksheet table. So my first table may have Job No Title Option 1234 Site 01 A 2345 Site 02 E 3456 Site 03 E 4567 Site 04 A My new table should only have: Job No 1234 4567 Kind regards Dylan |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Easiest way would be to copy the worksheet, then sort the new one by "option
column" and then delete the rows you don't want. "DDawson" wrote in message ... I have a table with the headings; job number, title, and option (A/E). I want to pull in the jobs which are only option A into a new worksheet table. So my first table may have Job No Title Option 1234 Site 01 A 2345 Site 02 E 3456 Site 03 E 4567 Site 04 A My new table should only have: Job No 1234 4567 Kind regards Dylan |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Another way which drives it out auto in another sheet
Assume source data as posted in Sheet1's cols A to C, from row2 down In Sheet2, Put in A2: =IF(Sheet1!C2="A",ROW(),"") Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) Select A2:B2, copy down to cover the max expected extent of data in Sheet1. Hide away col A. Col B will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote: I have a table with the headings; job number, title, and option (A/E). I want to pull in the jobs which are only option A into a new worksheet table. So my first table may have Job No Title Option 1234 Site 01 A 2345 Site 02 E 3456 Site 03 E 4567 Site 04 A My new table should only have: Job No 1234 4567 Kind regards Dylan |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Thanks Max, this kinda does what I want.
Sheet one contains the time people spend on each project and, so it may have two or three people's time to one job number. I need to remove the duplicates in sheet two, so that I have a column showing what jobs have Option E work to be payed. I'm sorry, I didn't explain this at the beginning. Dylan "Max" wrote: Another way which drives it out auto in another sheet Assume source data as posted in Sheet1's cols A to C, from row2 down In Sheet2, Put in A2: =IF(Sheet1!C2="A",ROW(),"") Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) Select A2:B2, copy down to cover the max expected extent of data in Sheet1. Hide away col A. Col B will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote: I have a table with the headings; job number, title, and option (A/E). I want to pull in the jobs which are only option A into a new worksheet table. So my first table may have Job No Title Option 1234 Site 01 A 2345 Site 02 E 3456 Site 03 E 4567 Site 04 A My new table should only have: Job No 1234 4567 Kind regards Dylan |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Thanks Max, this kinda does what I want.
Sheet one contains the time people spend on each project and, so it may have two or three people's time to one job number. I need to remove the duplicates in sheet two, so that I have a column showing what jobs have Option E work to be payed. Also, is there a way to sort the job numbers ascending? I'm sorry, I didn't explain this at the beginning. Dylan "Max" wrote: Another way which drives it out auto in another sheet Assume source data as posted in Sheet1's cols A to C, from row2 down In Sheet2, Put in A2: =IF(Sheet1!C2="A",ROW(),"") Leave A1 blank Put in B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) Select A2:B2, copy down to cover the max expected extent of data in Sheet1. Hide away col A. Col B will return the required results, all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote: I have a table with the headings; job number, title, and option (A/E). I want to pull in the jobs which are only option A into a new worksheet table. So my first table may have Job No Title Option 1234 Site 01 A 2345 Site 02 E 3456 Site 03 E 4567 Site 04 A My new table should only have: Job No 1234 4567 Kind regards Dylan |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
I'm afraid I lost you in your expanded scope
.. remove the duplicates in sheet two what are the duplicates in the source table to be removed? .. what jobs have Option E now its option E that you're interested in, not option A? Pl elaborate and illustrate -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote in message ... Thanks Max, this kinda does what I want. Sheet one contains the time people spend on each project and, so it may have two or three people's time to one job number. I need to remove the duplicates in sheet two, so that I have a column showing what jobs have Option E work to be payed. Also, is there a way to sort the job numbers ascending? I'm sorry, I didn't explain this at the beginning. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Job No Option
1234 E 2345 A 3456 E 2345 A 3456 A 4567 E To Avoid Duplicates, so that ... Job No Option 2345 A 3456 A is shown, rather than... Job No Option 2345 A 2345 A 3456 A "Max" wrote: I'm afraid I lost you in your expanded scope .. remove the duplicates in sheet two what are the duplicates in the source table to be removed? .. what jobs have Option E now its option E that you're interested in, not option A? Pl elaborate and illustrate -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote in message ... Thanks Max, this kinda does what I want. Sheet one contains the time people spend on each project and, so it may have two or three people's time to one job number. I need to remove the duplicates in sheet two, so that I have a column showing what jobs have Option E work to be payed. Also, is there a way to sort the job numbers ascending? I'm sorry, I didn't explain this at the beginning. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Ok, I got you. Here's the complete set-up
which will deliver the exact results that you're after Assume source data as below in Sheet1's cols A to C, data from row2 down Job No Title Option 1234 Site 01 E 2345 Site 02 A 3456 Site 03 E 2345 Site 04 A 3456 Site 05 A 4567 Site 06 E etc In Sheet2, In A2: =IF(Sheet1!C2="A",ROW(),"") Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In D2: =IF(B2="","",IF(COUNTIF($B$2:B2,B2)1,"",B2+ROW()/10^10)) Leave D1 blank (This is the additional criteria col to filter out duplicates and prepare for an ascending sort for the final results in cols E and F) In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(B:B,MATCH(SMA LL($D:$D,ROWS($1:1)),$D:$D,0))) Copy E2 to F2. Label E1:F1 as: Job No, Option Select A2:F2, copy down to cover the max expected extent of data in Sheet1. Hide away cols A to D. Cols E & F will return the required results dynamically, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote: Job No Option 1234 E 2345 A 3456 E 2345 A 3456 A 4567 E To Avoid Duplicates, so that ... Job No Option 2345 A 3456 A is shown, rather than... Job No Option 2345 A 2345 A 3456 A |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Dear Max
Thank you for following up on this, I will try it out today. Have a nice week and a merry Christmas - when it comes. Kind regards Dylan "Max" wrote: Ok, I got you. Here's the complete set-up which will deliver the exact results that you're after Assume source data as below in Sheet1's cols A to C, data from row2 down Job No Title Option 1234 Site 01 E 2345 Site 02 A 3456 Site 03 E 2345 Site 04 A 3456 Site 05 A 4567 Site 06 E etc In Sheet2, In A2: =IF(Sheet1!C2="A",ROW(),"") Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!A:A,SMAL L(A:A,ROWS($1:1)))) In C2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(Sheet1!C:C,SMAL L(A:A,ROWS($1:1)))) In D2: =IF(B2="","",IF(COUNTIF($B$2:B2,B2)1,"",B2+ROW()/10^10)) Leave D1 blank (This is the additional criteria col to filter out duplicates and prepare for an ascending sort for the final results in cols E and F) In E2: =IF(ROWS($1:1)COUNT($D:$D),"",INDEX(B:B,MATCH(SMA LL($D:$D,ROWS($1:1)),$D:$D,0))) Copy E2 to F2. Label E1:F1 as: Job No, Option Select A2:F2, copy down to cover the max expected extent of data in Sheet1. Hide away cols A to D. Cols E & F will return the required results dynamically, with all results neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote: Job No Option 1234 E 2345 A 3456 E 2345 A 3456 A 4567 E To Avoid Duplicates, so that ... Job No Option 2345 A 3456 A is shown, rather than... Job No Option 2345 A 2345 A 3456 A |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Creating a list of job numbers, missing out irrelevant items
Welcome, Dylan
Best wishes for a Merry Christmas -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "DDawson" wrote in message ... Dear Max Thank you for following up on this, I will try it out today. Have a nice week and a merry Christmas - when it comes. Kind regards Dylan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find missing numbers in a list | Excel Discussion (Misc queries) | |||
| creating a reconciling list of items not matched between two files | Excel Worksheet Functions | |||
| Find Missing Numbers in a List | Excel Discussion (Misc queries) | |||
| MISSING NUMBERS IN LIST | Excel Discussion (Misc queries) | |||
| How do i identify missing numbers in a sequential list | Excel Discussion (Misc queries) | |||
Linear Mode
