Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Unique Values
I know we've covered this already many many times.
Id like to do this with a twist: I have a list of Data (may have seen another thread of mine) with unique values. Say for example Dog Cat .. Mouse .. .. .. Kangaroo .. .. .. .. .. Bear Giraffe .. Donkey .. ....Etc. ('.'s represent blank rows) There is a lot of data out to the right side, and this data is listed with one heading (ie. 'Kangaroo') to represent all of it until the next heading. As I want to break this down, I want to list 'Kangaroo' beside each row of data until the next listing (in this case, Bear), and so on, to fill up all the blank rows. Many thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Unique Values
I swear, posting in here clears my head - I always answer my own
questions. For future reference, displaying unique values in this method just requires a combination of an if statement and the offset function. Use offset to display the row above and copy down, with the if statement filling in the title cells. S Davis wrote: I know we've covered this already many many times. Id like to do this with a twist: I have a list of Data (may have seen another thread of mine) with unique values. Say for example Dog Cat . Mouse . . . Kangaroo . . . . . Bear Giraffe . Donkey . ...Etc. ('.'s represent blank rows) There is a lot of data out to the right side, and this data is listed with one heading (ie. 'Kangaroo') to represent all of it until the next heading. As I want to break this down, I want to list 'Kangaroo' beside each row of data until the next listing (in this case, Bear), and so on, to fill up all the blank rows. Many thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Unique Values
Try this:
Select from the first category down through the last relevant blank cell Press the [F5] key......(that's the shortcut for <edit<goto) Click the [special cells] button Check: blanks Click the [OK] button (that will select all of the blank cells in that range) WHILE THOSE CELLS ARE STILL SELECTED... Type: = Press the UP ARROW one time Hold down the [ctrl] key and press the [enter] key (that will put formulas in each blank cell that refer to the cell above) Select the whole range of categories (hardcoded and formulas) <edit<copy <edit<paste specialCheck: Values and click [OK] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "S Davis" wrote: I know we've covered this already many many times. Id like to do this with a twist: I have a list of Data (may have seen another thread of mine) with unique values. Say for example Dog Cat .. Mouse .. .. .. Kangaroo .. .. .. .. .. Bear Giraffe .. Donkey .. ....Etc. ('.'s represent blank rows) There is a lot of data out to the right side, and this data is listed with one heading (ie. 'Kangaroo') to represent all of it until the next heading. As I want to break this down, I want to list 'Kangaroo' beside each row of data until the next listing (in this case, Bear), and so on, to fill up all the blank rows. Many thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Unique Values
Yes.... that's actually quite amazing:)
Thanks! Ron Coderre wrote: Try this: Select from the first category down through the last relevant blank cell Press the [F5] key......(that's the shortcut for <edit<goto) Click the [special cells] button Check: blanks Click the [OK] button (that will select all of the blank cells in that range) WHILE THOSE CELLS ARE STILL SELECTED... Type: = Press the UP ARROW one time Hold down the [ctrl] key and press the [enter] key (that will put formulas in each blank cell that refer to the cell above) Select the whole range of categories (hardcoded and formulas) <edit<copy <edit<paste specialCheck: Values and click [OK] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "S Davis" wrote: I know we've covered this already many many times. Id like to do this with a twist: I have a list of Data (may have seen another thread of mine) with unique values. Say for example Dog Cat .. Mouse .. .. .. Kangaroo .. .. .. .. .. Bear Giraffe .. Donkey .. ....Etc. ('.'s represent blank rows) There is a lot of data out to the right side, and this data is listed with one heading (ie. 'Kangaroo') to represent all of it until the next heading. As I want to break this down, I want to list 'Kangaroo' beside each row of data until the next listing (in this case, Bear), and so on, to fill up all the blank rows. Many thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display Unique Values
And now that you have them all filled in, if you want the display to
look like it was before then you can apply conditional formatting. Highlight all the cells except for the first one - assume this is A1. Click Format | Conditional Formatting and then "Cell Value is", then "Equal to", and "=A1" (you type =A1). Click the Format button and choose white as the colour, then OK your way out. This only works if they are in sequence, so it is not truly detecting uniques. Hope this helps. Pete S Davis wrote: Yes.... that's actually quite amazing:) Thanks! Ron Coderre wrote: Try this: Select from the first category down through the last relevant blank cell Press the [F5] key......(that's the shortcut for <edit<goto) Click the [special cells] button Check: blanks Click the [OK] button (that will select all of the blank cells in that range) WHILE THOSE CELLS ARE STILL SELECTED... Type: = Press the UP ARROW one time Hold down the [ctrl] key and press the [enter] key (that will put formulas in each blank cell that refer to the cell above) Select the whole range of categories (hardcoded and formulas) <edit<copy <edit<paste specialCheck: Values and click [OK] Is that something you can work with? *********** Regards, Ron XL2002, WinXP "S Davis" wrote: I know we've covered this already many many times. Id like to do this with a twist: I have a list of Data (may have seen another thread of mine) with unique values. Say for example Dog Cat .. Mouse .. .. .. Kangaroo .. .. .. .. .. Bear Giraffe .. Donkey .. ....Etc. ('.'s represent blank rows) There is a lot of data out to the right side, and this data is listed with one heading (ie. 'Kangaroo') to represent all of it until the next heading. As I want to break this down, I want to list 'Kangaroo' beside each row of data until the next listing (in this case, Bear), and so on, to fill up all the blank rows. Many thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list unique values in a column | Excel Worksheet Functions | |||
Populate combo box with unique values only | Excel Discussion (Misc queries) | |||
AutoFilter lists unique values | Excel Discussion (Misc queries) | |||
Modifying a Formula To display only Unique Values | Excel Worksheet Functions | |||
Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions |