| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
compile list
I have a column c2:c294 that contains Job names. There may be identical
entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Copy down until you get blanks Biff "smonsmo" wrote in message ... I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
I entered c1 in each of the rng. Is that correct? If so, when I copy down,
it only works on the first line. I assume I did something wrong. help "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Copy down until you get blanks Biff "smonsmo" wrote in message ... I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
In the formula, rng refers to your range C2:C294.
I have a column c2:c294 that contains Job names. The formula *MUST* be entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "smonsmo" wrote in message ... I entered c1 in each of the rng. Is that correct? If so, when I copy down, it only works on the first line. I assume I did something wrong. help "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Copy down until you get blanks Biff "smonsmo" wrote in message ... I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
Do I also need to change $1:1 to something else. I recieve a column of
blanks now. "T. Valko" wrote: In the formula, rng refers to your range C2:C294. I have a column c2:c294 that contains Job names. The formula *MUST* be entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "smonsmo" wrote in message ... I entered c1 in each of the rng. Is that correct? If so, when I copy down, it only works on the first line. I assume I did something wrong. help "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Copy down until you get blanks Biff "smonsmo" wrote in message ... I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
Do I also need to change $1:1 to something else.
No. Replace rng with your actual range or create the defined name rng and use it. =IF(ROWS($1:1)<=SUM((C$2:C$294<"")/COUNTIF(C$2:C$294,C$2:C$294&"")),INDEX(C$2:C$294,S MALL(IF(C$2:C$294<"",IF(ROW(C$2:C$294)-MIN(ROW(C$2:C$294))+1=MATCH(C$2:C$294,C$2:C$294,0) ,ROW(C$2:C$294)-MIN(ROW(C$2:C$294))+1)),ROWS($1:1))),"") =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Biff "smonsmo" wrote in message ... Do I also need to change $1:1 to something else. I recieve a column of blanks now. "T. Valko" wrote: In the formula, rng refers to your range C2:C294. I have a column c2:c294 that contains Job names. The formula *MUST* be entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Biff "smonsmo" wrote in message ... I entered c1 in each of the rng. Is that correct? If so, when I copy down, it only works on the first line. I assume I did something wrong. help "T. Valko" wrote: Try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"") Copy down until you get blanks Biff "smonsmo" wrote in message ... I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
I cant seem to get this formula to work past the first row.
"smonsmo" wrote: I have a column c2:c294 that contains Job names. There may be identical entries. I need to compile a list of all entries without duplicates or blank cells. This will need to be done automatically. Data sort or a macro are the obvious choices however in this case will not serve. Any Ideas? Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
compile list
Biffs formula works perfectly. Press F2 to edit the first formula, enter as
array (ctrl + Shift + Enter) and drag down If this still does not work look at you original data is the job# exactly the same on each row? Regards Peter "smonsmo" wrote: I cant seem to get this formula to work past the first row. "smonsmo" wrote: |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic Update of Dropdown List Box data | Excel Worksheet Functions | |||
| Drop-Down List Choice Affects Multiple Cells? | Excel Worksheet Functions | |||
| Editing a list of data | Excel Discussion (Misc queries) | |||
| auto updating list | Excel Worksheet Functions | |||
| Refresh a Validation List? | Excel Discussion (Misc queries) | |||
Linear Mode
