How to collect unique rows only using formulas?

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 


0
orbii (38)
12/29/2006 1:02:13 AM
excel 39879 articles. 2 followers. Follow

8 Replies
632 Views

Similar Articles

[PageSpeed] 53

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.

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


"orbii" wrote:

> 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 
> 
> 
> 
0
12/29/2006 3:41:01 AM
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.
-- 
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Martin Fishlock" wrote:

> 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.
> 
> -- 
> Hope this helps
> Martin Fishlock, Bangkok, Thailand
> Please do not forget to rate this reply.
> 
> 
> "orbii" wrote:
> 
> > 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 
> > 
> > 
> > 
0
12/29/2006 4:01:01 AM
On Thu, 28 Dec 2006 20:01:01 -0800, Martin Fishlock
<martin_fishlock@yahoo.co.uk.cutthis> wrote:

> 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>
0
don7989 (4)
12/29/2006 7:17:33 AM
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.

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


"Don Strachan" wrote:

> On Thu, 28 Dec 2006 20:01:01 -0800, Martin Fishlock
> <martin_fishlock@yahoo.co.uk.cutthis> wrote:
> 
> > 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>
> 
0
12/29/2006 9:40:01 AM
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

"Martin Fishlock" <martin_fishlock@yahoo.co.uk.cutthis> wrote in message 
news:294E294B-2C89-416D-BD27-4A933708E0A8@microsoft.com...
> 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.
>
> -- 
> Hope this helps
> Martin Fishlock, Bangkok, Thailand
> Please do not forget to rate this reply.
>
>
> "Don Strachan" wrote:
>
>> On Thu, 28 Dec 2006 20:01:01 -0800, Martin Fishlock
>> <martin_fishlock@yahoo.co.uk.cutthis> wrote:
>>
>> > 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>
>> 


0
orbii (38)
12/29/2006 9:50:39 AM
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)


"orbii" <orbii@hotmail.com> wrote in message 
news:egHzY7yKHHA.2028@TK2MSFTNGP03.phx.gbl...
> 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
>
> "Martin Fishlock" <martin_fishlock@yahoo.co.uk.cutthis> wrote in message 
> news:294E294B-2C89-416D-BD27-4A933708E0A8@microsoft.com...
>> 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.
>>
>> -- 
>> Hope this helps
>> Martin Fishlock, Bangkok, Thailand
>> Please do not forget to rate this reply.
>>
>>
>> "Don Strachan" wrote:
>>
>>> On Thu, 28 Dec 2006 20:01:01 -0800, Martin Fishlock
>>> <martin_fishlock@yahoo.co.uk.cutthis> wrote:
>>>
>>> > 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>
>>>
>
> 


0
bob.NGs (282)
12/29/2006 10:23:06 AM
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

0
bplumhoff1 (208)
12/30/2006 6:51:54 AM
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

0
bplumhoff1 (208)
12/30/2006 6:59:04 AM
Reply:

Similar Artilces:

Formula please
Hi, hope someone can help. I am trying to get a value in dollars in one cell dependant on another cell being one of two letters. If a particular letter appears then the cell will give the result of a third cell multiplied by 9%, if it is a different letter it will give 9% of another cell. eg the result will be in K30 if K15 is "T" then k30 will be p20*9%, but if K15 is "P" then K30 will be P21*9% Thank you if anyone can help. Mel One way: =IF(K15="T", P20*9%, IF(K15="P",P21*9%,"Not T or P")) In article <eM1rjLTaGHA.3328@TK2M...

Using business card templates in Publisher 2007
I created many business cards in Publisher 2003 and never had a problem with using my Card Mate to cut them. When I use 2007, it is never correct, no matter which template I use. I truly miss that feature with 2003. Can someone tell me how to format 2007 so that I use my Card Mate cutter? Thanks. AB wrote: > I created many business cards in Publisher 2003 and never had a > problem with using my Card Mate to cut them. When I use 2007, it is > never correct, no matter which template I use. I truly miss that > feature with 2003. Can someone tell me how to format 2007 so that I >...

Selecting Rows for Copying
Hi, I've been happily using a macro that has been copying and pasting data for me. However, I have recently encountered an issue where the sheets I am copying my data from have merged cells. As far as I can tell, Selection.Rows.Count will not work as desired when some columns are merged, but others are not. Basically it is counting what may be 100 rows of data as one row, due to the cell merging. ' select all lines except title Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select Is there another way to select all of the data, excluding the header row, whe...

Using a resource (conference room)
I am trying to set up a resource so we can schedule use of our conference room for meetings. I have the free/busy time working for all the people but for the life of me, can not decipher from Outlook help how to actually get the resource in place. I am not running an Exchange server, is this required ? I hope not. Once set up, I figured it shouldn't have to be managed. Help ? Thanks, Doug ...

