Thread: Dynamically copy only unique values to new datasheet?

PLEASE HELP!!!

I have tried to figure this out for days.

Background info:
I have a datasheet which contains my inventory of womens shoes. On this sheet I enter ALL of my shoes, but some are partial duplicates. I.e. The same brand/style (but different sizes). I would like only the unique shoes (brands and style) to be copied to a new datasheet.

Here is a simpliflied example.
SHEET 1
1
2
3
3
3
4
5


As you can see the number 3 appears 3 times. I would like a new datasheet to show only:
SHEET 2
1
2
3
4
5

I was able to do this via the remove duplicates and via a basic macro, but what I CAN NOT DO is have the list update dynamically.

I would love to do this via a formula. I WOULD really appreciate any help.

Hi

See if the attached example gives you some help.

rylo

Thank you for taking the time to answer me. That solution works well, but I really wanted to have a formula that would remove the blank cells.

After scouring the internet, I found a posting that works well.

Here is an array formula that works great:



"....Hi Leo.
It's been very hot over here, for this time of year. But, we have a saying
"Never cast a cloot till May is oot"..... so be warned..<bg>

Adding to your post, you can use Conditional Formatting to highlight the
unique entries in a column.
Selecting the column of cells, and using :

<Formula is> =COUNTIF($A$2:A2,A2)=1

A single array formula to give a list of unique entries ( no blank cells in
range ) , and with the range named "list". Copy formula down until #NUM
error is returned :

=INDEX(List,SMALL(IF(MATCH(List,List,0)=ROW(INDIRECT("1:"&ROWS(List))),ROW(
List)-1,""),ROW(1:1)))

The #NUM error can also be hidden by Conditional Formatting:

<Formula is> =ISERROR(A2) Font color, same as background color.

All the Best

George

Newcastle upon Tyne
England.

Leo Heuser wrote in message ...
> Hello NG

> It surely was the wonderful sunny weather, we had today in Denmark, that

caused the idea to pop up :-)
> The formula will give a list with no blanks in between of the unique

entries in a column (or row).

> Assuming O3:O200 is the range of entries.
> In any cell e.g. V5 enter this formula =O3
> In V6 enter this formula:

=OFFSET($O$3,MAX((COUNTIF($V$5:V5,$O$3:$O$200)=0)*(ROW($O$3:$O$200)-ROW($O$
3))),0)

> The formula is an array formula and must be entered with

<Shift><Ctrl><Enter> instead of <Enter>,
> also if edited at a later time. If entered correctly, Excel will show the

formula in the formula bar
> enclosed in braces { }. Do not enter these braces yourself.

> Drag V6 down as long as necessary with the fill handle (the little square

in the lower right corner of the cell)
> You now have a list of all the unique entries in O3:O200. Somewhere down

the list there is a "row" of cells containing the value

- Hide quoted text -
- Show quoted text -
> of O3. This "row" can be deleted.

> If O3:O200 contains empty cells, a zero is displayed in the list.

> I hope, you will find the formula useful.
> Best regards
> LeoH


Reply Reply to author Forward



You must Sign in before you can post messages.
To post a message you must first join this group.
Please update your nickname on the subscription settings page before posting.
You do not have the permission required to post.


Leo Heuser
View profile

=OFFSET($O$3,MIN(IF(COUNTIF($V$4:V4,O3:$O$200)=0,(ROW(O3:$O$200)-ROW($O$3)))),0)

Please notice the use of mixed absolute ($$) and relative adressing.
The formula is inserted in V5 and dragged down. V4 must be empty or containing data, which is
not in the range O3:O200.

LeoH

Leo Heuser skrev i meddelelsen ...