Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique instances of text in column
I have a column of text where I need to count the unique values. The column
of text is in another worksheet called Dist. The column of data may look like this: 24509 13450 24509 18709 24509 13450 I have no idea what the column of data may contain beforehand, so I can't do a count of "24509", but I need the result to look like this: 24509 3 13450 2 18709 1 Any suggestions? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique instances of text in column
Hi Medirate,
I found this in Excel's help. Is this what you were looking for? Count the number of unique values by using a filter You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range. Ensure that the first row in the column has a column header. On the Data menu, point to Filter, and then click Advanced Filter. In the Advanced Filter dialog box, click Copy to another location. If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data. In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values. Select the Unique records only check box, and click OK. The unique values from the selected range are copied to the new column. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter: =ROWS(B1:B45) "medirate" wrote: I have a column of text where I need to count the unique values. The column of text is in another worksheet called Dist. The column of data may look like this: 24509 13450 24509 18709 24509 13450 I have no idea what the column of data may contain beforehand, so I can't do a count of "24509", but I need the result to look like this: 24509 3 13450 2 18709 1 Any suggestions? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique instances of text in column
Hi
Look at this, output will be in Sheet1: Sub AAA() Dim TargetSh As Worksheet Dim DestSh As Worksheet Dim FilterRange As Range Set TargetSh = Worksheets("Dist") Set DestSh = Worksheets("Sheet1") ' Change to suit FirstRow = 1 ' HeaderRow TargetCol = "A" ' Change to suit LastRow = TargetSh.Range(TargetCol & Rows.Count).End(xlUp).Row Set FilterRange = TargetSh.Range(TargetCol & FirstRow, _ TargetSh.Range(TargetCol & LastRow)) FilterRange.AdvancedFilter Action:=xlFilterInPlace, _ Unique:=True FilterRange.SpecialCells(xlCellTypeVisible).Copy _ Destination:=DestSh.Range("A1") CellCount = FilterRange.SpecialCells(xlCellTypeVisible).Cells. Count TargetSh.ShowAllData DestSh.Range("B2").FormulaR1C1 = "=COUNTIF(Dist!R2C1:R7C1,RC[-1])" DestSh.Range("B2").AutoFill Destination:=DestSh.Range _ ("B2:B" & CellCount), Type:=xlFillDefault End Sub Regards, Per On 7 Maj, 01:10, "medirate" wrote: I have a column of text where I need to count the unique values. The column of text is in another worksheet called Dist. The column of data may look like this: 24509 13450 24509 18709 24509 13450 I have no idea what the column of data may contain beforehand, so I can't do a count of "24509", but I need the result to look like this: 24509 * *3 13450 * *2 18709 * *1 Any suggestions? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count unique instances of text in column
Not exactly. I need to accomplish this withouot user input. Bascially the
data for each worksheet will be imported from a text file. I have this set up so that all they have to do is click the Refresh Data button. At that point, the new text is imported, and I need this function to calculate the unique values from a column in the newly imported text. "mcwilsong" wrote in message ... Hi Medirate, I found this in Excel's help. Is this what you were looking for? Count the number of unique values by using a filter You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range. Ensure that the first row in the column has a column header. On the Data menu, point to Filter, and then click Advanced Filter. In the Advanced Filter dialog box, click Copy to another location. If the range that you are counting is not already selected, delete any information in the List range box and then click the column (or select the range) that contains your data. In the Copy to box, delete any information in the box or click in the box, and then click a blank column where you want to copy the unique values. Select the Unique records only check box, and click OK. The unique values from the selected range are copied to the new column. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter: =ROWS(B1:B45) "medirate" wrote: I have a column of text where I need to count the unique values. The column of text is in another worksheet called Dist. The column of data may look like this: 24509 13450 24509 18709 24509 13450 I have no idea what the column of data may contain beforehand, so I can't do a count of "24509", but I need the result to look like this: 24509 3 13450 2 18709 1 Any suggestions? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Instances of Text in String Q | Excel Worksheet Functions | |||
Count Multiple Instances of Text in a Cell | Excel Discussion (Misc queries) | |||
How do I count the # of unique occurences of a text in a column? | Excel Worksheet Functions | |||
count unique instances based on two columns | Excel Worksheet Functions | |||
counting unique instances of text in a list | Excel Worksheet Functions |