Display Unique Values

[S Davis]

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!

 

[S Davis]

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.

 

[Ron Coderre]

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 special>Check: Values and click [OK]

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[S Davis]

Yes.... that's actually quite amazing

Thanks!

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 special>Check: Values and click [OK]

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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!

 

[Pete_UK]

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

Yes.... that's actually quite amazing

Thanks!

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 special>Check: Values and click [OK]

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

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!