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