miicrosoft excel function question?

Question

ok i have this excel spreadsheet with a column of file names i need to fgure out a way to write a function that will look at the column, find the duplicates in that column and then give me another column with just the unique files in the first column so a column of no duplicates anyone know how i can do this?

Answer 1

If you paste this in the column alongside your data you will get a column of unique entries. It is an array formula, so you need to press CTRL+Shift+Enter instead of just Enter after typing it in. Of course, the A1 will posibly need to be changed if you data is not in Column A.

=IF(COUNTIF($A$1:A1, A1)=1, A1, "")

The column will be unsorted with each unique entry aligned with the entry where it first appeared in the original column. You can Copy | Paste Special... | Values to get the data sorted appropriately.

Answer 2

I tried the Pivottable and I don't know how to make it work with that, but I found a neat function called Subtotals that can get you the unique list you want pretty quickly and by copying a formula into all the rows:

First be sure you have a title of the column on the row above the data and use some format to set it apart from the data (your filenames) I just underlined my example.

Select one row of your data then do: Data, Subtotals, OK

My example had these options on and all others off, Replace current subtotals, Summary below data.
Click OK

It creates an outline to the left of your sheet. Mine has 3 levels and when I click on level 2, it collapses the duplicates to a list of unique items with the word 'Count' appended.

You can use a formula to take off the word: if the label is in B3, the formula would be =+LEFT(B3,LEN(B3)-6) because 6 is the number of characters in (space) Count.

I hope this works for what you want.

Answer 3

i don't think a function will work with this. i think you will need to use visual basic to do what you want.

if i were doing this i would simply import the data into ms access and run query which would sort by first name and output that query back into excel. that may sound complicated but it could be done in two minutes. far quicker than trying to work out the vb needed to do it solely in excel.

Answer 4

You have two options
One) Use Date > Filter > Advanced filter, you will see there an option that says "Copy Unique records only" so you can use it
Two) I still think that we can do a formula set for this, send me your address if you like to try it

Read my profile, I am the XLMan

Answer 5

The solution is to use countif and offset worksheet functions together with offset nested inside countif. Assume you have your 12 mth table on sheet2, excluding the column titles which is the month number the data block is A2:L21. The cell to show result, you put formula =COUNTIF( OFFSET( Sheet2!A2:A21, 0,A1 ,20,1),1) Inside the offset function: Sheet2!A2:A21 is the 1st mth data 0 is the offset of row, irrelevant in this case A1 is the offset of column, enter integer range from 0 to 11(1st mth to 12th mth) in Cell A1, or directly replace A1 with integer of that range. When enter 0(either in the formula or in cell A1), it means using A2:A21(1st mth) itself, if 11 is entered, it means move right 11 column from A2:A21 which is the 12th mth. 20 is the number of rows you want the formula return, 1 is the number of column you want the formula return. Hope this helps.

Answer 6

Use Data > PivotTable.

Answer 7

...OR you could just use sort in Excel without using Access.