How to collect unique rows only using formulas?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

orbii - 29 Dec 2006 01:02 GMT

Hi, is there any other ways of collecting unique data (anything, not just
numbers) from a verti column and transpose it to hori row?

example

from
qwerty1
qwerty1
qwerty2
qwerty2
qwerty2
qwerty3
qwerty3

to
qwerty1 qwerty2 qwerty3

in php there's ways for me to do it through arrays' functions. but i want to
avoid using vba because i also want to be able to use autofill to do the
job. can it be done?

i've tried adding another column to the left and using an
if(r1=r2,,count(r$1:r1)+1), this would create a index like skipping all the
dub rows. then using vlookup to transpose it. it works, but for what i
want to do, it might create a lot more columns for nothing.

thanks in advance, orbii

Reply to this Message

Martin Fishlock - 29 Dec 2006 03:41 GMT

Hi Orbii,

Here are two possible solutions:

1. Use the transpose function is an array function.
So if you data is in A2:A4 you select B1:D1 and enter =transpose(A2:A4) and
then press ctrl+shft+enter.

2. Copy paste special values with the transpose check box selected.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> Hi, is there any other ways of collecting unique data (anything, not just
> numbers) from a verti column and transpose it to hori row?
[quoted text clipped - 23 lines]
>
> thanks in advance, orbii

Reply to this Message

Martin Fishlock - 29 Dec 2006 04:01 GMT

Sorry, I miss read the question.

The only way I know outside a VBA macro is to use auto filter advanced and
click the unique records only. Then you have to transpose them as before.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> Hi Orbii,
>
[quoted text clipped - 33 lines]
> >
> > thanks in advance, orbii

Reply to this Message

Don Strachan - 29 Dec 2006 07:17 GMT

> Martin Fishlock <martin_fishlock@yahoo.co.uk.cutthis>

Hi Martin

Try select the vertical column of cells
Then Edit.Paste special and click the transpose button

Cheers
Don Strachan

For some 10,000 NZ and international Automotive, Alternate Fuel and Motorsport Links and information.

Dons Auto Pages: <http://www.donsautopages.co.nz/>
Email: <don@NOSPAMdonsautopages.co.nz>

Reply to this Message

Martin Fishlock - 29 Dec 2006 09:40 GMT

Don I suggested that originally. I miss read the request and the op wanted
unique items only and edit paste special transpose will not give unique.

so you have to filter first.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> > Martin Fishlock <martin_fishlock@yahoo.co.uk.cutthis>
>
[quoted text clipped - 10 lines]
> Dons Auto Pages: <http://www.donsautopages.co.nz/>
> Email: <don@NOSPAMdonsautopages.co.nz>

Reply to this Message

orbii - 29 Dec 2006 09:50 GMT

that's what i'm trying to avoid, that means each time the list gets updated
w/ about 4000 more records a day, i'ma have to keep recollecting via
copy/paste... which is a bit humbug.

i want to avoid using vba because those columns tends to be changed a lot.
and that is why formula works better. if column or rows gets moved, the
autofill will automaticly changes the cells.row/column.

i wish excel team would expand on the arrays and indexing functions, which
would make excel even more powerful. but then they'll tell you, why dont'
you just use access... then i'd say, i'd love to, but how am i going to
convince 50 other people?

aloha, orbii

> Don I suggested that originally. I miss read the request and the op wanted
> unique items only and edit paste special transpose will not give unique.
[quoted text clipped - 16 lines]
>> Dons Auto Pages: <http://www.donsautopages.co.nz/>
>> Email: <don@NOSPAMdonsautopages.co.nz>

Reply to this Message

Bob Phillips - 29 Dec 2006 10:23 GMT

Assuming that your data is on Sheet1, A1:A20, then on sheet2

A1: =Sheet1!A1
B1: ==IF(ISERROR(MATCH(0,COUNTIF($A1:A1,Sheet1!$A$1:$A$20&""),0)),"",
INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1:$A$20),MATCH(0,COUNTIF($A1:A1,Sheet1!$A$1:$A$20&""),0)))

B1 is an array formula, just copy it across as far as you need.

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

> that's what i'm trying to avoid, that means each time the list gets
> updated w/ about 4000 more records a day, i'ma have to keep recollecting
[quoted text clipped - 32 lines]
>>> Dons Auto Pages: <http://www.donsautopages.co.nz/>
>>> Email: <don@NOSPAMdonsautopages.co.nz>

Reply to this Message

bplumhoff@gmail.com - 30 Dec 2006 06:59 GMT

Hello,

I suggested a short (and quite simple - I hope) formula which uses a
(not too complex and well-tested (ok, but not guaranteed)) UDF.

If you want to "die" in complexity then take a complex
TRANSPOSE(INDEX/OFFSET(1/COUNTIF()))) approach. You mentioned that
changes of the result area are to be expected, I think.

SCNR,
Bernd

Reply to this Message

bplumhoff@gmail.com - 30 Dec 2006 06:51 GMT

Hello,

An approach WITH VBA:

If your values are in Sheet1!A1:A20, then select some adjacent cells in
a ROW and enter as array-formula(with CTRL + SHIFT + ENTER):
=TRANSPOSE(List_Freq(Sheet1!A1:A20;1))

The UDF List_Freq you can find here:
http://www.sulprobil.com/html/listfreq.html

Regards,
Bernd

Reply to this Message

Rate this thread: