extracting unique #'s from multiple columns

I have some data in two columns and i want to extract the uniqu
records,  I would use a combination of the if and countif if I wer
only dealing with 1 column but with two columns I am stumped.

EX

order#   box#
123          1
123          1
123          2
123          3
125          65
125          65

i NEED TO BE ABLE TO SEPERATE AND COUNT THE UNIQUE RECORDS  

123      1
123      2
123      3
125      6

--
Message posted from http://www.ExcelForum.com

0
8/10/2004 4:19:31 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
446 Views

Similar Articles

[PageSpeed] 2

Hi
one way:
- create a helper column C
- in C1 enter the formula:
=A1 & "^" & B1
- copy this down and use your approah on this column

>-----Original Message-----
>I have some data in two columns and i want to extract the 
unique
>records,  I would use a combination of the if and countif 
if I were
>only dealing with 1 column but with two columns I am 
stumped.
>
>EX
>
>order#   box#
>123          1
>123          1
>123          2
>123          3
>125          65
>125          65
>
>i NEED TO BE ABLE TO SEPERATE AND COUNT THE UNIQUE 
RECORDS  
>
>123      1
>123      2
>123      3
>125      65
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
frank.kabel (11126)
8/10/2004 4:26:48 PM
Another way:

Say your data is in C1:d7
select C1:D7
data|filter|advanced filter
check the unique records only

Copy those visible cells (you can include more columns in your copied range)
to a new location.

"matmich <" wrote:
> 
> I have some data in two columns and i want to extract the unique
> records,  I would use a combination of the if and countif if I were
> only dealing with 1 column but with two columns I am stumped.
> 
> EX
> 
> order#   box#
> 123          1
> 123          1
> 123          2
> 123          3
> 125          65
> 125          65
> 
> i NEED TO BE ABLE TO SEPERATE AND COUNT THE UNIQUE RECORDS
> 
> 123      1
> 123      2
> 123      3
> 125      65
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/10/2004 10:51:25 PM
Reply:

Similar Artilces:

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Column Charts for Pie Slices?
Hi. What a great forum! Hope someone can help me (went back about a month and didn't see this question). My pie chart has 5 slices (# of employees for each of 5 departments). Each slice consists of 2 values (male, female). I'd like each slice to "point" to its own stacked column chart that shows how the slice is distributed across the two values. So the pie chart itself shows the distribution of employees across depatments, and the 5 associated stacked column charts show the relative number of male and female employees for each dept. Any ideas? Thanks! You could ma...

transferring data in rows of one table to columns of another table
Hi All, We are working with FCC station data that puts some simple numeric data in one file, arranged in a number of consecutive rows for each station. The next station's data follows consecutively. Each group of rows that are common to a station share an index number, while the next group uses its own separate number. A separate file contains the main information fields in a table of separate rows, or records, along with a matching index number. We'd like to move the numeric data in a group of rows that share the index # for a station, to a series of new fields add...

adding 2 columns of currency
=D5-SUM(D6:D92,H6:H92) i thought worked but for some reason i can't get it to. Can anyone help me ...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

displaying multiple e-mail addresses
I am using Outlook 2000 with Windows XP and can not get my address book to display multiple e-mail addresses for my contacts. I would like to see all of the e-mail addresses which are loaded for a particular contact when I open the address book and/or when I select "To" or "Cc" to add an address to an e-mail. My work computer (Outlook 2000 and Windows 98) displays multiple entries for contacts with multiple e-mail addresses (E-mail, E-mail 2, E-mail 3). Can anyone tell me how to change this display setting? In what mail support mode are you using Outlook? If it's ...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

Multiple Signatures
I have my email set up to allow multiple signatures. My outlook is set up to use 2 POP3 accounts. In the signature settings I select the signature to use for each account. When I create a new message, the default account comes up with it's set signature. When I select the alternate Pop3 account, the signature remains the defaults and not the signature that was associated with that account. Any ideas. On my older version of Outlook express, it would automatically switch the signatures when I selected an alternate account to send from. Any ideas? I've tried deleting the ...

multiple modal dialogs
Hi, Anybody an idea how to have multiple modal dialogs in an application? I have an application that should allow multiple frame windows in the same time. The problem is, that these frame windows can have there own modal dialogs / message boxes. If a modal dialog (or message box) is open in one frame, and I switch to an other frame, and open there an other modal dialog (or a message box comes as a response to some event), the application enters in the modal loop if the new modal dialog. The problem is, that if the first modal dialog is closed with it's frame window, the frame window...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Redeem Gift Certificates at multiple locations
Does anyone know how I could create a Gift Card that could be redeemed at any of our stores? We are using Headquarters. Currently, the store that sells the card is the only one that can redeem it. Thanks! There is a Product called GIVEX out there for this purpose "Paul" <pbunn26@hotmail.com> wrote in message news:%23ICPCYLrEHA.3116@TK2MSFTNGP10.phx.gbl... > Does anyone know how I could create a Gift Card that could be redeemed at > any of our stores? We are using Headquarters. > Currently, the store that sells the card is the only one that can redeem it. > ...

change multiple links at once
Hello, I have a master spreadsheet that has multiple links in it from 150 other spreadsheets. Needless to say it was very time consuming to link certain rows from each spreadsheet. If there is a way to automate this for the future, I would very much appreciate the info. I have no exp in VB by the way. :) A co-worker was helping me do the links and accidently linked to the wrong file name. Is there a way to change each link to the correct one without it being such a manual process? For example, I have a spreadsheet named County.xls. In that spreadsheet I have multiple ro...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Could not determine which columns uniquely identify the rows
All, When I try to configure an odbcDataAdaptor through an ODBC connection to an Excel spreadsheet I the error message on the subject line. It applies to the SQL Insert and Update commands. I think I know that it is telling me that there is no primary key assigned for the table that is being referenced in the spreadsheet. What I cannot figure out is how to specify the primary key for the named range in the spreadsheet. TIA Bill ...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