XmlSerializer Collection with Collections
Hello I got this working but it is not how I really want it, basically I have an xml file which has a root of <test> and can be filled with 3 different types of <question> elements with different attributes, all share a base set of 4, one of the question types can have children with <option> elements, this is how the xml looks after serialization.... If you notice there is an extra <SelectionList> around the <option>'s in the final <question>, how can I write my class so I don't have this extra <SelectionList> element....my class follows, you ...

Complicated formula
I have an Excel sheet with probably 1200 cells. Each cell may contain a character which represents a numerical value (ex. P = .75, R - .50, etc.). Certain letters represent a higher value, and I want the higher of the two values. How can I compare every cell and arrive at a number? Thnaks try =IF(CODE(C10)>CODE(C11),C10,C11) or =IF(upper(CODE(C10))>upper(CODE(C11)),C10,C11) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "A. Stern" <aleSternman33@cox.net> wrote in message news:ZzgNh.188$786.172@newsfe03.lga... >I have an Excel sheet with probably...

Unique Random Number Generator
I'm looking for a macro that will generate a unique Random number between 1-2000. The random aspect is obviously not the problem, the unique requirement is whats got me stumped. Thanks Tom. What about using RANDBETWEEN function? It is not a macro but it appears to suit your need. RANDBETWEEN(bottom,top) Bottom is the smallest integer RANDBETWEEN will return. Top is the largest integer RANDBETWEEN will return. Example 1 2 3 A B Formula Description (Result) =RANDBETWEEN(1,100) ...

Need a little help on this formula
I'm trying to use the following formula to find the average an array of data using an ID as a criteria. The problem is, the array I'm pulling data from has some fields that are blank. My average function is including those cells in the calculation instead of ignoring. Here's my formula: {=AVERAGE(IF($B2='Total Transactions'!$C$2:$C$205,'Total Transactions'!$E$2: $Q$205,"Not Found"))} As an example, the sum of one of my rows of data is 5,367 total transactions over 7 months. That should work out to 767 transactions per month. Instead, the formul...

easy to use templates
Hi We have outlook templates that people fill in each morning and send to a distribution list. But they are not that easy to fill out as you have to place the curser in the proper spot to type then do that throughout the entire document. It is easy to overlook items. Is there a way to set up a template that you can just hit tab or enter and it automatically goes to the next area that you need to type information? -- Thank-you! Ruth What exactly are you using now? In other words, what do you mean by "templates"? Also, do you meet the prerequisites for using...

about Collections
Hi! The ArrayList support the ICollection and IEnumerable. Below is three ways to iterate through a collection of an instance of an ArrayList object. I just find it's easier to iterate though a collection by using the for loop or the foreach so my question is when is it better or more appropriate to use the Enumerator. static void Main(string[] args) { ArrayList list = new ArrayList(); list.Add(1); list.Add("hej"); //Using the foor loop for (int i = 0; i < list.Count; i++) { Console.WriteLine(li...

Formula to find last different value in a column?
I have a formula that is based on comparing the last value in a column. Basically of not null it performs an action. However I need the ones after that to be able to set up and find the last different value e.g. A B B B My formula so far is if Value<>"" then do X I even got it to say If Value<>"" AND Value not equal to prior record. Which would work for the second B But what I really need it to say (to get to A) is If Value <>"" and Value IS Equal to Last Record step backwards until you find the value of the first differe...

My BackUp wants a password...but I don't use one...
I use Money 2001 and have always religiously backed-up. I have now got a new machine and want to transfer all my money records. To restore backup it asks me for a password. I have never used one. I've tried setting one on my old machine but it still will not recognise it. It's asking for a password that doesn't exist. Any thoughts at all? Thanks. That error message usually means that the .mny/.mbf files were created by a later version than is currently loaded. By the way if you have a CD or memory stick, use that to copy the .mny file. You don't need to use restore. -...

Fill rows with column information
When a reference to a cell is included in another cell and the user drags the handle to fill adjacent cells Excel increments the column if the user drags down the column or increments the row if the user drags across the row. E.g. cell G2 has =A1 If the fill handle of G2 is dragged along column G then Excel increments the formula to =A2, =A3, =A4 e.t.c. If the fill handle of G2 is dragged along row 2 then Excel increments the formula to =B1, =C1, =D1 e.t.c. Is there a way to drag the fill handle along the row and have Excel increment the column? Or vice versa? Tim Yes. Right from my boo...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

How can I combine data that is in 2 rows to 1 row
Hello I have a file that has only 1 data point (name of an institution) in row 1 column 1 and then multiple data points (city of the institution and other results) in row 2 column 1-7. I would like to combine the data in consecutive rows into 1 so I can work (sort results in different columns) with the data. As you can imagine the file has 2000 rows but the data addresses only 1000 institutions. I Would appreciate any help thanks albert explained : > Hello > > I have a file that has only 1 data point (name of an institution) in row 1 > column 1 and then multiple data points...

