Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items
say i have a list of transactions in column a i have name,
in column b i have amount, in column c i have currency code. there are about 20 different currency codes that show up many times. like. A B c IBM $100 USD SONY $500 JPY DELL $340 CAD AOL $100 GBP NIKE $250 CAD SPRINT $300 USD QCOM $500 USD YAHOO $330 CAD and so forth..as you can see a currency code can show up many times in the list. how can i make a list in a new sheet that contains just the unique currencies that are in the data. for example in my list there is USD,JPY,CAD,GBP.. i would like to extract this data into a new table that would be populated by those codes. like A B C USD JPY CAD GBP would appreciate any help. tks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items
3 things to think about:-
1) Pivot Tables - Absolutely my preferred choice for this kind of data. http://peltiertech.com/Excel/Pivots/pivotstart.htm 2) Sort the Data and use Data / Subtotals adding Sums / Counts as required 3) Use formulas such as SUMIF / COUNTIF / SUMPRODUCT etc which do not require the data to be sorted. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Bryan" wrote in message ... say i have a list of transactions in column a i have name, in column b i have amount, in column c i have currency code. there are about 20 different currency codes that show up many times. like. A B c IBM $100 USD SONY $500 JPY DELL $340 CAD AOL $100 GBP NIKE $250 CAD SPRINT $300 USD QCOM $500 USD YAHOO $330 CAD and so forth..as you can see a currency code can show up many times in the list. how can i make a list in a new sheet that contains just the unique currencies that are in the data. for example in my list there is USD,JPY,CAD,GBP.. i would like to extract this data into a new table that would be populated by those codes. like A B C USD JPY CAD GBP would appreciate any help. tks --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items
Sub CopyUniques()
Dim rng as Range Set rng = Range(Range("C1"),Range("C1").End(xldown)) rng.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Worksheets("Sheet2").Range("A1"), _ Unique:=True End Sub Regards, Tom Ogilvy "Bryan" wrote in message ... say i have a list of transactions in column a i have name, in column b i have amount, in column c i have currency code. there are about 20 different currency codes that show up many times. like. A B c IBM $100 USD SONY $500 JPY DELL $340 CAD AOL $100 GBP NIKE $250 CAD SPRINT $300 USD QCOM $500 USD YAHOO $330 CAD and so forth..as you can see a currency code can show up many times in the list. how can i make a list in a new sheet that contains just the unique currencies that are in the data. for example in my list there is USD,JPY,CAD,GBP.. i would like to extract this data into a new table that would be populated by those codes. like A B C USD JPY CAD GBP would appreciate any help. tks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Items
Tks for the help..been banging my head on my desk all day
to figure this one. -----Original Message----- Sub CopyUniques() Dim rng as Range Set rng = Range(Range("C1"),Range("C1").End(xldown)) rng.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=Worksheets("Sheet2").Range("A1"), _ Unique:=True End Sub Regards, Tom Ogilvy "Bryan" wrote in message ... say i have a list of transactions in column a i have name, in column b i have amount, in column c i have currency code. there are about 20 different currency codes that show up many times. like. A B c IBM $100 USD SONY $500 JPY DELL $340 CAD AOL $100 GBP NIKE $250 CAD SPRINT $300 USD QCOM $500 USD YAHOO $330 CAD and so forth..as you can see a currency code can show up many times in the list. how can i make a list in a new sheet that contains just the unique currencies that are in the data. for example in my list there is USD,JPY,CAD,GBP.. i would like to extract this data into a new table that would be populated by those codes. like A B C USD JPY CAD GBP would appreciate any help. tks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT unique items | Excel Discussion (Misc queries) | |||
selecting unique items | Excel Worksheet Functions | |||
Locate unique items | Excel Worksheet Functions | |||
Picking unique Items | Excel Discussion (Misc queries) | |||
Dealing With Unique Items | Excel Programming |