How to collect unique rows only using formulas?

O

orbii

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
 
M

Martin Fishlock

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.
 
M

Martin Fishlock

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.
 
M

Martin Fishlock

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.
 
O

orbii

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
 
B

Bob Phillips

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.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
 
B

bplumhoff

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
 
B

bplumhoff

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
 
Top