If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Distinct list formula
Does anyone know of a formula I could use to compile a distinct list of entries from another column. I know this could be done with the advanced filter, and I know it could be done with VBA, but I need to do it with a formula. Likewise, I can't use a pivot table because then the data could be changed. I have to pull a distinct list and their correponding values from some static data so it can be graphed. Thanks -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
#2
|
|||
|
|||
Distinct list formula
When you say distinct list of entries I'm assuming you mean unique records.
One way to do this would be with a helper column. If the data which distinguishes one row from another is in column A, then the helper formula would be =IF(A1=A2,1,"") assuming the data table is sorted by column A. If you provide more information about the data you're trying to sort I could make this explanation more concrete. "summergs" wrote: Does anyone know of a formula I could use to compile a distinct list of entries from another column. I know this could be done with the advanced filter, and I know it could be done with VBA, but I need to do it with a formula. Likewise, I can't use a pivot table because then the data could be changed. I have to pull a distinct list and their correponding values from some static data so it can be graphed. Thanks -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
#3
|
|||
|
|||
Distinct list formula
B1: =A1
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"", INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),MATCH( 0,COUNTIF(B$1:B1,$A$1:$A$2 0&""),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. copy B2 down -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "summergs" wrote in message ... Does anyone know of a formula I could use to compile a distinct list of entries from another column. I know this could be done with the advanced filter, and I know it could be done with VBA, but I need to do it with a formula. Likewise, I can't use a pivot table because then the data could be changed. I have to pull a distinct list and their correponding values from some static data so it can be graphed. Thanks -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
#4
|
|||
|
|||
Distinct list formula
See Chip Pearson's site topic "Extracting Unique Entries"
"summergs" wrote: Does anyone know of a formula I could use to compile a distinct list of entries from another column. I know this could be done with the advanced filter, and I know it could be done with VBA, but I need to do it with a formula. Likewise, I can't use a pivot table because then the data could be changed. I have to pull a distinct list and their correponding values from some static data so it can be graphed. Thanks -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
#5
|
|||
|
|||
Distinct list formula
Sorry, yes I'm looking for Unique entries I was saying distinct as in SQL "select distinct...". Here's a mock up of what I'm dealing with. I dump data into a worksheet and then slice and dice it lots of different ways, but I have static information such as Apples Oranges Grapes Melon Apples Grapes Cherries Apples So I want to change this into: Apples Oranges Grapes Melon Cherries I quickly tried Bob's array formula and it seemed to work, though if the data in column A was seen as a dupe column B was blank, leaving me with gaps. I can probably clean this up by combining the info into column C but i'll get to that. I'm just looking for other ways to accomplish this. Also, if someone knows of an article please include the link. I'm been searching for the article suggested by Evan but the search functionality on the website leaves a little to be desired. Thanks again. -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
#6
|
|||
|
|||
Distinct list formula
Bob's formula works just fine for me. The only time it leaves a blank is if
all the uniques have been extracted (these blanks are at the end of the range by design) OR....if there is/are empty cells within the original list. Biff "summergs" wrote in message ... Sorry, yes I'm looking for Unique entries I was saying distinct as in SQL "select distinct...". Here's a mock up of what I'm dealing with. I dump data into a worksheet and then slice and dice it lots of different ways, but I have static information such as Apples Oranges Grapes Melon Apples Grapes Cherries Apples So I want to change this into: Apples Oranges Grapes Melon Cherries I quickly tried Bob's array formula and it seemed to work, though if the data in column A was seen as a dupe column B was blank, leaving me with gaps. I can probably clean this up by combining the info into column C but i'll get to that. I'm just looking for other ways to accomplish this. Also, if someone knows of an article please include the link. I'm been searching for the article suggested by Evan but the search functionality on the website leaves a little to be desired. Thanks again. -- summergs ------------------------------------------------------------------------ summergs's Profile: http://www.excelforum.com/member.php...o&userid=37419 View this thread: http://www.excelforum.com/showthread...hreadid=571901 |
Thread Tools | |
Display Modes | |
|
|