Multiple users with different addresses
I just switched to outlook. We have 5 different user names, each with our own e-mail address, but I can't figure out how to switch from one person to another, and the user that I set as my default isn't the one that it defaults to. I could really use some help. So could we - like providing the version of Outlook and, if 98 or 2000, the mode? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head-scra...

Multiple comparison of list items.
Hi, I need to give the users of my data a spreadsheet with the option of 'choosing' which items are relevant to their needs. I need them to make a preference choice between all the possible combinations, using VLOOKUP. Where my problem is though is as follows. I have a list of unique part numbers in a column. I need a macro to prepare a list that I can do some further comparitive work on. My list looks something like this. Column A 00237 00243 00251 00377 I need to be able to cross reference each part number with each of the others, generating a list which wil look like the followin...

setting print area for multiple worksheets at once
Hello - Looking for a way to set the print area on a large number of simiarly formatted worksheets at once. Ideally want to incorporate it into a macro that does some other basic stuff as well. Thanks for any suggestions! -- annabel ------------------------------------------------------------------------ annabel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26520 View this thread: http://www.excelforum.com/showthread.php?threadid=397879 Good morning Annabel This code will loop through all your worksheets in a workbook and se the range to A1:F30. Sub Se...

unique sequential number excel 2000
I have multiple sheets in a workbook which I am using as stock records. I have a macro which hides certain rows on the worksheet (which are not required on the Issue Voucher) and prints out a copy of the worksheet stock record as an Issue Voucher to two separate printers. I want to put in a sequential number on the Issue Voucher, each time I run a print. Help! Can I write something into the macro? ...

total a calculated column in report
I am constructing a fittings database and trying to calculate the cost of replacing missing fittings. I am calcularing the cost of each fitting that needs to be replaced by; =(([tblFtgs!fldFullStk]-[tblFtgs!fldOnHand])*( [fldCostPer])) subtracting the on hand stock (fldOnHand) from required stock (fldFullStk) then multiplying by the cost for each fitting (fldCostPer). The problem is , I want a total of all the items needing to be replaced by summing the column of that calculation but can't get it to work Trying in a query it says Can't have a Memo , Ole...