Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summarising a range in a column into a separate column
This assumes original list is in B2:B12, adjust accordingly
In two columns immediately next to your list, enter (adjusting for location) In C2, enter: =IF(COUNTIF($B$2:B2,B2)=1,B2,"") Copy formula down. This blanks out duplicates In D2 enter: {=IF(ROW()-ROW($D$2:$D$12)+1ROWS($C$2:$C$12)-COUNTBLANK($C$2:$C$12),"",INDIRECT(ADDRESS(SMALL(( IF($C$2:$C$12<"",ROW($C$2:$C$12),ROW()+ROWS($C$2: $C$12))),ROW()-ROW($D$2:$D$12)+1),COLUMN($C$2:$C$12),4)))} Copy formula down. This moves unique entries to top. Note: 2nd formula is an array formula Finally, hide column D. "Aidan" wrote: If I have a column with say car, boat, train, other I want to generate a separate column that only shows one incidence of each of the words (the words appearing one below the other). I'd appreciate any help on this. Thanks, Aidan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF: 2 criteria: Date Range Column & Text Column | Excel Worksheet Functions | |||
Summarising a column of numbers | Excel Discussion (Misc queries) | |||
linking one column or varying range to another column using same f | Excel Worksheet Functions | |||
EXCEL Sum column based on time range in different column? | Excel Worksheet Functions | |||
CountIf first column range = "Word" and second column range <> 0 | Excel Worksheet Functions |