Restrict Users to use Bcc
Hi, Sorry previous post by mistake posted and was incomplete I want to restrict users from sending Bcc. We use Ms Outlook 2000. I tried using Office Resource Kit System Policy Editor but doesn't work. in system policy editor : 1) I applied Default User - Ms Oulook 2000-Disable Items in User Interface-Predfine-Checked:Disable Command Bar Buttons & Menu Items-Checked:Mail Item-view|Bcc field 2) Saved as NTconfig.pol 3) On Mail server in Netlogon Folder I copied this file But still can't block/disbale Bcc Regards, Tanweer ...

Dropdown cell containing only the unique values from a column
Hi there, I have been looking for an answer on this one for some time now: How can I create a Dropdown cell containing only the unique values from a column (without using extra cells, but using vba is ceratinly an option). Is it maybe an idea to create a module/macro that can filter a list and return the unique items separated by a semi-colon? thx, Seansan Hi quite difficult without using a helper column. You may use the following function which returns an array of unique values for a specified range (posted by Myrna Larson some time ago): Function MakeArray(rng As Range) As Variant ...

using gdi in mobile
Hi, I'm using visual studio 2005 to draw the screen on mobile device . I'm looking for some smaple in using gdi. Is there anyone could provide some simple MFC sample that is using GDI? Thanks!! There should samples in the mobile sdk. Did you have a look at them? -- Vipin Aravind Microsoft - MVP "Amy" <Amy@discussions.microsoft.com> wrote in message news:212FF0D6-D9DF-436C-9B1D-F13D44CE59E5@microsoft.com... > Hi, > > I'm using visual studio 2005 to draw the screen on mobile device . I'm > looking for some smaple in using gdi. Is there anyon...

XML Serialization and custom collections
I am getting XML from a dataset that has been populated via a storedprocedure no schemas have been applied nor any relationships. But the correct table names have been given to match the table(s) equivalent business object(s). I have a custom collection that implements ICollection and IEnumerable. Here is the collection: Imports ERP.BusinessObjects Imports ERP.BusinessObjects.Helpdesk Imports ICTObjects.Collections Imports System.Xml.Serialization Namespace Collections <Serializable()> _ Public Class BusinessObjectCollection : Implements ICollection, IEnumerable ...

Message Rules using <or>
Hello, How do I create a rule that deals with messages sent from certain people OR (rather than AND) sent to a specific one of my addresses? Bob Bob Brannon <bbran@nowhere.com> wrote: > How do I create a rule that deals with messages sent from certain > people OR (rather than AND) sent to a specific one of my addresses? Two rules. -- Brian Tillman ...

Named range using conditional formatting #2
Hi, I have a named cell called Econvert which contains the conversion rate from Dollars to Euros. I am creating a template for others to use and I need to add a conditional format for when users forget to divide their inputs by the Econvert. For example, if they enter a salary number as $40,000 I want them to divide this number by Econvert so that the end result is =$40,000/Econvert. I would like the cell to become red if they forget to divide by Econvert which is why I think that conditional formatting would help. Any ideas? Thanks in advance. ...

rows in a table?
I keep a total of 1000 rows in a table full of vlookups linked to another sheet of data. The reason I keep 1000 rows is because the approx 800 rows of data fluctuates and the rest of the rows in the table are filled with N/A's. These N/A's become quite a problem and interrupt filtering data etc. There are only two answers to the problem that I can think of, but don't know how to do it. 1. I could figure out a way for the table to automatically change the number of rows to fit the 800 +/- 50 rows of data from the other sheet it looks up. 2. I have tried this and...

Microcontroller and PC commnicaiton using USB
Hi I am using a microcontroller. I have to communicate to the microcontroller using the PC. Both the PC and Microcontroller has the devivce driver. I am a new to this and I am not getting any pointer to start the programming. I have to do the programming using VC++ Please let me know if you have any idea on it Mayur... MJ wrote: > Hi > I am using a microcontroller. I have to communicate to the > microcontroller using the PC. Both the PC and Microcontroller has the > devivce driver. I am a new to this and I am not getting any pointer to > start the programming. I have to do t...

How can I transpose rows to columns in a large worksheet?
I have a worksheet with 1268 rows and 31 columns. Ideally I need all the rows to be converted to one column, although I believe this is impossible without some additional manual steps. Every time I attempt to copy/transpose my rows to columns, I receive the message that the paste area does not match the size of the copy area. I have tried transposing a few rows at a time. I have tried transposing ONE row at a time. Each time I try, I either receive an error message, or get the #VALUE! error message in the cell. I've tried TRANSPOSE and MINVERSE. I would prefer NOT to do this m...

Splitting records to two rows
I have a report that has many columns; most of them not very wide, however the report is too wide to fit even legal size sheets in landscape mode, without reducing the font to a "difficult to read" size. I realize I can split the report vertically into two pages, however, I would prefer not to. Does anyone know of a way to split each record into two rows without using an inefficient cut and paste process? Hi, Try this macro on a test sheet. Sub Make2_rows() Dim lastrow As Long Dim row_index As Long Dim ColtoSplit As Integer ColtoSplit = 4 ' ColtoSplit is the column number...