Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
i have integer values in column A where some integers repeat. i want column B to list the unique integers of column A in the order they appear in column A ignoring repeats. So if column A has the integers: 1,4,1,3,3,1,2 I want my formula to give me the integers: 1,4,3,2,0,0,0 Is this possible? Any help would be greatly appreciated! -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
One way, probably the long way (array entered using Control+Shift+Enter).
Assuming your data is in A1:A7 and the formula is entered in cell F1 (change ranges as needed): =IF(ROWS($F$1:F1)SUM(1/COUNTIF($A$1:$A$7,$A$1:$A$7)),0,INDEX($A$1:$A$7,SM ALL(IF((FREQUENCY($A$1:$A$7,$A$1:$A$7)0),ROW(INDI RECT("1:"&ROWS($A$1:$A$7))),""),ROWS($F$1:F1)))) "bridgerbell" wrote: i have integer values in column A where some integers repeat. i want column B to list the unique integers of column A in the order they appear in column A ignoring repeats. So if column A has the integers: 1,4,1,3,3,1,2 I want my formula to give me the integers: 1,4,3,2,0,0,0 Is this possible? Any help would be greatly appreciated! -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
Hi bridgerbell,
You could goto DataFilterAdvanced Filter Check 'copy to another location' Specify the range Specify the destination And click 'Unique records only' You may need to put a name at the top of your column, a single letter will be enough. HTH Martin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
Just another play which can extract the uniques list using non-array formulas
... Data assumed running in A1 down In B1: =IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0))) In C1: =IF(A1="","",IF(COUNTIF($A$1:A1,A1)1,"",ROW())) Select B1:C1, fill down as far as data is expected in col A Col B will return the required results, all neatly bunched at the top -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "bridgerbell" wrote: i have integer values in column A where some integers repeat. i want column B to list the unique integers of column A in the order they appear in column A ignoring repeats. So if column A has the integers: 1,4,1,3,3,1,2 I want my formula to give me the integers: 1,4,3,2,0,0,0 Is this possible? Any help would be greatly appreciated! -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
i like using max's function but it includes zeroes. is there a way to adjust max's function to exclude zeroes? -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
"bridgerbell" wrote:
i like using max's function but it includes zeroes. is there a way to adjust max's function to exclude zeroes? Glad you liked it. Think we could just tweak the criteria formula in col C Try instead in C1, copied down: =IF(OR(A1="",A1=0),"",IF(COUNTIF($A$1:A1,A1)1,"", ROW())) (No change to formulas in col B) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
i actually figured out how to exclude zeroes after i posted but couldn't load the forum for some reason to remove my question. but, i have one more question: i have a header row, so my first entries are in row 2. your formulas only work if my entries start in row 1. if i put your formulas in my spreadsheet with the header row, i get the second number i want at the top instead of the first. how can i adjust the formula to account for the numbers starting in row 2? THANKS MUCH! -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
"bridgerbell" wrote:
i actually figured out how to exclude zeroes after i posted but couldn't load the forum for some reason to remove my question. Do not edit after you have posted. If you wish to clarify your own earlier post, just post again as a reply to it. That way, your clarifications will appear and be visible to all readers out there, not just to readers of excelforum. but, i have one more question: i have a header row, so my first entries are in row 2. your formulas only work if my entries start in row 1. if i put your formulas in my spreadsheet with the header row, i get the second number i want at the top instead of the first. how can i adjust the formula to account for the numbers starting in row 2? If source data in col A starts in A2 down ... In B2: =IF(ROW(A1)COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C: C,ROW(A1)),C:C,0))) [ Same formula as previous. No change. ROW(A1) is always used in the starting formula cell, irrespective of the row that the cell is in. ROW(A1) is used as an incrementer when we copy down. It simply produces the number series: 1,2,3 ... for the SMALL to pick off ] In C2: =IF(OR(A2={"",0}),"",IF(COUNTIF($A$2:A2,A2)1,"",R OW())) [ OR(A2={"",0}) is just a shorter rendition of: OR(A2="",A2=0) ] Then just select B2:C2, fill down as far as data is expected in col A -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
bridgerbell wrote...
i have integer values in column A where some integers repeat. i want column B to list the unique integers of column A in the order they appear in column A ignoring repeats. So if column A has the integers: 1,4,1,3,3,1,2 I want my formula to give me the integers: 1,4,3,2,0,0,0 .... With your original data in A1:A7, you could try the following array formulas in B1:B7. B1 [array formula]: =INDEX(A1:A7,MATCH(TRUE,A1:A7<0,0)) B2 [array formula]: =IF(OR(COUNTIF(B$1:B1,A2:A$7)=0), INDEX(A2:A$7,MATCH(1,(A2:A$7<0)*(COUNTIF(B$1:B1,A 2:A$7)=0),0)),0) Fill B2 down into B3:B7. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
this has been a great help thank you all. i am well on my way now. -- bridgerbell ------------------------------------------------------------------------ bridgerbell's Profile: http://www.excelforum.com/member.php...o&userid=36092 View this thread: http://www.excelforum.com/showthread...hreadid=558725 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
1,4,1,3,3,1,2=1,4,3,2
"bridgerbell" wrote:
this has been a great help thank you all. i am well on my way now. Good to hear that. Bon voyage ! <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|